I’m trying to produce a report that keeps a running total by the week. Based on the day of the week, it should start 1-7 days in the past. I’m new to Bus Obj and not sure if this can be done. I currently get a 00905 error with the SQL. Any help would be appreciated. Thanks.
SELECT
DATAMART.DM_TIME.TIM_DATE,
DATAMART.DM_OUTLET.DAZ_ZONE_CODE,
sum(DATAMART.FT_OUTLET_DRAW.ACTUAL_DRAW_QTY),
CASE WHEN TO_CHAR(sysdate, ‘DY’) = ‘TUE’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)-1 AND trunc(sysdate) - 1
WHEN TO_CHAR(sysdate,‘DY’) = ‘WED’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 2
WHEN TO_CHAR(sysdate,‘DY’) = ‘THR’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 3
WHEN TO_CHAR(sysdate,‘DY’) = ‘FRI’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 4
WHEN TO_CHAR(sysdate,‘DY’) = ‘SAT’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 5
WHEN TO_CHAR(sysdate,‘DY’) = ‘SUN’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 6
ELSE DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 7 END
FROM
DATAMART.DM_TIME,
DATAMART.DM_OUTLET,
DATAMART.FT_OUTLET_DRAW,
DATAMART.DM_CATEGORY
WHERE
( DATAMART.DM_TIME.TIM_KEY=DATAMART.FT_OUTLET_DRAW.TIM_KEY )
AND ( DATAMART.FT_OUTLET_DRAW.DM_OUT_KEY=DATAMART.DM_OUTLET.DM_OUT_KEY )
AND ( DATAMART.FT_OUTLET_DRAW.CATEGORY_KEY=DATAMART.DM_CATEGORY.CATEGORY_KEY(+) )
AND (DATAMART.DM_TIME.SITE_ID(+)=‘ORL’)
AND (DATAMART.DM_OUTLET.SITE_ID(+)=‘ORL’)
AND (DATAMART.FT_OUTLET_DRAW.SITE_ID(+)=‘ORL’)
AND (DATAMART.DM_CATEGORY.SITE_ID(+)=‘ORL’)
AND (
DATAMART.DM_CATEGORY.CATEGORY_CODE IN (‘ALHD’, ‘HDDS’, ‘HDPD’)
AND DATAMART.DM_OUTLET.DAZ_ZONE_CODE = ‘01’
)
GROUP BY
DATAMART.DM_TIME.TIM_DATE,
DATAMART.DM_OUTLET.DAZ_ZONE_CODE
CASE WHEN TO_CHAR(sysdate, 'DY') = 'TUE' THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)-1 AND trunc(sysdate) - 1
WHEN TO_CHAR(sysdate,'DY') = 'WED' THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 2
WHEN TO_CHAR(sysdate,'DY') = 'THR' THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 3
WHEN TO_CHAR(sysdate,'DY') = 'FRI' THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 4
WHEN TO_CHAR(sysdate,'DY') = 'SAT' THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 5
WHEN TO_CHAR(sysdate,'DY') = 'SUN' THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 6
ELSE DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 7 END
has a form of a condition that can go into the WHERE part of the query. You have it in the SELECT part.
I tried moving it to Where and getting a 00936 error. Not sure if I have formatted it correctly. The DM.TIME_DATE is a formatted date. It will run correctly if I enter one line in the where clause without the CASE. I’m trying to get it to run auto is the reason for the CASE statement.
SELECT
DATAMART.DM_TIME.TIM_DATE,
DATAMART.DM_OUTLET.DAZ_ZONE_CODE,
sum(DATAMART.FT_OUTLET_DRAW.ACTUAL_DRAW_QTY),
FROM
DATAMART.DM_TIME,
DATAMART.DM_OUTLET,
DATAMART.FT_OUTLET_DRAW,
DATAMART.DM_CATEGORY
WHERE
( DATAMART.DM_TIME.TIM_KEY=DATAMART.FT_OUTLET_DRAW.TIM_KEY )
AND ( DATAMART.FT_OUTLET_DRAW.DM_OUT_KEY=DATAMART.DM_OUTLET.DM_OUT_KEY )
AND ( DATAMART.FT_OUTLET_DRAW.CATEGORY_KEY=DATAMART.DM_CATEGORY.CATEGORY_KEY(+) )
AND (DATAMART.DM_TIME.SITE_ID(+)=‘ORL’)
AND (DATAMART.DM_OUTLET.SITE_ID(+)=‘ORL’)
AND (DATAMART.FT_OUTLET_DRAW.SITE_ID(+)=‘ORL’)
AND (DATAMART.DM_CATEGORY.SITE_ID(+)=‘ORL’)
AND (
DATAMART.DM_CATEGORY.CATEGORY_CODE IN (‘ALHD’, ‘HDDS’, ‘HDPD’)
AND DATAMART.DM_OUTLET.DAZ_ZONE_CODE = ‘01’
AND (CASE WHEN TO_CHAR(sysdate, ‘DY’) = ‘TUE’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)-1 AND trunc(sysdate) - 1
WHEN TO_CHAR(sysdate,‘DY’) = ‘WED’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 2
WHEN TO_CHAR(sysdate,‘DY’) = ‘THR’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 3
WHEN TO_CHAR(sysdate,‘DY’) = ‘FRI’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 4
WHEN TO_CHAR(sysdate,‘DY’) = ‘SAT’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 5
WHEN TO_CHAR(sysdate,‘DY’) = ‘SUN’ THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 6
ELSE DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 7 END )
)
GROUP BY
DATAMART.DM_TIME.TIM_DATE,
DATAMART.DM_OUTLET.DAZ_ZONE_CODE
Hi
A variety of issues with this code, but easy enough to fix.
What you are trying to do is set up a restriction i.e. between the start of the week and yesterday. This means that the restriction needs to go in the where clause (as i said, already been pointed out)
When you use a between, the lower bound (smaller) value must be the first argument whilst the upper bound (larger) value is the second argument. Therfore you have got your two arguments round the wrong way:
THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 1 AND trunc(sysdate) - 2
BECOMES
THEN DATAMART.DM_TIME.TIM_DATE BETWEEN trunc(sysdate)- 2 AND trunc(sysdate) - 1
However all of this can be done without the CASE expression at all:
You can calculate the start of the week simply by using the TRUNC function Therefore your restriction becomes
WHERE DATAMART.DM_TIME.TIM_DATE BETWEEN TRUNC(SYSDATE,'IW') and TRUNC(SYSDATE)-1)
Now that may need tweaking depending on your [i]exact[./i] requirements, but that should pretty much cover it.
You made my day! This is certainly an easy fix and allows for some other options. Thanks again.
SELECT
DATAMART.DM_TIME.TIM_DATE,
DATAMART.DM_OUTLET.DAZ_ZONE_CODE,
sum(DATAMART.FT_OUTLET_DRAW.ACTUAL_DRAW_QTY)
FROM
DATAMART.DM_TIME,
DATAMART.DM_OUTLET,
DATAMART.FT_OUTLET_DRAW,
DATAMART.DM_CATEGORY
WHERE
( DATAMART.DM_TIME.TIM_KEY=DATAMART.FT_OUTLET_DRAW.TIM_KEY )
AND ( DATAMART.FT_OUTLET_DRAW.DM_OUT_KEY=DATAMART.DM_OUTLET.DM_OUT_KEY )
AND ( DATAMART.FT_OUTLET_DRAW.CATEGORY_KEY=DATAMART.DM_CATEGORY.CATEGORY_KEY(+) )
AND (DATAMART.DM_TIME.SITE_ID(+)=‘ORL’)
AND (DATAMART.DM_OUTLET.SITE_ID(+)=‘ORL’)
AND (DATAMART.FT_OUTLET_DRAW.SITE_ID(+)=‘ORL’)
AND (DATAMART.DM_CATEGORY.SITE_ID(+)=‘ORL’)
AND (
DATAMART.DM_CATEGORY.CATEGORY_CODE IN (‘ALHD’, ‘HDDS’, ‘HDPD’)
AND DATAMART.DM_OUTLET.DAZ_ZONE_CODE = ‘01’
AND DATAMART.DM_TIME.TIM_DATE BETWEEN TRUNC(SYSDATE,‘IW’) and TRUNC(SYSDATE)-1
)
GROUP BY
DATAMART.DM_TIME.TIM_DATE,
DATAMART.DM_OUTLET.DAZ_ZONE_CODE
(BOB member since 2003-11-27)
(BOB member since 2008-07-29)