using with Y (select * from ....) issue

[MisterMo]

Hi guys,

this one has stumped me for a couple of days now.

this is the query

WITH Y AS (SELECT * FROM V_WB_SUMMARY_SALES where DECK = :PLOC)
select 'First' as F1, SUM(DL_PRICE) AS TOT_SALES from Y WHERE NOT CW IS NULL
union
select 'Second' as F1, SUM(DL_PRICE) AS TOT_SALES from Y WHERE CW IS NULL

I am succesfully using the with option inside views but for some reason when I try to execute this one I get:

-----------------------------------------
ORA-00604: error occurred at recursive SQL level 1
ORA-01427: single-row subquery returns more than one row
-----------------------------------------

please let me know if you need more info

-Mo

 

[LKBrwnDBA]

Sure you are providing correct info? the error does not seem to correspond to the query.

Please provide more details.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Actually, your query could be re-written as follows:

Select Decode(Cw,Null,'Second','First') As F1
     , Sum(Dl_Price) As Tot_Sales
  From V_Wb_Summary_Sales Where Deck = :Ploc
 Group By Decode(Cw,Null,'Second','First');

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MisterMo]

Yes the error is the correct one and therefore I cannot find out what or where the real problem is.

using with and then just one query at the time doesn't matter which all works fime but when I try to use union it gives the error.

I have done some test on a simple table using unions a retrieving multiple instances of a same record and all worked.


-Mo

 

[LKBrwnDBA]

Maybe it's nothing, but try changing:

...WHERE NOT CW IS NULL
to
...WHERE CW IS NOT NULL




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[MisterMo]

Thanks for the replay and clues. I have already tried various options but I do get the same error.



-Mo

 

[MisterMo]

with X as (SELECT DECK, CASE WHEN ACT_MONTH = TS_MONTH THEN 1 ELSE 0 END AS MONTHLY,
CASE WHEN NOT CW IS NULL THEN 1 ELSE 0 END AS WEEKLY,
CASE WHEN TRUNC(DL_DATE) = TRUNC(SYSDATE)-7 THEN 1 ELSE 0 END AS DAILY, DL_PRICE 
FROM(SELECT  DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE, SUM(DL_PRICE) AS DL_PRICE 
FROM V_WB_SUMMARY_SALES WHERE DECK = :PLOC
AND ACT_MONTH = TS_MONTH
GROUP BY DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE))
select * from X

this query return this data

DECK	MONTHLY	WEEKLY	DAILY	DL_PRICE
CENT	1	0	0	131016
CENT	1	0	0	81409
CENT	1	0	0	120088
CENT	1	0	0	16938
CENT	1	0	0	10500
CENT	1	0	0	116257
CENT	1	0	0	175199
CENT	1	0	0	163584
CENT	1	0	0	56264
CENT	1	0	0	61285
CENT	1	0	1	34679
CENT	1	0	0	73349
CENT	1	0	0	95320
CENT	1	1	0	152766

what I need is to summarise it this way

PERIOD	TOT_SALES
DAILY	34679
MONTHLY	1288654
WEEKLY	152766
YEARLY	10667271

because I use with X for some reason it won't let me use union,
this is only with my view because if I use a normal table I can re-query as many times as I want.


-Mo

 

[LKBrwnDBA]

Maybe something like this will work:

SELECT CASE WHEN ACT_MONTH = TS_MONTH THEN 'MONTHLY'
            WHEN NOT CW IS NULL THEN 'WEEKLY'
            WHEN TRUNC(DL_DATE) = TRUNC(SYSDATE)-7 THEN 'DAILY'
            ELSE 'YEARLY'
        END AS PERIOD
     , SUM(DL_PRICE) AS TOT_SALES
  FROM (
  SELECT DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE, SUM(DL_PRICE) AS DL_PRICE 
    FROM V_WB_SUMMARY_SALES WHERE DECK = :PLOC AND ACT_MONTH = TS_MONTH
   GROUP BY DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE)
      )
 GROUP BY CASE WHEN ACT_MONTH = TS_MONTH THEN 'MONTHLY'
               WHEN NOT CW IS NULL THEN 'WEEKLY'
               WHEN TRUNC(DL_DATE) = TRUNC(SYSDATE)-7 THEN 'DAILY'
               ELSE 'YEARLY'
           END AS PERIOD;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[karluk]

I haven't tested the following code, but I believe it is close to what you need. I haven't tried to generate your row with YEARLY=10667271, since I don't see how this value is calculated.

with X as (SELECT DECK, CASE WHEN ACT_MONTH = TS_MONTH THEN 1 ELSE 0 END AS MONTHLY,
CASE WHEN NOT CW IS NULL THEN 1 ELSE 0 END AS WEEKLY,
CASE WHEN TRUNC(DL_DATE) = TRUNC(SYSDATE)-7 THEN 1 ELSE 0 END AS DAILY, DL_PRICE 
FROM(SELECT  DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE, SUM(DL_PRICE) AS DL_PRICE 
FROM V_WB_SUMMARY_SALES WHERE DECK = :PLOC
AND ACT_MONTH = TS_MONTH
GROUP BY DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE))
select 'DAILY' as PERIOD, sum(dl_price) as TOT_SALES
    from X WHERE DAILY=1
UNION
select 'WEEKLY' as PERIOD, sum(dl_price) as TOT_SALES
    from X WHERE WEEKLY=1
UNION
select 'MONTHLY' as PERIOD, sum(dl_price) as TOT_SALES
    from X WHERE MONTHLY=1

 

[MisterMo]

Hi karluk, that is what I have been trying for the last 3 days as soon as I use a union it breaks and the error message is not useful

LKBrwnDBA, I have tried your options before but as you can see some of the data is in both monthly weekly and daily so it needs to be counted 3 times

union is the best solution so far but is not working...

-Mo

 

[LKBrwnDBA]

OK, i'm out of town now, but I beleive I can work something out when I return.
Have a nice weekend.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[karluk]

MisterMo, may I ask if you took the trouble to actually test my suggestion or just assumed it wouldn't work because it contains the "UNION" operator? In fact, I can exactly reproduce your desired results on my own test database.

First I reproduced the results from your earlier post:

SQL> with X as (SELECT DECK, CASE WHEN ACT_MONTH = TS_MONTH THEN 1 ELSE 0 END AS MONTHLY,
  2  CASE WHEN NOT CW IS NULL THEN 1 ELSE 0 END AS WEEKLY,
  3  CASE WHEN TRUNC(DL_DATE) = TRUNC(SYSDATE)-7 THEN 1 ELSE 0 END AS DAILY, DL_PRICE
  4  FROM(SELECT  DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE, SUM(DL_PRICE) AS DL_PRICE
  5  FROM V_WB_SUMMARY_SALES WHERE DECK = 'CENT'
  6  AND ACT_MONTH = TS_MONTH
  7  GROUP BY DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE))
  8  SELECT * FROM X;

DECK          MONTHLY     WEEKLY      DAILY   DL_PRICE
---------- ---------- ---------- ---------- ----------
CENT                1          0          0     131016
CENT                1          0          0      81409
CENT                1          0          0     163584
CENT                1          1          0      34679
CENT                1          0          0     116257
CENT                1          0          0     175199
CENT                1          0          0      56264
CENT                1          0          0      95320
CENT                1          0          1     152766
CENT                1          0          0      10500
CENT                1          0          0      61285
CENT                1          0          0      73349
CENT                1          0          0     120088
CENT                1          0          0      16938

I then substituted the union of selects in my original post and got the following:

SQL> with X as (SELECT DECK, CASE WHEN ACT_MONTH = TS_MONTH THEN 1 ELSE 0 END AS MONTHLY,
  2  CASE WHEN NOT CW IS NULL THEN 1 ELSE 0 END AS WEEKLY,
  3  CASE WHEN TRUNC(DL_DATE) = TRUNC(SYSDATE)-7 THEN 1 ELSE 0 END AS DAILY, DL_PRICE
  4  FROM(SELECT  DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE, SUM(DL_PRICE) AS DL_PRICE
  5  FROM V_WB_SUMMARY_SALES WHERE DECK = 'CENT'
  6  AND ACT_MONTH = TS_MONTH
  7  GROUP BY DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE))
  8  select 'DAILY' as PERIOD, sum(dl_price) as TOT_SALES
  9      from X WHERE DAILY=1
 10  UNION
 11  select 'WEEKLY' as PERIOD, sum(dl_price) as TOT_SALES
 12      from X WHERE WEEKLY=1
 13  UNION
 14  select 'MONTHLY' as PERIOD, sum(dl_price) as TOT_SALES
 15      from X WHERE MONTHLY=1;

PERIOD   TOT_SALES
------- ----------
DAILY       152766
MONTHLY    1288654
WEEKLY       34679

So, if my query doesn't work on your database, we will have to dig deeper to find out what is different in your own environment. But I trust that it will work if you just give it a try.

Please note also, that I still haven't included the "YEARLY" total, since you haven't explained how this value is calculated.

 

[MisterMo]

Hi Karluk, thanks again for the replay,

I have the exact same code as you have written in your suggestion + another 15 different variety of attempts using unions, as I said in my previous post I know that the logic with+union is sound i've tested it with a normal table.

my fear is that my main view is the one causing the problem
the view is the one the return the data that I have posted earlier.

p.s. I have copied your code and tested it and got the same error.

this is the main view it will be difficul to read but here it goes

CREATE OR REPLACE FORCE VIEW SALES.WB_SUMMARY_SALES
AS 
WITH X AS (SELECT TS_MONTH_BEGIN,TS_MONTH_END,TS_MONTH FROM V_ACCOUNTS_MONTHS WHERE TS_YEAR = TO_CHAR(SYSDATE,'YYYY'))
SELECT DL_CODE,SP_ALLOC_DECK AS DECK, DL_PRICE, CW, ACT_MONTH, TS_MONTH, TRUNC(DL_DATE) AS DL_DATE
FROM (SELECT DL_CODE, SP_ALLOC_DECK, SP_TEAM,DL_REP1, CM_NAME AS REP_NAME, DL_STATUS, DECODE(DL_V_PRICE, NULL,DL_PRICE *DL_EXCH , DL_V_PRICE)AS DL_PRICE,
CASE WHEN DL_DT_COMP IS NULL THEN DL_DATE ELSE DL_DT_COMP END AS DL_DATE,
(SELECT TS_MONTH FROM X WHERE TRUNC(SYSDATE) BETWEEN TS_MONTH_BEGIN AND TS_MONTH_END) AS ACT_MONTH,
CASE WHEN DECODE(DL_DT_COMP, NULL, TRUNC(DL_DATE),TRUNC(DL_DT_COMP)) BETWEEN (NEXT_DAY(TRUNC(SYSDATE), FDAY)-6) AND (NEXT_DAY(TRUNC(SYSDATE), FDAY))
THEN TO_CHAR(DECODE(DL_DT_COMP, NULL, DL_DATE,DL_DT_COMP), 'DY') END AS CW
FROM T_DL,T_CM, T_SP,(SELECT TO_CHAR(TS_MONTH_BEGIN + 6 ,'DY') AS FDAY FROM X WHERE TS_MONTH = 1),
(SELECT CASE WHEN DL_PRICE1> DL_PRICE2 THEN DL_CODE1 ELSE DLREF1 END AS DL_V_CODE,
CASE WHEN DL_PRICE1> DL_PRICE2 THEN DL_PRICE2
WHEN DL_PRICE1< DL_PRICE2 THEN DL_PRICE2 - DL_PRICE2 ELSE 0 END AS DL_V_PRICE
FROM
(SELECT DA_DLREF2 DLREF1, DA_DLREF DL_CODE1,DL_STATUS DL_STATUS1, DL_PRICE AS DL_PRICE1
FROM T_DA, T_DL WHERE DA_DLREF = DL_CODE
AND DL_STATUS = 540
AND DL_DATE >= (SELECT TS_MONTH_BEGIN FROM X WHERE TS_MONTH = 1)),
(SELECT DA_DLREF DLREF2, DA_DLREF2 DL_CODE2,DL_STATUS DL_STATUS2,DL_PRICE*DL_EXCH AS DL_PRICE2
FROM T_DA, T_DL WHERE DA_DLREF2 = DL_CODE))
WHERE 1=1
AND DL_CODE = DL_V_CODE(+)
AND DL_REP1 = CM_REF
AND CM_CODE = SP_CMREF
AND INSTR(CM_VISIBILITY, 'CA')>0
AND NOT DL_STATUS IN(200, 220,250,260,270,300,310,330,370,380, 500,570) 
AND DL_DATE >= (SELECT TS_MONTH_BEGIN FROM X WHERE TS_MONTH = 1)),X
WHERE TRUNC(DL_DATE) BETWEEN TS_MONTH_BEGIN AND TS_MONTH_END
AND TS_MONTH >= (SELECT TS_MONTH -1 FROM X
WHERE TRUNC(SYSDATE) BETWEEN TS_MONTH_BEGIN AND TS_MONTH_END)
and dl_price >0;

look for the X named query which used 5 times in this query.

thanks again for you help

-Mo

 

[MisterMo]

and here is the solution to the problem

CREATE OR REPLACE VIEW SALES.WB_SUMMARY_SALES
AS
WITH X AS (SELECT TS_MONTH_BEGIN,TS_MONTH_END,TS_MONTH FROM V_ACCOUNTS_MONTHS WHERE TS_YEAR = TO_CHAR(SYSDATE,'YYYY'))
,J as (SELECT DECK, DL_PRICE, CW, ACT_MONTH, TS_MONTH, TRUNC(DL_DATE) AS DL_DATE
FROM (SELECT SP_ALLOC_DECK  AS DECK, SP_TEAM,DL_REP1, CM_NAME AS REP_NAME, DL_STATUS, DECODE(DL_V_PRICE, NULL,DL_PRICE *DL_EXCH , DL_V_PRICE)AS DL_PRICE,
CASE WHEN DL_DT_COMP IS NULL THEN DL_DATE ELSE DL_DT_COMP END AS DL_DATE,
(SELECT TS_MONTH FROM X WHERE TRUNC(SYSDATE) BETWEEN TS_MONTH_BEGIN AND TS_MONTH_END) AS ACT_MONTH,
CASE WHEN DECODE(DL_DT_COMP, NULL, TRUNC(DL_DATE),TRUNC(DL_DT_COMP)) BETWEEN (NEXT_DAY(TRUNC(SYSDATE), FDAY)-6) AND (NEXT_DAY(TRUNC(SYSDATE), FDAY))
THEN TO_CHAR(DECODE(DL_DT_COMP, NULL, DL_DATE,DL_DT_COMP), 'DY') END AS CW
FROM T_DL,T_CM, T_SP,(SELECT TO_CHAR(TS_MONTH_BEGIN + 6 ,'DY') AS FDAY FROM X WHERE TS_MONTH = 1),
(SELECT CASE WHEN DL_PRICE1> DL_PRICE2 THEN DL_CODE1 ELSE DLREF1 END AS DL_V_CODE,
CASE WHEN DL_PRICE1> DL_PRICE2 THEN DL_PRICE2
WHEN DL_PRICE1< DL_PRICE2 THEN DL_PRICE2 - DL_PRICE2 ELSE 0 END AS DL_V_PRICE
FROM
(SELECT DA_CODE AS DC1, DA_DLREF2 DLREF1, DA_DLREF DL_CODE1,DL_STATUS DL_STATUS1, ROUND(DECODE(DL_PRICE, NULL, 0,DL_PRICE)*DL_EXCH) AS DL_PRICE1
FROM T_DA, T_DL 
WHERE DA_DLREF = DL_CODE
AND DL_STATUS = 540
AND DL_DATE >= (SELECT TS_MONTH_BEGIN FROM X WHERE TS_MONTH = 1)),
(SELECT DA_CODE AS DC2, DA_DLREF DLREF2, DA_DLREF2 DL_CODE2,DL_STATUS DL_STATUS2,ROUND(DECODE(DL_PRICE, NULL, 0,DL_PRICE)*DL_EXCH) AS DL_PRICE2
FROM T_DA, T_DL 
WHERE DA_DLREF2 = DL_CODE)
WHERE DC1 = DC2)
WHERE 1=1
AND DL_CODE = DL_V_CODE(+)
AND DL_REP1 = CM_REF
AND CM_CODE = SP_CMREF
AND INSTR(CM_VISIBILITY, 'CA')>0
AND NOT DL_STATUS IN(200, 220,250,260,270,300,310,330,370,380, 500,570) 
AND DL_DATE >= (SELECT TS_MONTH_BEGIN FROM X WHERE TS_MONTH = 1)),X
WHERE TRUNC(DL_DATE) BETWEEN TS_MONTH_BEGIN AND TS_MONTH_END
AND TS_MONTH >= (SELECT TS_MONTH -1 FROM X
WHERE TRUNC(SYSDATE) BETWEEN TS_MONTH_BEGIN AND TS_MONTH_END)
and dl_price >0),
Y as (SELECT DECK, CASE WHEN ACT_MONTH = TS_MONTH THEN 1 ELSE 0 END AS MONTHLY,
CASE WHEN NOT CW IS NULL THEN 1 ELSE 0 END AS WEEKLY,
CASE WHEN TRUNC(DL_DATE) = TRUNC(SYSDATE) THEN 1 ELSE 0 END AS DAILY, DL_PRICE
FROM(SELECT  DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE, SUM(DL_PRICE) AS DL_PRICE
FROM J WHERE ACT_MONTH = TS_MONTH
GROUP BY DECK, CW, ACT_MONTH, TS_MONTH, DL_DATE))
SELECT DECK,'DAILY' AS PERIOD, SUM(DL_PRICE) AS TOT_SALES
FROM Y WHERE DAILY=1
GROUP BY DECK
UNION
SELECT DECK,'WEEKLY' AS PERIOD, SUM(DL_PRICE) AS TOT_SALES
FROM Y WHERE WEEKLY=1
GROUP BY DECK
UNION
SELECT DECK,'MONTHLY' AS PERIOD, SUM(DL_PRICE) AS TOT_SALES
FROM Y WHERE MONTHLY=1
GROUP BY DECK
UNION
SELECT DECK, 'YEARLY' AS PERIOD, SUM(TOT_SALE) AS TOT_SALES
FROM V_WB_TERMOMETER
GROUP BY DECK;

by nesting the a with within a with within a with I get the data that I want, however the .NET driver that we use to disply data on the website cannot dial with the complexity.

I love programming......

-Mo