Click here to Skip to main content
15,173,818 members
Home / Discussions / Database
   

Database

 
GeneralRe: A Secured Hotel Management System using Visual BAsic 2010 Pin
hairy_hats3-Aug-10 6:37
Memberhairy_hats3-Aug-10 6:37 
GeneralRe: A Secured Hotel Management System using Visual BAsic 2010 Pin
J4amieC3-Aug-10 7:02
MemberJ4amieC3-Aug-10 7:02 
GeneralRe: A Secured Hotel Management System using Visual BAsic 2010 Pin
Simon_Whale3-Aug-10 7:05
professionalSimon_Whale3-Aug-10 7:05 
AnswerRe: A Secured Hotel Management System using Visual BAsic 2010 Pin
Simon_Whale3-Aug-10 6:28
professionalSimon_Whale3-Aug-10 6:28 
AnswerRe: A Secured Hotel Management System using Visual BAsic 2010 Pin
dan!sh 3-Aug-10 6:29
professional dan!sh 3-Aug-10 6:29 
AnswerRe: A Secured Hotel Management System using Visual BAsic 2010 Pin
Smithers-Jones3-Aug-10 6:49
MemberSmithers-Jones3-Aug-10 6:49 
AnswerRe: A Secured Hotel Management System using Visual BAsic 2010 Pin
Chris Meech3-Aug-10 9:54
MemberChris Meech3-Aug-10 9:54 
QuestionHow to get value from outer to inner SELECT Pin
Robert König3-Aug-10 2:59
MemberRobert König3-Aug-10 2:59 
Hello,

i am at the beginning of learning SQL, and i cannot find the solution to my problem:
I have got two tables, WORKCENTER and CALENDAR. WORKCENTER contains columns ID and NR_OF_DAYS, CALENDAR Contains columns DAY and IS_WORKING_DAY.
DAY contains the numeric value of a day (for example 14824 for today, 2010-08-03), and IS_WORKING_DAY is CHAR Type and indicates with 'y/n' if it is a working day or not.

I want for each Work Center to get the day from today + NR_OF_DAYS from table CALENDAR, excluding weekends and holidays.

The result should be like this:

ID   MAX_CALENDAR_DAY
 1              14833
 2              14840
...


Explanation:

Work Center 1 has NR_OF_DAYS = 7, today is 14824, + 7 = 14831, but there is a weekend in between, so + 2 = 14833.
Work Center 2 has NR_OF_DAYS = 12, today is 14824, + 12 = 14836, but there are two weekends in between, so + 4 = 14840.

This is my sql query (Oracle):

SELECT WORKCENTER.ID AS WORKCENTER_ID, MAX_CALENDAR_DAY
FROM WORKCENTER,
    (SELECT MAX(DAY) AS MAX_CALENDAR_DAY
     FROM (SELECT DAY
           FROM CALENDAR
           WHERE DAY > TRUNC(TO_NUMBER(SYSDATE - TO_DATE('01.01.1970', 'DD.MM.YYYY')))
           WHERE CALENDAR.IS_WORKING_DAY= 'y'
           ORDER BY CALENDAR.DAY
          )
     WHERE ROWNUM <= WORKCENTER.NR_OF_DAYS
    )
WHERE WORKCENTER.ID IN ( 1, 2, 6, 7, 9)


Oracle SQL Developer shows an error, WORKCENTER.NR_OF_DAYS is unknown. This is obvious, if inner queries are handled first (is this correct?).
Could you tell me how i have to build this query?

I hope you see what i am trying to do...

Greetings
AnswerRe: How to get value from outer to inner SELECT Pin
Tim Carmichael3-Aug-10 6:27
MemberTim Carmichael3-Aug-10 6:27 
AnswerRe: How to get value from outer to inner SELECT Pin
Bernhard Hiller3-Aug-10 21:55
MemberBernhard Hiller3-Aug-10 21:55 
GeneralRe: How to get value from outer to inner SELECT Pin
Robert König3-Aug-10 23:35
MemberRobert König3-Aug-10 23:35 
QuestionTemporary freeze updating .SDF file - fixed Pin
hairy_hats3-Aug-10 1:45
Memberhairy_hats3-Aug-10 1:45 
GeneralSelect number of rows [modified] Pin
MathewPV2-Aug-10 22:18
MemberMathewPV2-Aug-10 22:18 
GeneralRe: Select number of rows Pin
J4amieC2-Aug-10 23:01
MemberJ4amieC2-Aug-10 23:01 
GeneralRe: Select number of rows Pin
MathewPV2-Aug-10 23:24
MemberMathewPV2-Aug-10 23:24 
GeneralRe: Select number of rows Pin
Jörgen Andersson3-Aug-10 0:25
professionalJörgen Andersson3-Aug-10 0:25 
GeneralRe: Select number of rows Pin
MathewPV3-Aug-10 0:58
MemberMathewPV3-Aug-10 0:58 
GeneralRe: Select number of rows Pin
J4amieC3-Aug-10 4:06
MemberJ4amieC3-Aug-10 4:06 
GeneralRe: Select number of rows Pin
J4amieC3-Aug-10 4:07
MemberJ4amieC3-Aug-10 4:07 
GeneralRe: Select number of rows Pin
Jörgen Andersson3-Aug-10 4:46
professionalJörgen Andersson3-Aug-10 4:46 
QuestionOrder By Pin
SatyaKeerthi151-Aug-10 21:34
MemberSatyaKeerthi151-Aug-10 21:34 
AnswerRe: Order By Pin
R. Giskard Reventlov1-Aug-10 22:57
MemberR. Giskard Reventlov1-Aug-10 22:57 
GeneralRe: Order By Pin
SatyaKeerthi152-Aug-10 0:40
MemberSatyaKeerthi152-Aug-10 0:40 
GeneralRe: Order By [modified] Pin
R. Giskard Reventlov2-Aug-10 1:16
MemberR. Giskard Reventlov2-Aug-10 1:16 
Questiondb4o Embedded DB Pin
Mike Hankey1-Aug-10 3:05
professionalMike Hankey1-Aug-10 3:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.