Re: select works in SQL but not in SQR

[Clara Carter <clarac@RAINMAKERSYSTEMS.COM>]

I think it has to do with your date formats in the to_date functions.
You are telling it to expect it in the 'mm/dd/yyyy' format but you
are passing it '22-JAN-99'. So then the conversion is wrong.
This is what it's reading in:

and to_date('22-JAN-99','mm/dd/yyyy')

when I think it should be

and to_date('22-JAN-99','MM-MON-YY')

Just a start....

Clara


where project_id = $project_id
>      and dist_dt > to_date($asof_dt,'mm/dd/yyyy')
>      and to_date($proc_we_dt,'mm/dd/yyyy') >=
>to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy')
>      and to_date($proc_we_dt,'mm/dd/yyyy') < to_date($asof_dt,'mm/dd/yyyy')

At 12:32 PM 7/6/99 -0700, you wrote:
>Greetings SQRUG Gurus!
>
>Can anybody help me see what is not working here?
>
>In SQLPlus, I can use
>
>      select proc_we_dt, prod_comm_cur
>        from ps_dist_summary
>        where project_id = '10001'
>        and proc_we_dt >= '22-JAN-99'
>        and proc_we_dt < '19-FEB-99'
>
>and I get the following data:
>
>      PROC_WE     PROD_COMM_CUR
>      --------- ---------------
>      22-JAN-99           193.20
>      29-JAN-99           361.34
>      05-FEB-99           361.54
>      12-FEB-99           240.92
>      22-JAN-99            -9.06
>      29-JAN-99            -5.06
>      05-FEB-99            -9.06
>      12-FEB-99            -9.08
>
>      8 rows selected.
>
>but in my SQR, I have:
>
>      begin-select
>      to_char(proc_we_dt,'mm/dd/yy') &proc_we_dt_fm
>      round(sum(prod_comm_cur), 0) &prod_comm_cur
>      from  ps_dist_summary
>      where project_id = $project_id
>      and dist_dt > to_date($asof_dt,'mm/dd/yyyy')
>      and to_date($proc_we_dt,'mm/dd/yyyy') >=
>to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy')
>      and to_date($proc_we_dt,'mm/dd/yyyy') < to_date($asof_dt,'mm/dd/yyyy')
>
>      group by to_char(proc_we_dt,'mm/dd/yy')
>
>      order by to_char(proc_we_dt,'mm/dd/yy')
>
>      end-select
>
>And I get no data.
>
>I use '10001' for $project_id
>      '22-JAN-99' for to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy')
>  and '19-FEB-99' for to_date($asof_dt,'mm/dd/yyyy')
>
>AGGGGHHHH!!!!!!!
>
>Maybe my brain is fried from looking at the monitor for so long, but that
>shoe sales job at the mall is looking better and better!
>
>TIA
>
>Joe Johnson
>jejohn1216@surfree.com
>
>~~ JEJ ~~   ;{)
>
>--------------------------------------------------------
>$14.95 Unlimited Internet Access, http://www.surfree.com