Re: [sqr-users] SQR Variable not being read by SQL

["William Beckner" <wbeckner@ccc.edu>]

Misha,
 
Try putting square brackets around the variable name $NewCodeString in your SQL 
block so it looks like this:
...
and tbbdetc_detail_code not in ([$NewCodeString]))
...
 
 
 
William Beckner (Just call me Bill)
Network Support Analyst
Office of Information Technology
City Colleges of Chicago
312-553-3235

>>> Misha Franklin <franklim@ohsu.edu> 7/11/2007 3:27 PM >>>
I have replaced some hard-coded values in the SQL block with a variable
name that is populated by SQR.  We don't see any errors when the users
run the program, but it is not generating the expected results.  It
appears that SQL doesn't 'read' the variable value, which is a series of
codes from an array that I single quote and comma delimit:

LET $SQ = ''''                     ! $SQ = one single quote

  WHILE #CurrSdaxCt <= #MaxSdaxCt
    GET $CodeString FROM sdax_excl_codes(#CurrSdaxCt)
    IF $CodeString !=''
      IF #CurrSdaxCt =0
        LET $NewCodeString = $SQ || $CodeString || $SQ
      ELSE
        LET $NewCodeString = $SQ || $CodeString || $SQ || ',' ||
$NewCodeString
      END-IF
      ADD 1 to #CurrSdaxCt
    END-IF
  END-WHILE
END-PROCEDURE

(so $NewCodeString = 'F01','F02','F03','F04')

The SQL Block looks like this:

begin-sql on-error=wbrbnfa_insert_error
insert into wbrbnfa
(
wbrbnfa_term_code,
wbrbnfa_pidm,
wbrbnfa_detail_code,
wbrbnfa_sum_amount,
wbrbnfa_source,
wbrbnfa_activity_date,
wbrbnfa_session_id)
(
select
tbraccd_term_code,
tbraccd_pidm          ,
tbraccd_detail_code   ,
sum(tbraccd_amount)   ,
'TBRACCD'             ,
sysdate               ,
$fa_session
from tbraccd
where tbraccd_term_code = $job_term
and tbraccd_detail_code in
(select tbbdetc_detail_code
from tbbdetc
where tbbdetc_dcat_code = 'FA'
and tbbdetc_detail_code not like ('F6%')
and tbbdetc_detail_code not like ('F9%')
and tbbdetc_detail_code not in ($NewCodeString))
group by tbraccd_term_code, tbraccd_pidm, tbraccd_detail_code
union all
select
tbrmemo_term_code    ,
tbrmemo_pidm         ,
tbrmemo_detail_code  ,
sum(tbrmemo_amount)  ,
'TBRMEMO'            ,
sysdate              ,
$fa_session
from tbrmemo
where tbrmemo_term_code = $job_term
and tbrmemo_detail_code in
(select tbbdetc_detail_code
from tbbdetc
where tbbdetc_dcat_code = 'FA'
and tbbdetc_detail_code not like ('F6%')
and tbbdetc_detail_code not like ('F9%')
and tbbdetc_detail_code not in ($NewCodeString))
group by tbrmemo_term_code, tbrmemo_pidm, tbrmemo_detail_code
)
end-sql

After I replaced the text 'F01','F02','F03','F04 that was originally in
the program with my $NewCodeString variable, the program stopped
excluding the F values in the variable (and this is bad).  I am printing
the $NewCodeString variable in my control report, and it looks good, so
I think that SQL isn't decoding (bad choice of words?) the variable. 
After searching the archives and googling for a while, I am still at a
loss.  Does anyone have any ideas (even resources I should check out if
I'm missing important basic SQR/SQL foundational information).

Thanks in advance,






Misha Franklin
Software Applications Analyst
Academic & Research Applications
Information Technology Group
Oregon Health & Sciences University
1515 SW 5th Avenue
Portland, Oregon 97201
503-418-3244


_______________________________________________
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