RE: [sqr-users] sqr select variable problem

[Bob Stone <bstone@fastenal.com>]

Uh, isn't the SQL supposed to not be indented?  SQL flush to the left side.
Your 'FROM' and 'WHERE' look to be double indented...i dont know if that's
email formatting...but maybe try that.  

And, i dont think making your SQL dynamic would help at all.  It only makes
it more confusing when you send it to us.  I think you'd be better off
writing:

BEGIN-SELECT on-error=SQL-Error
A.business_unit &A.business_unit,
        let $CustBU = &A.business_unit
        show 'A.business_unit ' &A.business_unit
FROM ps_cust_data A, ps_item I
WHERE A.item = I.item
AND A.cust_id = I.cust_id
AND I.item_line = 1
AND A.cust_id = (select B.remit_from_cust_id from ps_cust_micr_tbl B
                 where B.micr_id = $MICR 
                 and B.EFFDT = ( select MAX (C.Effdt) from ps_cust_micr_tbl
C
                                 where C.remit_from_cust_id =
B.remit_from_cust_id
                                 and C.setid = B.setid
                                 and C.micr_id = B.micr_id
                                 and C.effdt <= $AsOfToday)
                 and B.setid = 'SHARE')
END-SELECT

**NOTE - subselects can be not against the left.  but your main select
should be hugging the wall there.


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Joey Ramion
Sent: Wednesday, November 10, 2004 6:04 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] sqr select variable problem


Hello all,

I have created what appears to be a very simple select statement in
sqr that returns only one value when run in sql plus on db2 however
when I run in sqr I cannot get it to populate my variable $CustBU when
I include all of the subselects. Please help!!!!

    let $Temp_String_1 = 'A.item = I.item'
        let $Temp_String_2 = 'and A.cust_id = I.cust_id'
        string 'and I.item_line = ' 1 ''
                by '' into $Temp_String_3
        let $Temp_String_4 = 'and A.cust_id = (select B.remit_from_cust_id'
        let $Temp_String_5 = 'from ps_cust_micr_tbl B'
        string 'where B.micr_id = ''' $MICR ''''
                by '' into $Temp_String_6
        let $Temp_String_7 = 'and B.EFFDT = (select MAX (C.Effdt)'
        let $Temp_String_8 = 'from ps_cust_micr_tbl C'
        let $Temp_String_9 = 'where C.remit_from_cust_id =
B.remit_from_cust_id'
        let $Temp_String_10 = 'and C.setid = B.setid'
        let $Temp_String_11 = 'and C.micr_id = B.micr_id'
        string 'and C.effdt <= ''' $AsOfToday ''')'
                by '' into $Temp_String_12
        string 'and B.setid = ''SHARE'')'
                by '' into $Temp_String_13
                
    string $Temp_String_1 $Temp_String_2 $Temp_String_3 $Temp_String_4
$Temp_String_5 $Temp_String_6 $Temp_String_7 $Temp_String_8
$Temp_String_9 $Temp_String_10 $Temp_String_11 $Temp_String_12
$Temp_String_13
           by ' ' into $Dynamic_Request_Where
           
show '$Temp_String_1 ' $Temp_String_1
show '$Temp_String_2 ' $Temp_String_2
show '$Temp_String_3 ' $Temp_String_3
show '$Temp_String_4 ' $Temp_String_4
show '$Temp_String_5 ' $Temp_String_5
show '$Temp_String_6 ' $Temp_String_6
show '$Temp_String_7 ' $Temp_String_7
show '$Temp_String_8 ' $Temp_String_8
show '$Temp_String_9 ' $Temp_String_9
show '$Temp_String_10 ' $Temp_String_10
show '$Temp_String_11 ' $Temp_String_11
show '$Temp_String_12 ' $Temp_String_12
show '$Temp_String_13 ' $Temp_String_13
show 'Before MICR ' $MICR  

begin-select on-error=SQL-Error
A.business_unit &A.business_unit,
        let $CustBU = &A.business_unit
        show 'A.business_unit ' &A.business_unit
        let $Return = 'Y'
        show ' $Return inner ' $Return
        show 'inner $MICR ' $MICR
                 from ps_cust_data A, ps_item I
                 where [$Dynamic_Request_Where]
end-select

P.S. I included the dynamic variable in an attempt to get the value
retiurned thinking that may have been my problem.

Thanks,

Joey

_______________________________________________
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