I need to rewrite it so that the first order to be treated as tuesday, and last order to be treated as following monday if it has a monday order.

Ideally for it to work with Tuesday as start of week, monday as end of week.

this is what you wrote before. I've tried figuring it out on my own, but can't figure it out

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;

I trade changing the decode portion and the connect by level and giving monday a ranking of 6, but it doesnt seem to work correctly

0

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

sdstuber, attached file has create table and inserts.

the reason some of these are varchar vs number is because when i get the data dump from our customer, they send whitespaces. i have to clean the file after i have in oracle. elm-book.csv

using the tables and data above I ran this query...
I used 2013-12-03 as the date because that was the date when you posted the expected results

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;

Using current date you simply substitute sysdate for (date '2013-12-03')

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;

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.

Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of demâ€¦