Re: [sqr-users] troubleshoot dynamic SQL

["Genady Sosnovsky" <genadys@ontko.com>]

Basically I'm forcing SQR to fail by providing  syntactically incorrect sql
code
This incorrect portion of the code is represented by

#ifdef showsql1
  type here anything that will invalidate the sql code.
#endif

You can name showsql1 variable the way you like it. It is not a sqr feature.
The simplest form of my suggestion would look like this

begin-select
...
from ...
where ...
/
end-select

where  "/"  char will invalidate the sql and will force the SQR to produce
an error log file where you can find you sql with all dynamic variables
resolved.


----- Original Message -----
From: "Ben Anderson" <pegandersonbd1@hotmail.com>
To: <sqr-users@sqrug.org>
Sent: Tuesday, March 25, 2003 8:44 AM
Subject: Re: [sqr-users] troubleshoot dynamic SQL


> could you possibly explain this a little more?  DO you have an example?
> What am I supposed to do inside of the:
> >#ifdef showsql1
> >   ...
> >#endif
> Thanks,
> Ben
>
>
> >From: "Genady Sosnovsky" <genadys@ontko.com>
> >Reply-To: sqr-users@sqrug.org
> >To: <sqr-users@sqrug.org>
> >Subject: Re: [sqr-users] troubleshoot dynamic SQL
> >Date: Tue, 25 Mar 2003 08:29:21 -0600
> >
> >a small correction to the example below
> >
> >#ifdef showsql1
> >   ...
> >#endif
> >
> >----- Original Message -----
> >From: "Genady Sosnovsky" <genadys@ontko.com>
> >To: <sqr-users@sqrug.org>
> >Sent: Tuesday, March 25, 2003 8:19 AM
> >Subject: Re: [sqr-users] troubleshoot dynamic SQL
> >
> >
> > > You can try to to add the following
> > > define a variable
> > > #define showsql1
> > >
> > > begin-select
> > > from
> > > order by
> > >
> > > ifdef showsql1
> > >   this will abort the normal execution, see error log for details
> > >   you will find you sql assembled
> > > endif
> > >
> > > end-select
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: "Ben Anderson" <pegandersonbd1@hotmail.com>
> > > To: <sqr-users@sqrug.org>
> > > Sent: Tuesday, March 25, 2003 7:58 AM
> > > Subject: [sqr-users] troubleshoot dynamic SQL
> > >
> > >
> > > > I was not familiar with the -S option so I just tried it out.  I'm
> >testing
> > > a
> > > > report right now that consists of a dynamic sql statement.  What I'm
> > > > wondering is if there is a way to print out the actual SQL statement
> > > instead
> > > > of the variable names.  Here is my output.
> > > >
> > > > Cursor #4:
> > > >    SQL = SELECT [$select_bd_trans_date], [$select_cli_cost_center],
> > > >          [$select_cli_pr_client_nbr], [$select_ph_clt_id],
> > > >          [$select_cli_client_name], [$select_ph_policy_nbr],
> > > >          [$select_ph_policy_id], [$select_btt_adj_type],
> > > >          [$select_bd_amount], [$select_btg_dr_cr]
> > > >    From [$cltinfo.client]
> > > >          [$wc.bank_detail] [$wc.bank_trans_type]
> >[$wc.bank_trans_group]
> > > >          [$ins.policy_history] Where
> > > >          [$link_cltinfo.client_TO_wc.bank_detail] and
> > > >          [$link_wc.bank_detail_TO_wc.bank_trans_type] and
> > > >          [$link_wc.bank_trans_group_TO_wc.bank_trans_type] and
> > > >          [$link_ins.policy_history_TO_wc.bank_detail] and
> > > >          [$query1_ON_ph_clt_id] and [$query1_ON_ph_policy_id] Order
by
> > > >          [$group1] [$order_by2]
> > > > Compiles = 0
> > > > Executes = 0
> > > > Rows     = 0
> > > >
> > > >
> > > >
> > > > >From: "George Jansen" <GJANSEN@aflcio.org>
> > > > >Reply-To: sqr-users@sqrug.org
> > > > >To: <sqr-users@sqrug.org>
> > > > >Subject: Re: [sqr-users] ORA-00937: not a single-group group
function
> > > > >Date: Tue, 25 Mar 2003 08:26:39 -0500
> > > > >
> > > > >Might I point out that the error you got is ORA-00936, "missing
> > > > >expression", not 937, the single-group etc.?
> > > > >
> > > > >What if you were to run this with the "-S" flag to see what cursor
> > > > >exactly is bombing?
> > > > >
> > > > >
> > > > >
> > > > >_______________________________________________
> > > > >sqr-users mailing list
> > > > >sqr-users@sqrug.org
> > > > >http://www.sqrug.org/mailman/listinfo/sqr-users
> > > >
> > > >
> > > > _________________________________________________________________
> > > > Protect your PC - get McAfee.com VirusScan Online
> > > > http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
> > > >
> > > > _______________________________________________
> > > > sqr-users mailing list
> > > > sqr-users@sqrug.org
> > > > http://www.sqrug.org/mailman/listinfo/sqr-users
> > > >
> > >
> > > _______________________________________________
> > > sqr-users mailing list
> > > sqr-users@sqrug.org
> > > http://www.sqrug.org/mailman/listinfo/sqr-users
> > >
> >
> >_______________________________________________
> >sqr-users mailing list
> >sqr-users@sqrug.org
> >http://www.sqrug.org/mailman/listinfo/sqr-users
>
>
> _________________________________________________________________
> Protect your PC - get McAfee.com VirusScan Online
> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users