Home » SQL & PL/SQL » SQL & PL/SQL » problem in the query  () 1 Vote
problem in the query [message #249852] Fri, 06 July 2007 03:51 Go to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
SELECT  period_start_date,SUM(DECODE(deptno, 10, 1, 0)) AS v1
       , SUM(DECODE(deptno, 20, 1, 0)) AS val2
       , SUM(DECODE(deptno, 30, 1, 0)) AS val3
FROM     scott.emp emp
       , (SELECT ADD_MONTHS ( TO_DATE('1980-10-01', 'YYYY-MM-DD')
                            , 6 * (LEVEL - 1)
                            ) AS period_start_date
          FROM   DUAL
          CONNECT BY LEVEL <= 100) dates
WHERE    emp.hiredate BETWEEN dates.period_start_date
                          AND ADD_MONTHS (dates.period_start_date, 6)
GROUP BY period_start_date

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          1          2
01-APR-81          1          1          3
01-OCT-81          2          1          1
01-APR-87          0          2          0
01-OCT-05          1          0          0
01-OCT-06          1          0          0
01-OCT-07          0          1          0

7 rows selected.

In the above query my period_start_date is every six months added. 
Now if records does not exist during that period still a row should be 
inserted with that date and the values should be zero for that row as below.

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          1          2
01-APR-81          1          1          3
01-OCT-81          2          1          1
01-APR-82          0          0          0
01-OCT-82          0          0          0
01-APR-83          0          0          0
01-OCT-83          0          0          0
01-APR-84          0          0          0
01-OCT-84          0          0          0
01-APR-85          0          0          0
01-OCT-85          0          0          0
01-APR-86          0          0          0
01-OCT-86          0          0          0
01-APR-87          0          2          0
....
.....
01-OCT-06          0          0          0

till date.

thanks


[Updated on: Fri, 06 July 2007 03:57] by Moderator

Report message to a moderator

Re: problem in the query [message #249861 is a reply to message #249852] Fri, 06 July 2007 04:51 Go to previous messageGo to next message
ahamed79
Messages: 3
Registered: March 2007
Location: India - Australia
Junior Member
Hi,

May be please try using NVL Function.

Ex:
SELECT period_start_date,
NVL(SUM(DECODE(deptno, 10, 1, 0)), 0) AS v1,
NVL(SUM(DECODE(deptno, 20, 1, 0)), 0) AS val2,
NVL(SUM(DECODE(deptno, 30, 1, 0)), 0) AS val3
FROM scott.emp emp
, (SELECT ADD_MONTHS ( TO_DATE('1980-10-01', 'YYYY-MM-DD')
, 6 * (LEVEL - 1)
) AS period_start_date
FROM DUAL
CONNECT BY LEVEL <= 100) dates
WHERE emp.hiredate BETWEEN dates.period_start_date
AND ADD_MONTHS (dates.period_start_date, 6)
GROUP BY period_start_date

Cheers,
Faizal
Re: problem in the query [message #249862 is a reply to message #249852] Fri, 06 July 2007 05:00 Go to previous messageGo to next message
ahamed79
Messages: 3
Registered: March 2007
Location: India - Australia
Junior Member
Hi,

May be please try using NVL Function.

Ex:
SELECT period_start_date,
NVL(SUM(DECODE(deptno, 10, 1, 0)), 0) AS v1,
NVL(SUM(DECODE(deptno, 20, 1, 0)), 0) AS val2,
NVL(SUM(DECODE(deptno, 30, 1, 0)), 0) AS val3
FROM scott.emp emp
, (SELECT ADD_MONTHS ( TO_DATE('1980-10-01', 'YYYY-MM-DD')
, 6 * (LEVEL - 1)
) AS period_start_date
FROM DUAL
CONNECT BY LEVEL <= 100) dates
WHERE emp.hiredate BETWEEN dates.period_start_date
AND ADD_MONTHS (dates.period_start_date, 6)
GROUP BY period_start_date

Cheers,
Faizal
Re: problem in the query [message #249863 is a reply to message #249852] Fri, 06 July 2007 05:00 Go to previous messageGo to next message
ahamed79
Messages: 3
Registered: March 2007
Location: India - Australia
Junior Member
Hi,

May be please try using NVL Function.

Ex:
SELECT period_start_date,
NVL(SUM(DECODE(deptno, 10, 1, 0)), 0) AS v1,
NVL(SUM(DECODE(deptno, 20, 1, 0)), 0) AS val2,
NVL(SUM(DECODE(deptno, 30, 1, 0)), 0) AS val3
FROM scott.emp emp
, (SELECT ADD_MONTHS ( TO_DATE('1980-10-01', 'YYYY-MM-DD')
, 6 * (LEVEL - 1)
) AS period_start_date
FROM DUAL
CONNECT BY LEVEL <= 100) dates
WHERE emp.hiredate BETWEEN dates.period_start_date
AND ADD_MONTHS (dates.period_start_date, 6)
GROUP BY period_start_date

Cheers,
Faizal
Re: problem in the query [message #249906 is a reply to message #249852] Fri, 06 July 2007 08:34 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
it is not working

thanks
Re: problem in the query [message #249917 is a reply to message #249906] Fri, 06 July 2007 09:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try converting the join between emp and dates into an outer join.
WHERE emp.hiredate(+) BETWEEN dates.period_start_date AND ADD_MONTHS (dates.period_start_date, 6)
Re: problem in the query [message #250165 is a reply to message #249852] Sun, 08 July 2007 23:27 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
SELECT period_start_date,
NVL(SUM(DECODE(deptno, 10, 1, 0)), 0) AS v1,
NVL(SUM(DECODE(deptno, 20, 1, 0)), 0) AS val2,
NVL(SUM(DECODE(deptno, 30, 1, 0)), 0) AS val3
FROM scott.emp emp
, (SELECT ADD_MONTHS ( TO_DATE('1980-10-01', 'YYYY-MM-DD')
, 6 * (LEVEL - 1)
) AS period_start_date
FROM DUAL
CONNECT BY LEVEL <= 100) dates
WHERE emp.hiredate(+) BETWEEN dates.period_start_date
AND ADD_MONTHS (dates.period_start_date, 6) and
emp.hiredate <= sysdate
GROUP BY period_start_date

I tried as above, but still not working.

thanks


Re: problem in the query [message #250166 is a reply to message #249852] Sun, 08 July 2007 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I tried as above, but still not working.
I tried to help, but it is not working.
Too bad that "not working" is not a valid Oracle return code.
My car is not working. Tell me how to make my car start working.
Re: problem in the query [message #250253 is a reply to message #250166] Mon, 09 July 2007 05:45 Go to previous messageGo to next message
diku
Messages: 23
Registered: April 2007
Junior Member
Is this what you're looking at?

With
period as 
(select add_months(to_date('01-Oct-1980'),(level-1)*6)data from dual
connect by level <=100)
select 
	   to_char(data,'DD-Mon-YYYY') Period_ST,
	   SUM(DECODE(deptno, 10, 1, 0)) AS Dept10
       , SUM(DECODE(deptno, 20, 1, 0)) AS Dept20
       , SUM(DECODE(deptno, 30, 1, 0)) AS Dept30
from period,emp 
where hiredate(+) between to_date(data) and add_months(data,6)
group by data


DIKU
Re: problem in the query [message #250288 is a reply to message #249852] Mon, 09 July 2007 08:47 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
hi,
With
period as 
(select add_months(to_date('01-Oct-1980'),(level-1)*6)data from dual
connect by level <=100)
select 
	   to_char(data,'DD-Mon-YYYY') Period_ST,
	   SUM(DECODE(deptno, 10, 1, 0)) AS Dept10
       , SUM(DECODE(deptno, 20, 1, 0)) AS Dept20
       , SUM(DECODE(deptno, 30, 1, 0)) AS Dept30
from period,emp 
where hiredate(+) between to_date(data) and add_months(data,6)
group by data

PERIOD_ST       DEPT10     DEPT20     DEPT30
----------- ---------- ---------- ----------
01-Oct-1980          0          1          2
01-Apr-1981          1          1          3
01-Oct-1981          2          1          1
01-Apr-1982          0          0          0
01-Oct-1982          0          0          0
01-Apr-1983          0          0          0
01-Oct-1983          0          0          0
01-Apr-1984          0          0          0
01-Oct-1984          0          0          0
01-Apr-1985          0          0          0
01-Oct-1985          0          0          0

PERIOD_ST       DEPT10     DEPT20     DEPT30
----------- ---------- ---------- ----------
01-Apr-1986          0          0          0
01-Oct-1986          0          0          0
01-Apr-1987          0          2          0
01-Oct-1987          0          0          0
01-Apr-1988          0          0          0
01-Oct-1988          0          0          0
01-Apr-1989          0          0          0
01-Oct-1989          0          0          0
01-Apr-1990          0          0          0

20 rows selected.


this is my new query

With
period as
(select add_months(to_date('01-Oct-1980'),(level-1)*6)data from dual
connect by level <=100)
select
    to_char(data,'DD-Mon-YYYY') Period_ST,
    SUM(DECODE(deptno, 10, 1, 0)) AS Dept10
       , SUM(DECODE(deptno, 20, 1, 0)) AS Dept20
       , SUM(DECODE(deptno, 30, 1, 0)) AS Dept30
from period,emp
where hiredate(+) between to_date(data) and add_months(data,6)
and hiredate <= sysdate and ename like 'M%'
group by data


Added to more conditions to the where clause.when my where clause contains more than one condition then I am not getting the output what I am expecting

now my output is
PERIOD_ST       DEPT10     DEPT20     DEPT30
----------- ---------- ---------- ----------
01-Apr-1981          0          0          1
01-Oct-1981          1          0          0

This is not what I am expecting.
my output should be 

PERIOD_ST       DEPT10     DEPT20     DEPT30
----------- ---------- ---------- ----------
01-Oct-1980          0          0          0
01-Apr-1981          0          0          1
01-Oct-1981          1          0          0
01-Apr-1982          0          0          0
01-Oct-1982          0          0          0
.....
.....
01-Oct-2006          0          0          0


thanks

Re: problem in the query [message #250409 is a reply to message #249852] Mon, 09 July 2007 23:50 Go to previous message
diku
Messages: 23
Registered: April 2007
Junior Member
Hey, I think you need to do a bit of homework before posting these.When you need your employee names to begin with 'M' and also display data for those periods which don't have any new employee joining you'd need an UNION.

Read up a bit on that(UNION).
Work around any of the queries posted here.
It's just a question of adding some more rows.

Thanks,
DIKU
Previous Topic: preserve whitespaces in xml
Next Topic: Returning total pages in pagination
Goto Forum:
  


Current Time: Sun Dec 11 08:12:46 CST 2016

Total time taken to generate the page: 0.08949 seconds