Can you post sample data and expected results?
WITH q AS
			(SELECT chain,
cusnum,
CASE WHEN monday 	= 'Monday' 		AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Monday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Monday'),'yyyymmdd'))
ELSE 0 END AS monday,
CASE WHEN tuesday 	= 'Tuesday'		AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Tuesday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Tuesday'),'yyyymmdd'))
ELSE 0 END AS tuesday,
CASE WHEN wednesday = 'Wednesday'	AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Wednesday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Wednesday'),'yyyymmdd')) ELSE 0 END AS wednesday,
CASE WHEN thursday 	= 'Thursday' 	AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Thursday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Thursday'),'yyyymmdd'))
ELSE 0 END AS thursday,
CASE WHEN friday 	 = 'Friday' 		AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Friday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Friday'),'yyyymmdd'))	
ELSE 0 END AS friday,
CASE WHEN saturday = 'Saturday' AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Saturday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
THEN TO_NUMBER(to_char(next_day((sysdate), 'Saturday'),'yyyymmdd')) ELSE 0 END AS saturday
			FROM elm_dates),
x AS
			(SELECT store,
item,
monday,
tuesday,
wednesday,
thursday,
friday,
saturday,
o1,
o2,
o3,
o4,
o5,
sug
FROM elm_book, q
		 WHERE store = cusnum and sug <> ' ' AND sug <> '0')
SELECT "STORE","XDATE","ITEM","QTY"
FROM (SELECT store,
DECODE(
n,
1, monday,
2, tuesday,
3, wednesday,
4, thursday,
5, friday,
6, saturday
)
xdate,
item,
CASE
WHEN n = 1 AND moncnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 1 THEN o1
WHEN n = 3 AND wedcnt = 1 THEN o1
WHEN n = 4 AND thucnt = 1 THEN o1
WHEN n = 5 AND fricnt = 1 THEN o1
WHEN n = 6 AND satcnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 2 THEN o2
WHEN n = 4 AND thucnt = 2 THEN o2
WHEN n = 5 AND fricnt = 2 THEN o2
WHEN n = 6 AND satcnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 3 THEN o3
WHEN n = 5 AND fricnt = 3 THEN o3
WHEN n = 6 AND satcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 4 THEN o4
WHEN n = 5 AND fricnt = 4 THEN o4
WHEN n = 6 AND satcnt = 4 THEN o4
WHEN n = 5 AND fricnt = 5 THEN o5
WHEN n = 6 AND satcnt = 5 THEN o5
END
qty
FROM (SELECT x.*,
n,
SIGN(monday) moncnt,
SIGN(monday) + SIGN(tuesday) tuecnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) wedcnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) + SIGN(thursday) thucnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) + SIGN(thursday) + SIGN(friday) fricnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) + SIGN(thursday) + SIGN(friday) + SIGN(saturday) satcnt
FROM x,
(SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 6)
WHERE n = 1 AND monday != 0
OR n = 2 AND tuesday != 0
OR n = 3 AND wednesday != 0
OR n = 4 AND thursday != 0
OR n = 5 AND friday != 0
OR n = 6 AND saturday != 0))
WHERE qty > '0'
and store = '10397'
ORDER BY store,xdate,item;
10579 20131203 1064 1
10579 20131203 1064 1
10579 20131203 12 1
10579 20131203 12 1
10579 20131203 1475 1
10579 20131203 1475 1
10579 20131203 17 1
10579 20131203 17 1
10579 20131203 305 12
10579 20131203 305 12
10579 20131203 306 4
10579 20131203 306 4
10579 20131203 307 6
10579 20131203 307 6
10579 20131203 309 2
10579 20131203 309 2
10579 20131203 384 1
10579 20131203 384 1
10579 20131203 6 2
10579 20131203 6 2
10579 20131203 7415 1
10579 20131203 7415 1
10579 20131205 1064 2
10579 20131205 1064 2
10579 20131205 12 1
10579 20131205 12 1
10579 20131205 1475 1
10579 20131205 1475 1
10579 20131205 17 1
10579 20131205 17 1
10579 20131205 305 16
10579 20131205 305 16
10579 20131205 306 6
10579 20131205 306 6
10579 20131205 307 8
10579 20131205 307 8
10579 20131205 309 3
10579 20131205 309 3
10579 20131205 384 2
10579 20131205 384 2
10579 20131205 6 3
10579 20131205 6 3
10579 20131205 7415 1
10579 20131205 7415 1
10395 20131207 1 2
10395 20131207 13 1
10395 20131207 1690 1
10395 20131207 1692 1
10395 20131207 30 1
10395 20131207 305 6
10395 20131207 306 2
10395 20131207 307 5
10395 20131207 308 1
10395 20131207 309 1
10395 20131207 3743 1
10395 20131207 3752 1
10395 20131207 384 1
10395 20131207 6 1
10396 20131205 1 1
10396 20131205 17 1
10396 20131205 30 1
10396 20131205 305 6
10396 20131205 306 3
10396 20131205 307 4
10396 20131205 309 1
10396 20131205 384 1
10396 20131205 6 1
WITH q
AS (SELECT chain,
cusnum,
CASE
WHEN monday = 'Monday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Monday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Monday'), 'yyyymmdd'))
ELSE
0
END
AS monday,
CASE
WHEN tuesday = 'Tuesday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Tuesday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Tuesday'), 'yyyymmdd'))
ELSE
0
END
AS tuesday,
CASE
WHEN wednesday = 'Wednesday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Wednesday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Wednesday'), 'yyyymmdd'))
ELSE
0
END
AS wednesday,
CASE
WHEN thursday = 'Thursday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Thursday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Thursday'), 'yyyymmdd'))
ELSE
0
END
AS thursday,
CASE
WHEN friday = 'Friday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Friday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Friday'), 'yyyymmdd'))
ELSE
0
END
AS friday,
CASE
WHEN saturday = 'Saturday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03') - 7), 'Saturday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR((date '2013-12-03'), 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY(((date '2013-12-03')), 'Saturday'), 'yyyymmdd'))
ELSE
0
END
AS saturday
FROM export_table),
x
AS (SELECT store,
item,
monday,
tuesday,
wednesday,
thursday,
friday,
saturday,
o1,
o2,
o3,
o4,
o5,
sug
FROM elm_book, q
WHERE store = cusnum AND sug <> ' ' AND sug <> '0')
SELECT "STORE",
"XDATE",
"ITEM",
"QTY"
FROM (SELECT store,
DECODE(
n,
1, monday,
2, tuesday,
3, wednesday,
4, thursday,
5, friday,
6, saturday
)
xdate,
item,
CASE
WHEN n = 1 AND moncnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 1 THEN o1
WHEN n = 3 AND wedcnt = 1 THEN o1
WHEN n = 4 AND thucnt = 1 THEN o1
WHEN n = 5 AND fricnt = 1 THEN o1
WHEN n = 6 AND satcnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 2 THEN o2
WHEN n = 4 AND thucnt = 2 THEN o2
WHEN n = 5 AND fricnt = 2 THEN o2
WHEN n = 6 AND satcnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 3 THEN o3
WHEN n = 5 AND fricnt = 3 THEN o3
WHEN n = 6 AND satcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 4 THEN o4
WHEN n = 5 AND fricnt = 4 THEN o4
WHEN n = 6 AND satcnt = 4 THEN o4
WHEN n = 5 AND fricnt = 5 THEN o5
WHEN n = 6 AND satcnt = 5 THEN o5
END
qty
FROM (SELECT x.*,
n,
SIGN(monday) moncnt,
SIGN(monday) + SIGN(tuesday) tuecnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) wedcnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) + SIGN(thursday) thucnt,
SIGN(monday)
+ SIGN(tuesday)
+ SIGN(wednesday)
+ SIGN(thursday)
+ SIGN(friday)
fricnt,
SIGN(monday)
+ SIGN(tuesday)
+ SIGN(wednesday)
+ SIGN(thursday)
+ SIGN(friday)
+ SIGN(saturday)
satcnt
FROM x,
(SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 6)
WHERE n = 1 AND monday != 0
OR n = 2 AND tuesday != 0
OR n = 3 AND wednesday != 0
OR n = 4 AND thursday != 0
OR n = 5 AND friday != 0
OR n = 6 AND saturday != 0))
WHERE qty > '0' AND store in ( '10395','10396')
ORDER BY store, xdate, item;
WITH q
AS (SELECT chain,
cusnum,
CASE
WHEN monday = 'Monday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Monday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Monday'), 'yyyymmdd'))
ELSE
0
END
AS monday,
CASE
WHEN tuesday = 'Tuesday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Tuesday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Tuesday'), 'yyyymmdd'))
ELSE
0
END
AS tuesday,
CASE
WHEN wednesday = 'Wednesday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Wednesday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Wednesday'), 'yyyymmdd'))
ELSE
0
END
AS wednesday,
CASE
WHEN thursday = 'Thursday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Thursday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Thursday'), 'yyyymmdd'))
ELSE
0
END
AS thursday,
CASE
WHEN friday = 'Friday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Friday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Friday'), 'yyyymmdd'))
ELSE
0
END
AS friday,
CASE
WHEN saturday = 'Saturday'
AND TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE - 7), 'Saturday'), 'yyyymmdd')) <
TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'))
THEN
TO_NUMBER(TO_CHAR(NEXT_DAY((SYSDATE), 'Saturday'), 'yyyymmdd'))
ELSE
0
END
AS saturday
FROM export_table),
x
AS (SELECT store,
item,
monday,
tuesday,
wednesday,
thursday,
friday,
saturday,
o1,
o2,
o3,
o4,
o5,
sug
FROM elm_book, q
WHERE store = cusnum AND sug <> ' ' AND sug <> '0')
SELECT "STORE",
"XDATE",
"ITEM",
"QTY"
FROM (SELECT store,
DECODE(
n,
1, monday,
2, tuesday,
3, wednesday,
4, thursday,
5, friday,
6, saturday
)
xdate,
item,
CASE
WHEN n = 1 AND moncnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 1 THEN o1
WHEN n = 3 AND wedcnt = 1 THEN o1
WHEN n = 4 AND thucnt = 1 THEN o1
WHEN n = 5 AND fricnt = 1 THEN o1
WHEN n = 6 AND satcnt = 1 THEN o1
WHEN n = 2 AND tuecnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 2 THEN o2
WHEN n = 4 AND thucnt = 2 THEN o2
WHEN n = 5 AND fricnt = 2 THEN o2
WHEN n = 6 AND satcnt = 2 THEN o2
WHEN n = 3 AND wedcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 3 THEN o3
WHEN n = 5 AND fricnt = 3 THEN o3
WHEN n = 6 AND satcnt = 3 THEN o3
WHEN n = 4 AND thucnt = 4 THEN o4
WHEN n = 5 AND fricnt = 4 THEN o4
WHEN n = 6 AND satcnt = 4 THEN o4
WHEN n = 5 AND fricnt = 5 THEN o5
WHEN n = 6 AND satcnt = 5 THEN o5
END
qty
FROM (SELECT x.*,
n,
SIGN(monday) moncnt,
SIGN(monday) + SIGN(tuesday) tuecnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) wedcnt,
SIGN(monday) + SIGN(tuesday) + SIGN(wednesday) + SIGN(thursday) thucnt,
SIGN(monday)
+ SIGN(tuesday)
+ SIGN(wednesday)
+ SIGN(thursday)
+ SIGN(friday)
fricnt,
SIGN(monday)
+ SIGN(tuesday)
+ SIGN(wednesday)
+ SIGN(thursday)
+ SIGN(friday)
+ SIGN(saturday)
satcnt
FROM x,
(SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 6)
WHERE n = 1 AND monday != 0
OR n = 2 AND tuesday != 0
OR n = 3 AND wednesday != 0
OR n = 4 AND thursday != 0
OR n = 5 AND friday != 0
OR n = 6 AND saturday != 0))
WHERE qty > '0' AND store IN ('10395', '10396')
ORDER BY store, xdate, item;
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
oracle report printing 2 pages in one page | 2 | 57 | |
Need a replacement data type in Oracle | 6 | 65 | |
Extract the first word (before the , ) | 2 | 37 | |
Performance issue with case statement in oracle 11G | 7 | 45 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
19 Experts available now in Live!