Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Can't figure this out.

Posted on 2014-02-16
22
Medium Priority
?
241 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 74

Expert Comment

by:sdstuber
ID: 39863880
What is the question?

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

Author Comment

by:FutureDBA-
ID: 39863882
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 74

Expert Comment

by:sdstuber
ID: 39863897
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:FutureDBA-
ID: 39863903
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-
ID: 39863905
elm_dates does have saturday values

elm_dates saturdays
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39863907
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-
ID: 39863912
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-
ID: 39863913
however, the view is returning Saturday on saturday columns.

didnt think it to be important..

select all from elm_dates
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39863925
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 49

Expert Comment

by:PortletPaul
ID: 39863926
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-
ID: 39863927
requested results
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39863932
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-
ID: 39863938
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-
ID: 39863941
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 74

Expert Comment

by:sdstuber
ID: 39863942
>>  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-
ID: 39863944
saturday sum
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39863947
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-
ID: 39863948
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-
ID: 39863950
my apologies, i did a find / replace to edit out internal users... please see attached file
ELM-RMRVCP.SQL
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39864013
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-
ID: 39866363
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 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39869054
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

886 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