How to add a third table to change the way the prompt is currently used

[JoelR]

How do I modify this sql code to do the following:
There is a table called R5PERSONNEL (Not yet included below). Inside of table R5PERSONNEL is a record called PER_CODE (it is like a social security number). I want the sql below to prompt on PER_CODE instead of EXC_PERSON (currently being prompted by name, shown below). Table R5PERSONNEL can be joined to the existing table R5EXCEPTIONS by record PER_USER (In table R5PERSONNEL) and EXC_PERSON (In table R5EXCEPTIONS).   In other words PER_USER and EXC_PERSON are primary and foriegn keys.  Thank you I hope this is clear.  This allows the user to enter a prompt number that is unique to him, like a social security number, instead of prompting by user name.

SELECT
EXC_DATE, EXC_PERSON, EXC_HOURS, EXC_COMMENT, SHP_SHIFT, SHP_PERSON,

CASE
WHEN SHP_SHIFT IN ('A', 'B', 'C', 'LUBE') THEN (10-(EXC_HOURS))
WHEN SHP_SHIFT IN ('D', 'E', 'F', 'G') THEN (12-(EXC_HOURS))
ELSE
(8-(EXC_HOURS))
END TIMEUSED

FROM
R5EXCEPTIONS LEFT OUTER JOIN R5SHFPERS ON EXC_PERSON = SHP_PERSON

WHERE
(EXC_COMMENT = 'vacation' OR EXC_COMMENT = 'Vacation' OR EXC_COMMENT = 'VACATION') AND
EXC_DATE BETWEEN TRUNC(SYSDATE, 'YEAR') AND SYSDATE AND
EXC_PERSON = #prompt('Parameter1')#

[Sunchaser]

Hi,

Have you tried something like:

Code Select Expand


FROM
R5PERSONNEL,
R5EXCEPTIONS LEFT OUTER JOIN R5SHFPERS ON EXC_PERSON = SHP_PERSON

WHERE
(EXC_COMMENT = 'vacation' OR EXC_COMMENT = 'Vacation' OR EXC_COMMENT = 'VACATION')
AND EXC_DATE BETWEEN TRUNC(SYSDATE, 'YEAR') AND SYSDATE
AND EXC_PERSON = PER_USER
AND PER_CODE = #prompt('Your_prompt', 'number')#

As the PER_CODE is a unique identifier for only one person ...

Something more: I don't know one which DB you're working but I think you could replace:

Code Select Expand


WHERE
(EXC_COMMENT = 'vacation' OR EXC_COMMENT = 'Vacation' OR EXC_COMMENT = 'VACATION')

by

Code Select Expand


WHERE UPPER(EXC_COMMENT) = 'VACATION'


Hope it could help you,
vinc.

[Sunchaser]

Sorry something more,

Juste one question, regarding this:

Code Select Expand


EXC_DATE BETWEEN TRUNC(SYSDATE, 'YEAR') AND SYSDATE AND

in your code.

AND SYSDATE ? Is there something missing about sysdate ?

[JoelR]

Thank you, this ended up being my solution:


SELECT
     x.EXC_DATE, x.EXC_PERSON, x.EXC_HOURS, x.EXC_COMMENT, s.SHP_SHIFT, s.SHP_PERSON,

     CASE
          WHEN s.SHP_SHIFT IN ('A', 'B', 'C', 'LUBE') THEN (10-(x.EXC_HOURS))
          WHEN s.SHP_SHIFT IN ('D', 'E', 'F', 'G') THEN (12-(x.EXC_HOURS))
     ELSE
          (8-(x.EXC_HOURS))
     END TIMEUSED

FROM
     R5PERSONNEL p, R5EXCEPTIONS x, R5SHFPERS s

WHERE   p.per_code = s.shp_person AND
     p.per_code = x.exc_person AND
     UPPER(x.EXC_COMMENT)='VACATION' AND
     x.exc_date between trunc(sysdate, 'YEAR') and sysdate AND
     p.per_user= #prompt('Parameter1')#

[Sunchaser]

Hi,
Just check twice maybe about your outer join that you don't use know, compared to the old version, in order to be sure of the results given now.

(I've understood that my preceding question was stupid, sysdate is used as a limit for "between" and I didn't see it)