Re: input parameters

["Thomas D. Hins" <Thomas-Hins@OUHSC.EDU>]

Lorraine,
  I believe if you change this if ($eccname != 0)
  to IF $eccname <>'' it should work.
HTH
Tom Hins
Information Technology - Application Solutions
University of Oklahoma Health Sciences Center
P.O. Box 26901
Oklahoma City, OK  73190
Phone:(405) 271-2262 Ext. 50213
Fax: (405) 271-2352



-----Original Message-----
From: Ambrosio Lorraine RDOG [mailto:Lorraine.Ambrosio@COMINCO.COM]
Sent: Friday, June 29, 2001 1:44 PM
To: SQR-USERS@list.iex.net
Subject: input parameters


Hello everyone,
I'm very new at SQR, no programming experience, only SQL.
Took the Brio SQR fundamental/advanced courses about 1 month ago , I'm
trying to get started with this one.

Note: there may be other problems with this script but my question is  with
the input parameters.

If the parameter 'eccname' is blank then select all records between
startdate and enddate otherwise
select eccname like '$eccname' within startdate and enddate.


Any help would be appreciated.
Thanks,
Lorraine


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++
begin-setup

!declare-layout
!       orientation = portrait
!end-declare


declare-variable
        date $startdate
        date $enddate
end-declare


begin-program
!       input $eccname 'Enter CCE Name(press <Enter> to select all):'
type=char
!        if ($eccname != 0)
!               let $ecctest = 'dbaprod.cid_eq_crct_cmplx_tb.ecc_desc like
'SAG%'
!        else let $ecctest = 'x'
!        end-if
        input #eccname 'Enter CCE Name(press <Enter> to select all):'
         let $whereclause = 'where dbaprod.cid_eq_crct_cmplx_tb.ecc_desc =
|| to_char(#eccname)'
!                                               or
dbaprod.cid_eq_crct_cmplx_tb.ecc_desc like || to_char(#eccname)'

!        else let $ecctest = 'x'
!        end-if
        input $startdate 'Enter Start Date:(DD-MON-YYYY)' type=date
format='DD-MON-YYYY'
        let #diff =datediff (datenow (), $startdate, 'day')
        input $enddate 'Enter End Date:(DD-MON-YYYY)' type=date
format='DD-MON-YYYY'
        let #diff =datediff (datenow (), $enddate, 'day')

                do cce_memo
end-program

begin-procedure cce_memo
begin-select distinct
dbaprod.cid_eq_crct_cmplx_tb.ecc_desc !(+1,{c1},
dbaprod.cid_eq_crct_cmplx_status_tb.ecc_stts_desc !(,{c2}) ,
dbaprod.cid_eq_crct_cmplx_notes_tb.effctv_dt !(+1,{c3}),
dbaprod.cid_eq_crct_cmplx_notes_tb.ecc_cmmnt !(,{c4})
        from    dbaprod.cid_eq_crct_cmplx_tb,
                        dbaprod.cid_eq_crct_cmplx_status_tb,
                        dbaprod.cid_eq_crct_cmplx_notes_tb
        where dbaprod.cid_eq_crct_cmplx_tb.ecc_id =
dbaprod.cid_eq_crct_cmplx_notes_tb.ecc_id
                and             dbaprod.cid_eq_crct_cmplx_notes_tb.effctv_dt
>= $startdate
                and     dbaprod.cid_eq_crct_cmplx_notes_tb.effctv_dt <=
$enddate
                and     dbaprod.cid_eq_crct_cmplx_tb.ecc_stts_cd =
dbaprod.cid_eq_crct_cmplx_status_tb.ecc_stts_cd
                and [$whereclause]
!group by    dbaprod.cid_eq_crct_cmplx_tb.ecc_desc,
dbaprod.cid_eq_crct_cmplx_status_tb.ecc_stts_desc,
!dbaprod.cid_eq_crct_cmplx_notes_tb.ecc_cmmnt,
!dbaprod.cid_eq_crct_cmplx_notes_tb.effctv_dt
order by        dbaprod.cid_eq_crct_cmplx_tb.ecc_desc asc
end-select
end-procedure