Home » SQL & PL/SQL » SQL & PL/SQL » Conditional Query
Conditional Query [message #227579] Wed, 28 March 2007 12:49 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi All,
Please help me in accomplishing the below output using a query.

I have 3 tables

--EMPLOYEE_TIME
CREATE TABLE EMPLOYEE_TIME(EMPLOYEE NUMBER, WEEK_DATE DATE, TASK NUMBER, HOURS NUMBER)


--PROJECT_TASK (One PROJECT can have many TASK)
CREATE TABLE PROJECT_TASK(TASK NUMBER, PROJECT NUMBER)

--EMPLOYEE_TASK_RATE 
CREATE TABLE EMPLOYEE_TASK_RATE(EMPLOYEE NUMBER, PROJECT NUMBER, TASK NUMBER, RATE NUMBER)


INSERT INTO PROJECT_TASK VALUES(100, 1001);
INSERT INTO PROJECT_TASK VALUES(100, 1002);
INSERT INTO PROJECT_TASK VALUES(100, 1003);
INSERT INTO PROJECT_TASK VALUES(200, 2000);
INSERT INTO PROJECT_TASK VALUES(200, 2001);
INSERT INTO PROJECT_TASK VALUES(200, 2002);


INSERT INTO EMPLOYEE_TASK_RATE VALUES(10, NULL, 1001, 13);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(11, NULL, 1001, 14);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(11, NULL, 1002, 15);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(12, 200, NULL, 21);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(12, NULL, 2000, 17);
INSERT INTO EMPLOYEE_TASK_RATE VALUES(13, NULL, 2001, 16);

--Time can only be booked against a TASK
INSERT INTO EMPLOYEE_TIME VALUES(10, '01-APR-2006', 1001, 7);
INSERT INTO EMPLOYEE_TIME VALUES(10, '02-APR-2006', 1001, 8);
INSERT INTO EMPLOYEE_TIME VALUES(11, '01-APR-2006', 1001, 8);
INSERT INTO EMPLOYEE_TIME VALUES(12, '01-APR-2006', 2000, 8);
INSERT INTO EMPLOYEE_TIME VALUES(13, '02-APR-2006', 2001, 9);
INSERT INTO EMPLOYEE_TIME VALUES(12, '03-APR-2006', 2002, 8);


How to write a query based on table EMPLOYEE_TIME and EMPLOYEE_TASK_RATE to get Employee Hour Rate Details.
If rate is not defined/available at TASK level and it is defined at PROJECT Level, then PROJECT level Rate should be used. Otherwise Rate should be taken based on TASK only.

So the output should be:
EMPLOYEE	WEEK_DATE	TASK	HOURS	RATE
10		01-APR-2006	1001	7	13
10		02-APR-2006	1001	8	13
11		01-APR-2006	1001	8	14
12		01-APR-2006	2000	8	17 
13		02-APR-2006	2001	9	16
12		03-APR-2006	2002	8	21  -- 21 is taken as the rate because employee 12 does not 
						       have a rate for TASK 2002 in EMPLOYEE_TASK_RATE table
						       and 200 is the PROJECT for TASK 2002 




Please help in accomplishing this.

Thanks
Sandi
Re: Conditional Query [message #227591 is a reply to message #227579] Wed, 28 March 2007 13:52 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
look at nvl and decode (or case if you like).

if t1.col1 is null then return t2.col2
nvl(t1.col1, t2.col2)


if t1.col1 is null then return t2.col2 [else return t1.col1].
decode(t1.col1, null, t2.col2 [, t1.col1)]
Re: Conditional Query [message #227593 is a reply to message #227579] Wed, 28 March 2007 13:59 Go to previous messageGo to next message
jrich
Messages: 35
Registered: February 2006
Member
Whenever you want the first row that matches from two or more sources in a certain order, you can use the following...

select column(s) from (
  select column(s) from (
    select 1 prio, column(s) from table1 where ...
    union all
    select 2 prio, column(s) from table2 where ...
    -- repeat union all and select for other sources,
    -- incrementing prio number each time
  ) order by prio
) where rownum = 1


The selects can be as complex as you want them, but the returned column datatypes should match in each select for each column position.

Or in the simple case of two sources with one column you can use the following in your select statement...

nvl((select col from table1 where ...), (select col from table2 where ..))

I'll leave it to you to integrate the above into your homework assignment Smile

JR





Re: Conditional Query [message #227676 is a reply to message #227593] Thu, 29 March 2007 00:45 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
I am still stuck. After using NVL options, i sometime get duplicate rows. Any further help is greatly appreciated.

[Updated on: Thu, 29 March 2007 00:48]

Report message to a moderator

Re: Conditional Query [message #227707 is a reply to message #227579] Thu, 29 March 2007 02:30 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try following (I assumed existance of PROJECT table with RATE column). Everything else is based on your table's definitions:


CREATE TABLE PROJECT ( PROJECT NUMBER, RATE NUMBER);

SELECT ET.EMPLOYEE, ET.WEEK_DATE, ET.TASK, NVL(ETR.RATE,(SELECT RATE FROM PROJECT P WHERE P.PROJECT = PT.PROJECT )) RATE 
FROM EMPLOYEE_TIME ET
   JOIN PROJECT_TASK PT ON ( PT.TASK = ET.TASK )
   JOIN EMPLOYEE_TASK_RATE ETR ON ( ETR.EMPLOYEE = ET.EMPLOYEE AND ETR.TASK = ET.TASK AND ETR.PROJECT = PT.PROJECT )


HTH.
Michael
Previous Topic: hard sql urgent
Next Topic: Multiple fields with allongside COUNT
Goto Forum:
  


Current Time: Wed Dec 07 08:26:18 CST 2016

Total time taken to generate the page: 0.09091 seconds