[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

RE: [sqr-users] Re: sqr select variable problem

I don't start my "from" in column 1.  Are you sure about those rules?  I
thought only the selected columns had to start in column 1, and after SQR
sees "from", it doesn't take any more selected columns or any more SQR code.

-----Original Message-----
From: White, Denise [mailto:DEWhite@vicr.com]
Sent: Monday, November 15, 2004 1:33 PM
To: SQR Users Group (E-mail)
Subject: [sqr-users] Re: sqr select variable problem


Hi Joey,

I agree with Bob.  You need to start the 'from' and the 'where' clauses in
column one.  I think that people were getting too focused on figuring out
your dynamic stuff, and overlooked a very basic rule.  I also agree with
removing the comma from after the column you are selecting.  If you are
selecting multiple variables, it is optional to use commas if you have each
column on a separate line (or you can put multiple columns per line if you
use commas), but in either case there should never be one after the last
column.  Not guaranteeing that it will work then (don't have time to analyze
the dynamic stuff!), but at least you will be following SQR syntax rules.

HTH,

Denise M. White
Sr. Software Engineer
HR/Payroll Systems
Vicor Corporation
------------------------------

Message: 3
Date: Thu, 11 Nov 2004 08:47:41 -0600
From: Joey Ramion <joey.ramion@gmail.com>
Subject: Re: [sqr-users] sqr select variable problem
To: "This list is for discussion about the SQR database reporting
        language        from Hyperion Solutions." <sqr-users@sqrug.org>
Message-ID: <b9fab530041111064728cd6a91@mail.gmail.com>
Content-Type: text/plain; charset=US-ASCII

Bob,

Hey and thanks. The variable was added after I had first tried
withouta variable and with all of the sql flush. I am going to play
with the spacing some more but I am starting to think this is a DB2
issue.

Thanks for the advice.

Joey


On Thu, 11 Nov 2004 08:37:57 -0600, Bob Stone <bstone@fastenal.com> wrote:
> 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