Re: select works in SQL but not in SQR

["Sarabudla, Raji R." <Raji.R.Sarabudla@KP.ORG>]

Hi Joe,

The Select Syntax in SQR is not correct, you need not format the Database
columns in where clause. you
can simply use those columns and format String Dates (SQR Date Variables)
against the Database Columns in
where clause.

May be the following Select clause works for you,


      Begin-procedure main
        begin-select
        to_char(proc_we_dt,'mm/dd/yyyy') &proc_we_dt_fm
        round(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 proc_we_dt >= to_date($proc_we_dt_dtu_limit,'mm/dd/yyyy')
        and  proc_we_dt < to_date($asof_dt,'mm/dd/yyyy')
        order by proc_we_dt
        end-procedure main

   If you want use the Group by Columns, the syntax has to be changed and
the Group columns have to be verified
   before using any Group columns.

  Hope this may atleast help.

 Thanks,

 Raji


----------
From:  Joe [SMTP:jejohn1216@SURFREE.COM]
Sent:  Tuesday, July 06, 1999 3:32 PM
To:  Multiple recipients of list SQR-USERS
Subject:  select works in SQL but not in SQR

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