Solved

Can't figure this out.

Posted on 2014-02-16
22
228 Views
Last Modified: 2014-03-24
I've been using this as part of a process I have to do on sundays for the past couple of months.

I have a table

Elm_dates
CHAIN     NOT NULL NUMBER(5)   
CUSNUM    NOT NULL NUMBER(9)   
SUNDAY             VARCHAR2(6) 
MONDAY             VARCHAR2(6) 
TUESDAY            VARCHAR2(7) 
WEDNESDAY          VARCHAR2(9) 
THURSDAY           VARCHAR2(8) 
FRIDAY             VARCHAR2(6) 
SATURDAY           VARCHAR2(8) 

Open in new window


See attached test data
elm-dates.sql
0
Comment
Question by:FutureDBA-
  • 12
  • 6
  • 4
22 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
What is the question?

I see the data, what do you want to have happen with that data?
0
 

Author Comment

by:FutureDBA-
Comment Utility
Yes, I pressed submit by mistake, was editing the question but was denied because you had commented.

Continuing.

Above table, and data..

This query here,  converts to dates. With monday being the monday of the following week.. right now, my problem is that i am getting nothing for saturday.

I have been running this every sunday night for a couple of months with no problem..

This is the query.

SELECT chain,
                cusnum,
                CASE WHEN sunday  	= 'Sunday' 		AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Sunday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
                     THEN TO_NUMBER(to_char(next_day((sysdate), 'Sunday'),'yyyymmdd')) ELSE 0 END AS sunday,
                
                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+7), '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;
      

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
I get 0 for Sunday, which is what I'd expect given your data.

All of your Sunday values are 0.  Your query only returns a date if the corresponding day column has that day in it.

If that's not what you expect, please post the expected results for the data above
0
 

Author Comment

by:FutureDBA-
Comment Utility
I am expecting 0's on Sunday..

My problem is that I am also getting 0's on saturday.

I am expecting dates on saturday

current results.
results
0
 

Author Comment

by:FutureDBA-
Comment Utility
elm_dates does have saturday values

elm_dates saturdays
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Using the inserts and query you posted above I get Saturday results


     CHAIN     CUSNUM     SUNDAY     MONDAY    TUESDAY  WEDNESDAY   THURSDAY     FRIDAY   SATURDAY
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      9007      10563          0          0   20140218          0   20140220          0   20140222
      9007       3385          0          0   20140218          0          0          0   20140222
      9007       4992          0   20140224          0   20140219          0   20140221          0
      9007      10598          0   20140224          0   20140219          0   20140221          0
      9007       2612          0   20140224   20140218          0   20140220          0   20140222
      9007       3958          0          0   20140218          0          0          0   20140222
      9007       3110          0   20140224   20140218   20140219   20140220   20140221   20140222
      9007       2744          0          0   20140218          0   20140220          0   20140222
      9007      10538          0   20140224          0   20140219          0   20140221          0
      9007       2010          0          0   20140218   20140219          0   20140221   20140222
      9007       4836          0          0   20140218          0   20140220          0   20140222
      9007       3847          0          0   20140218          0   20140220          0   20140222
      9007       4712          0          0   20140218          0   20140220          0   20140222
      9007       4733          0   20140224          0          0   20140220          0   20140222
      9007      10584          0   20140224          0   20140219          0   20140221          0
      9007        862          0   20140224          0          0          0   20140221          0
      9007       4902          0          0          0          0   20140220          0          0

17 rows selected.

Open in new window

0
 

Author Comment

by:FutureDBA-
Comment Utility
wtf..


Okay,  can the following be a variable?

elm_dates, isnt really a table. I posted it here as a table to simplify,.. it's really a view.. the SQL for that view is the following.


  CREATE OR REPLACE FORCE EDITIONABLE VIEW "REPERMS"."ELM_DATES" ("CHAIN", "CUSNUM", "SUNDAY", "MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY") AS 
  with m as (SELECT 
		RVCCUSCHN AS CHAIN, 
		RVCCUSNUM AS CUSNUM,
		CASE WHEN substr(to_char(RVCCUSWK1),1,1) = '1' THEN RVCRTENUM ELSE 0 END AS SUN, 
		CASE WHEN substr(to_char(RVCCUSWK1),2,1) = '1' THEN RVCRTENUM ELSE 0 END AS MON, 
		CASE WHEN substr(to_char(RVCCUSWK1),3,1) = '1' THEN RVCRTENUM ELSE 0 END AS TUE, 
		CASE WHEN substr(to_char(RVCCUSWK1),4,1) = '1' THEN RVCRTENUM ELSE 0 END AS WED, 
		CASE WHEN substr(to_char(RVCCUSWK1),5,1) = '1' THEN RVCRTENUM ELSE 0 END AS THU, 
		CASE WHEN substr(to_char(RVCCUSWK1),6,1) = '1' THEN RVCRTENUM ELSE 0 END AS FRI, 
		CASE WHEN substr(to_char(RVCCUSWK1),7,1) = '1' THEN RVCRTENUM ELSE 0 END AS SAT   
	FROM ELM_RMRVCP  
	WHERE  RVCCUSCHN in (9008,9007))
  
SELECT 
	M.CHAIN, 
	M.CUSNUM, 
	case when SUM(M.SUN) > 0 then 'Sunday'    else '0' end AS SUNDAY, 
	case when SUM(M.MON) > 0 then 'Monday'    else '0' end AS MONDAY, 
	case when SUM(M.TUE) > 0 then 'Tuesday'   else '0' end AS TUESDAY, 
	case when SUM(M.WED) > 0 then 'Wednesday' else '0' end AS WEDNESDAY, 
	case when SUM(M.THU) > 0 then 'Thursday'  else '0' end AS THURSDAY, 
	case when SUM(M.FRI) > 0 then 'Friday'    else '0' end AS FRIDAY, 
	case when SUM(M.SAT) > 0 then 'Saturday'  else '0' end AS SATURDAY 
FROM m
	GROUP BY M.CHAIN, M.CUSNUM;

Open in new window

0
 

Author Comment

by:FutureDBA-
Comment Utility
however, the view is returning Saturday on saturday columns.

didnt think it to be important..

select all from elm_dates
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
If you're running the query you show above on a view called elm_dates that produces the data shown above then it should be the same results as querying a table of the same data.

If it's not, then either something is different between what you're posting and what is really on your system


CREATE OR REPLACE FORCE EDITIONABLE VIEW


Do you have multiple editions of the view?
Are you sure you're using the same edition all the time and that edition is producing the same output you are showing above?



select * from dba_editions

do you have any editions other than ORA$BASE  ?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
perhaps you now need to provide source data from the source table ELM_RMRVCP

But in the meantime I would like to know what output this produces:
SELECT
		substr(to_char(RVCCUSWK1),1,1)
             , sum(RVCRTENUM)
FROM ELM_RMRVCP  
WHERE  RVCCUSCHN in (9008,9007)
GROUP BY
		substr(to_char(RVCCUSWK1),1,1)

Open in new window

0
 

Author Comment

by:FutureDBA-
Comment Utility
requested results
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
it appears that
substr(to_char(RVCCUSWK1),1,1)
is producing an "empty string"
and therefore it cannot equal "1"
CASE WHEN substr(to_char(RVCCUSWK1),1,1) = '1'

and that is why you  are not getting any Sundays in the wanted output.

To further trace your problem and reach resolution would require a set of data from the base table.
0
 

Author Comment

by:FutureDBA-
Comment Utility
edition
I only have 1 edition of the view.

I manually do a complete refresh of the view, every sunday before running this process.


Attached file is actual RMRVCP data
ELM-RMRVCP.SQL
0
 

Author Comment

by:FutureDBA-
Comment Utility
it appears that
substr(to_char(RVCCUSWK1),1,1)
is producing an "empty string"
and therefore it cannot equal "1"
CASE WHEN substr(to_char(RVCCUSWK1),1,1) = '1'


and that is why you  are not getting any Sundays in the wanted output.

To further trace your problem and reach resolution would require a set of data from the base table.

I am not expecting any data on sunday. My issue is with Saturday
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>  manually do a complete refresh of the view,

How do you refresh a "view"  ?

You can refresh a materialized view, but that's not what your DDL above shows.
0
 

Author Comment

by:FutureDBA-
Comment Utility
saturday sum
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
running your create table and inserts, I get an error on the very first row


ORA-12899: value too large for column "SDS"."ELM_RMRVCP"."RVCOREREP" (actual: 12, maximum: 10)
0
 

Author Comment

by:FutureDBA-
Comment Utility
it is a materialized view.

  CREATE MATERIALIZED VIEW "REPERMS"."ELM_RMRVCP" ("RVCRTENUM", "RVCRTEVER", "RVCEFFDTE", "RVCCUSCHN", "RVCCUSNUM", "RVCSUBCUS", "RVCENDDTE", "RVCCUSDLT", "RVCCUSBWK", "RVCCUSCYC", "RVCCUSFRQ", "RVCCUSSTR", "RVCCUSEND", "RVCCUSWK1", "RVCCUSWK2", "RVCSUBR01", "RVCSUBR02", "RVCSUBR03", "RVCSUBR04", "RVCSUBR05", "RVCSUBR06", "RVCSUBR07", "RVCSUBR08", "RVCSUBR09", "RVCSUBR10", "RVCSUBR11", "RVCSUBR12", "RVCSUBR13", "RVCSUBR14", "RVCDORD01", "RVCDORD02", "RVCDORD03", "RVCDORD04", "RVCDORD05", "RVCDORD06", "RVCDORD07", "RVCDORD08", "RVCDORD09", "RVCDORD10", "RVCDORD11", "RVCDORD12", "RVCDORD13", "RVCDORD14", "RVCPRID01", "RVCPRID02", "RVCPRID03", "RVCPRID04", "RVCPRID05", "RVCPRID06", "RVCPRID07", "RVCPRID08", "RVCPRID09", "RVCPRID10", "RVCPRID11", "RVCPRID12", "RVCPRID13", "RVCPRID14", "RVCCALD01", "RVCCALD02", "RVCCALD03", "RVCCALD04", "RVCCALD05", "RVCCALD06", "RVCCALD07", "RVCCALD08", "RVCCALD09", "RVCCALD10", "RVCCALD11", "RVCCALD12", "RVCCALD13", "RVCCALD14", "RVCCALT01", "RVCCALT02", "RVCCALT03", "RVCCALT04", "RVCCALT05", "RVCCALT06", "RVCCALT07", "RVCCALT08", "RVCCALT09", "RVCCALT10", "RVCCALT11", "RVCCALT12", "RVCCALT13", "RVCCALT14", "RVCLODS01", "RVCLODS02", "RVCLODS03", "RVCLODS04", "RVCLODS05", "RVCLODS06", "RVCLODS07", "RVCLODS08", "RVCLODS09", "RVCLODS10", "RVCLODS11", "RVCLODS12", "RVCLODS13", "RVCLODS14", "RVCPCKS01", "RVCPCKS02", "RVCPCKS03", "RVCPCKS04", "RVCPCKS05", "RVCPCKS06", "RVCPCKS07", "RVCPCKS08", "RVCPCKS09", "RVCPCKS10", "RVCPCKS11", "RVCPCKS12", "RVCPCKS13", "RVCPCKS14", "RVCCNTNAM", "RVCCNTPHN", "RVCOREREP", "RVCORERP1", "RVCORERP2", "RVCORERP3", "RVCORERP4", "RVCORERP5", "RVCORERP6", "RVCORERP7", "RVCCDW101", "RVCCDW102", "RVCCDW103", "RVCCDW104", "RVCCDW105", "RVCCDW106", "RVCCDW107", "RVCCDW201", "RVCCDW202", "RVCCDW203", "RVCCDW204", "RVCCDW205", "RVCCDW206", "RVCCDW207", "RVCSVCD01", "RVCSVCD02", "RVCSVCD03", "RVCSVCD04", "RVCSVCD05", "RVCSVCD06", "RVCSVCD07", "RVCSVCD08", "RVCSVCD09", "RVCSVCD10", "RVCSVCD11", "RVCSVCD12", "RVCSVCD13", "RVCSVCD14", "RVCCRTUSR", "RVCCRTDTE", "RVCCRTTIM", "RVCCHGUSR", "RVCCHGDTE", "RVCCHGTIM", "X_UPID", "X_RRNO")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_ERMS" 
  BUILD IMMEDIATE
  USING INDEX 
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS (select * from cxprddta.rmrvcp@E where rvccuschn in (9007,9008));

   COMMENT ON MATERIALIZED VIEW "REPERMS"."ELM_RMRVCP"  IS 'snapshot table for snapshot REPERMS.ELM_RMRVCP';

Open in new window

0
 

Author Comment

by:FutureDBA-
Comment Utility
my apologies, i did a find / replace to edit out internal users... please see attached file
ELM-RMRVCP.SQL
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
It is in the nature of your case expressions that you are suppressing a colun of data depending on which day the query is running. I ran your code on Monday, and the "Monday" column is zeros. If I omit some code (see "Monday_Test") then the values are not suppresssed.
    | CHAIN | CUSNUM | SUNDAY | MONDAY | MONDAY_TEST |  TUESDAY | WEDNESDAY | THURSDAY |   FRIDAY | SATURDAY |
    |-------|--------|--------|--------|-------------|----------|-----------|----------|----------|----------|
    |  9007 |  10563 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |   3385 |      0 |      0 |           0 | 20140218 |         0 |        0 |        0 | 20140222 |
    |  9007 |   4992 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |  10598 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |   2612 |      0 |      0 |    20140303 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |   3958 |      0 |      0 |           0 | 20140218 |         0 |        0 |        0 | 20140222 |
    |  9007 |   1805 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 |        0 |
    |  9007 |   3766 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |   4876 |      0 |      0 |           0 | 20140218 |         0 |        0 |        0 | 20140222 |
    |  9007 |  10582 |      0 |      0 |           0 | 20140218 |         0 |        0 |        0 | 20140222 |
    |  9007 |   1947 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |   4873 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |   3771 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |  10608 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |  10567 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |   4858 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |  10580 |      0 |      0 |    20140303 |        0 |         0 |        0 | 20140221 |        0 |
    |  9007 |  10569 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 |        0 |
    |  9007 |  10600 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |   3809 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |   4855 |      0 |      0 |           0 | 20140218 |         0 |        0 | 20140221 |        0 |
    |  9007 |  10586 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |   4893 |      0 |      0 |           0 | 20140218 |         0 |        0 |        0 | 20140222 |
    |  9007 |  10605 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |   1711 |      0 |      0 |    20140303 | 20140218 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |  10570 |      0 |      0 |           0 | 20140218 |         0 |        0 |        0 | 20140222 |
    |  9007 |   4565 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 | 20140222 |
    |  9007 |   4782 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |   4610 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |   1707 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |   4865 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |  10601 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |   4866 |      0 |      0 |    20140303 |        0 |         0 |        0 | 20140221 |        0 |
    |  9007 |  10604 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 |        0 |
    |  9007 |   4257 |      0 |      0 |    20140303 |        0 |         0 |        0 | 20140221 |        0 |
    |  9007 |   7848 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |   4693 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |  10606 |      0 |      0 |    20140303 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |  10568 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |  10572 |      0 |      0 |           0 | 20140218 |         0 | 20140220 |        0 | 20140222 |
    |  9007 |   3526 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |   3962 |      0 |      0 |    20140303 |        0 |  20140219 |        0 | 20140221 |        0 |
    |  9007 |   4664 |      0 |      0 |           0 | 20140218 |         0 |        0 |        0 | 20140222 |

Open in new window

**Query 1**:

    SELECT chain,
                    cusnum,
                    CASE WHEN sunday  	= 'Sunday' 		AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Sunday'),'yyyymmdd')) < to_number(to_char(sysdate,'yyyymmdd'))
                         THEN TO_NUMBER(to_char(next_day((sysdate), 'Sunday'),'yyyymmdd')) ELSE 0 END AS sunday,
                    
                    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+7), 'Monday'),'yyyymmdd'))  
                    ELSE 0 END AS monday,
                    
                    CASE WHEN monday  	= 'Monday' 
                         THEN TO_NUMBER(to_char(next_day((sysdate+7), 'Monday'),'yyyymmdd'))  
                    ELSE 0 END AS monday_test,
                    
                    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

Open in new window

you can see this working at: http://sqlfiddle.com/#!4/2edc8/2
0
 

Author Comment

by:FutureDBA-
Comment Utility
I see it working on your end,

I'm still getting 0 value for saturdays.

I proceeded with my import.

I have until thursday night to figure out.

Saturday orders have to go in on friday to be loaded that night for saturday delivery.

I am going to test further and see what findings i can come up with.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
try changing
)) <
to
)) <=
in your query, like this:
        SELECT 'changed query' src, chain,
                cusnum,
                CASE WHEN sunday  	= 'Sunday' 		AND TO_NUMBER(to_char(next_day((sysdate - 7), 'Sunday'),'yyyymmdd')) <= to_number(to_char(sysdate,'yyyymmdd'))
                     THEN TO_NUMBER(to_char(next_day((sysdate), 'Sunday'),'yyyymmdd')) ELSE 0 END AS sunday,
                
                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+7), 'Monday'),'yyyymmdd'))  
                ELSE 0 END AS monday,
                
                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+7), 'Monday'),'yyyymmdd'))  
                ELSE 0 END AS monday_test,
                
                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;
      

Open in new window

see this - in comparison to the original -- at:
http://sqlfiddle.com/#!4/2edc8/6

(compare the 2 sets of results below. The original query produces an unwanted column of zeros depending on the value of sysdate)
    |            SRC | CHAIN | CUSNUM | SUNDAY |   MONDAY |  TUESDAY | WEDNESDAY | THURSDAY |   FRIDAY | SATURDAY |
    |----------------|-------|--------|--------|----------|----------|-----------|----------|----------|----------|
    | original query |  9007 |  10563 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |   3385 |      0 |        0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | original query |  9007 |   4992 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |  10598 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |   2612 |      0 | 20140303 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |   3958 |      0 |        0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | original query |  9007 |   1805 |      0 |        0 | 20140225 |         0 | 20140220 |        0 |        0 |
    | original query |  9007 |   3766 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |   4876 |      0 |        0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | original query |  9007 |  10582 |      0 |        0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | original query |  9007 |   1947 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |   4873 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |   3771 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |  10608 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |  10567 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |   4858 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |  10580 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |  10569 |      0 |        0 | 20140225 |         0 | 20140220 |        0 |        0 |
    | original query |  9007 |  10600 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |   3809 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |   4855 |      0 |        0 | 20140225 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |  10586 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |   4893 |      0 |        0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | original query |  9007 |  10605 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |   1711 |      0 | 20140303 | 20140225 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |  10570 |      0 |        0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | original query |  9007 |   4565 |      0 | 20140303 |        0 |         0 |        0 | 20140221 | 20140222 |
    | original query |  9007 |   4782 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |   4610 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |   1707 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |   4865 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |  10601 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |   4866 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |  10604 |      0 |        0 | 20140225 |         0 | 20140220 |        0 |        0 |
    | original query |  9007 |   4257 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |   7848 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |   4693 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |  10606 |      0 | 20140303 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |  10568 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |  10572 |      0 |        0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | original query |  9007 |   3526 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |   3962 |      0 | 20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | original query |  9007 |   4664 |      0 |        0 | 20140225 |         0 |        0 |        0 | 20140222 |


    
    |           SRC | CHAIN | CUSNUM | SUNDAY |   MONDAY | MONDAY_TEST |  TUESDAY | WEDNESDAY | THURSDAY |   FRIDAY | SATURDAY |
    |---------------|-------|--------|--------|----------|-------------|----------|-----------|----------|----------|----------|
    | changed query |  9007 |  10563 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |   3385 |      0 |        0 |           0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | changed query |  9007 |   4992 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |  10598 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |   2612 |      0 | 20140303 |    20140303 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |   3958 |      0 |        0 |           0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | changed query |  9007 |   1805 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 |        0 |
    | changed query |  9007 |   3766 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |   4876 |      0 |        0 |           0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | changed query |  9007 |  10582 |      0 |        0 |           0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | changed query |  9007 |   1947 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |   4873 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |   3771 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |  10608 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |  10567 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |   4858 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |  10580 |      0 | 20140303 |    20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | changed query |  9007 |  10569 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 |        0 |
    | changed query |  9007 |  10600 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |   3809 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |   4855 |      0 |        0 |           0 | 20140225 |         0 |        0 | 20140221 |        0 |
    | changed query |  9007 |  10586 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |   4893 |      0 |        0 |           0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | changed query |  9007 |  10605 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |   1711 |      0 | 20140303 |    20140303 | 20140225 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |  10570 |      0 |        0 |           0 | 20140225 |         0 |        0 |        0 | 20140222 |
    | changed query |  9007 |   4565 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 | 20140222 |
    | changed query |  9007 |   4782 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |   4610 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |   1707 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |   4865 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |  10601 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |   4866 |      0 | 20140303 |    20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | changed query |  9007 |  10604 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 |        0 |
    | changed query |  9007 |   4257 |      0 | 20140303 |    20140303 |        0 |         0 |        0 | 20140221 |        0 |
    | changed query |  9007 |   7848 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |   4693 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |  10606 |      0 | 20140303 |    20140303 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |  10568 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |  10572 |      0 |        0 |           0 | 20140225 |         0 | 20140220 |        0 | 20140222 |
    | changed query |  9007 |   3526 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |   3962 |      0 | 20140303 |    20140303 |        0 |  20140226 |        0 | 20140221 |        0 |
    | changed query |  9007 |   4664 |      0 |        0 |           0 | 20140225 |         0 |        0 |        0 | 20140222 |

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now