Re: DB2 where clause disappearing

[Jarrod Blomberg <jblomberg@AAMI.COM.AU>]

The first thing I would do is take out the comments from within the select.


Here is a sample of one of my db2 selects.


 Let $WhereClause = ' WHERE xxxxMNTH = ' || $xxxxMonth || ' AND TOT > 2'
 Let $OrderBy     = ' ORDER BY RPCSC_NM, REPAIRNM'

Begin-Select
RPCSC_NM &RPCSC_NM
REPAIRNM &REPAIRNM
PEBOT25  &PEBOT25
TOT      &TOT
EQTE     &EQTE
ONEQTE   &ONEQTE
  Do Print-Detail
  FROM REPAIRER.RPPICKUP
  [$WhereClause]
  [$OrderBy]
End-Select




-----Original Message-----
From: Discussion of SQR, Brio Software's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Paul Hoyte
Sent: Thursday, 28 March 2002 09:53
To: SQR-USERS@list.iex.net
Subject: DB2 where clause disappearing


Hello all:

DB2 V7 with SQR 6.1.3

I was just wondering if anyone in the DB2 environment had run into this
problem.  The entire where clause disappears during the sql compilation
check before the SQR is executed causing a DB2 error as follows:

The Error message:

(SQR 5528) ODBC SQL dbsql: SQLPrepare error -495 in cursor 2:
   [IBM][CLI Driver][DB2] SQL0495N  Estimated processor cost of
"000000000626" processor seconds ("000096885952" service units) in cost
category "A" exceeds a resource limit error threshold of "000001000000"
service units.  SQLSTATE=57051

SQL: SELECT ALGN_XREF_CD, CNTR_LGCY_ID,
DVLP.BSC_RPT_CONTROL.BSC_MEAS_SECT_ID,
     FIS_YP, SECT_PERCENT_WT, WT_PERCENT, LINE_SEQ_NUM, LINE_TYPE,
     LINE_SUPPRESS, DVLP.BSC_RPT_CONTROL.BSC_MEAS_ID, ON_TARGET, PRD_ACTUAL,
     PRD_RATING, QTD_ACTUAL, QTD_RATING, YTD_ACTUAL, YTD_RATING, LINE_DESC
     From  DVLP.BSC_RPT_CONTROL, DVLP.BSC_SUMMARY_RPT

Error on line 297:
   (SQR 3716) Error in SQL statement.

Errors were found in the program file.

SQR: Program Aborting.


The following is the where clause that disappears:

>From  DVLP.BSC_RPT_CONTROL, DVLP.BSC_SUMMARY_RPT
Where DVLP.BSC_RPT_CONTROL.BRAND_SHRT_NM =
DVLP.BSC_SUMMARY_RPT.BRAND_SHRT_NM
 And DVLP.BSC_RPT_CONTROL.REPORT_ID = DVLP.BSC_SUMMARY_RPT.REPORT_ID
            And DVLP.BSC_RPT_CONTROL.BSC_MEAS_SECT_ID =
DVLP.BSC_SUMMARY_RPT.BSC_MEAS_SECT_ID
            And DVLP.BSC_RPT_CONTROL.BSC_MEAS_ID =
DVLP.BSC_SUMMARY_RPT.BSC_MEAS_ID
 And FIS_YP = 200202
 And DVLP.BSC_RPT_CONTROL.BRAND_SHRT_NM = 'TBC'
 And DVLP.BSC_RPT_CONTROL.REPORT_ID = '080'
    [$where1]
    [$where2]
 !and dvlp.bsc_summary_rpt.algn_xref_cd     = $entity_code   !$where1
 !and dvlp.bsc_summary_rpt.cntr_lgcy_id     = $legacy_code  !$where2

order by dvlp.bsc_summary_rpt.algn_xref_cd ,
         dvlp.bsc_summary_rpt.cntr_lgcy_id ,
         dvlp.bsc_rpt_control.bsc_meas_sect_id ,
         dvlp.bsc_rpt_control.line_seq_num

End-Select


Has anyone run into this problem, and does anyone have a sample of DB2 code
that used dynamic where clauses that I can look at?  Any solutios to this?

Regards to all,


Paul Hoyte
Sharidionne, Inc.
(248) 559-6868
www.sharidionne.com
phoyte@sharidionne.com


*****************************************************************
THIS MESSAGE HAS BEEN VIRUS SCANNED BY MAILSWEEPER.
*****************************************************************





----------------------------------------------------------------------------
This email contains information confidential to AAMI Limited.
If you are not the intended recipient, you must not disclose
or use the information contained in it. If you have received
this email in error, please notify us immediately by return email
and delete this email and any attached documents.
AAMI Limited is not responsible for any changes made to a document
other than those made by AAMI Limited or for the effect of the
changes on the documents meaning.
----------------------------------------------------------------------------