RE: [sqr-users] Re: Passing a Variable Value as an Expression Stringto DECODE(), is it possible?

["Alexander, Steven" <Steven.Alexander@sanjoseca.gov>]

The Begin-Select loop forms a SQL statement once, and sends it to your
database server once.  The loop is for each row that the statement returns.
Perhaps you can put the Begin-Select loop inside the
#PRINT_TRANSLATION_DETAIL_COUNTER while-loop.

-----Original Message-----
From: Saffiulla Mohamed [mailto:mohigro@gmail.com] 
Sent: Monday, September 11, 2006 10:15 AM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Subject: Re: [sqr-users] Re: Passing a Variable Value as an Expression
Stringto DECODE(), is it possible?

Thanks for the input Dave,

I've included the procedure in question below.  It includes debugging
show statements.  I have also included the resulting show output.

The variable substitution is occurring within a loop, however, it's
not based on a &column_var from the same SQL statement.  Rather, I'm
attempting to execute a loop that replaces a variable in the
MAX(DECODE) statement.  That variable is being pulled from an array.
I guess my issue may have more to do with when SQR compiles the sql
statements, and maybe what I'm trying isn't possible with this tool.

Here's whole procedure:

!---------------------------------------------------------------------------
-----
!PROCEDURE: PRINT_TRANSLATION_DETAIL
!---------------------------------------------------------------------------
-----
BEGIN-PROCEDURE PRINT_TRANSLATION_DETAIL (#RESORT)

    LET #PRINT_TRANSLATION_DETAIL_COUNTER = 0

    SHOW
'---------------------------------------------------------------------'
    SHOW 'BEGIN SELECT FOR ' #RESORT

BEGIN-SELECT ON-ERROR =PROCESS_ERROR

    POSITION (+1) ! ADVANCE 1 LINE FROM CURRENT POSITION
PG.PKG_NAME
(,1)

    WHILE #PRINT_TRANSLATION_DETAIL_COUNTER < #_LANG_COUNT
        MOVE '' TO $ABB         !RESET $ABB

        LET $ABB =
UPPER(LANG_COL_ARRAY.ABBREVIATION(#PRINT_TRANSLATION_DETAIL_COUNTER))

     SHOW '>' $abb '<' #PRINT_TRANSLATION_DETAIL_COUNTER

max (decode(bl.abbreviation,
LTRIM(RTRIM($ABB,''),''),'Y',bl.abbreviation,'N')) LANGUAGE (,+5)

        ADD 1 TO #PRINT_TRANSLATION_DETAIL_COUNTER

    END-WHILE

    MOVE 0 TO #PRINT_TRANSLATION_DETAIL_COUNTER

FROM PM_RESORT_TRANSLATION RT, PM_TRANS_LANGUAGE TL,
PM_TRANS_BWI_LANGUAGE BL, PM_RESORT_PKG_GENERIC PG

WHERE TL.PM_TRANS_BWI_LANGUAGE_ID = BL.PM_TRANS_BWI_LANGUAGE_SEQNO
AND   RT.PM_TRANS_LANGUAGE_ID = TL.PM_TRANS_LANGUAGE_SEQNO
AND   RT.CONTENT_IDENTIFIER = PG.PKG_GENERIC_SEQNO
AND   RT.RESORT = #RESORT
GROUP BY PG.PKG_NAME!, RT.RESORT

END-SELECT

    SHOW
'---------------------------------------------------------------------'
    SHOW 'END SELECT FOR ' #RESORT

END-PROCEDURE
!---------------------------------------------------------------------------
-----------------------------------------

When I run the above I get the following debugging output:

BEGIN PROCESSING AT 09:58:00
---------------------------
BEGIN SELECT FOR 10.000000
>FR<0.000000
>DE<1.000000
>IT<2.000000
>ES<3.000000
>ZH<4.000000
>JA<5.000000
>KO<6.000000
>B7<7.000000
>NL<8.000000
>FI<9.000000
>PT<10.000000
>SV<11.000000
END SELECT FOR 10.000000
---------------------------

According the to output, I am getting the values intended for the variable.

I thought about your statement regarding the error in using the loop
within the select.  So I decided to try a single MAX(DECODE)
statement, within which I passed a variable only once.  So, instead of
having a loop, I simply said:
Let $ABB = 'FR'
and then used $ABB in a single MAX(DECODE) statement.  My thought was,
that if my error was attempting the loop, and since I could see that
my variables were returning accurate values, that should work.
However, when I ran that modified test...the pattern matching still
failed!...The only way it worked, was when i replaced the variable all
together and just used the literal 'FR' in the statement.  That's a
major reason why I'm wondering about possible conversion issues when
the SQL statement is actually being created.

At anyrate, I will keep trying some things, and definitely report back
with whatever solution eventually works...

keep bringing the suggestions though, I really appreciate everyone's
time and consideration!! :)


On 9/9/06, David Donnelly <Dave@isisbio.com> wrote:
> Saffiula,
>
> First, do put some debugging show statements in to be sure you're getting
> what you expect in your variable.
>
> You shouldn't need to use dynamic SQR (square brackets); simple
> substitution should work.
>
> Remember though, that this substitution is done ONCE, before the select
> starts.  Sometimes newbies try to do this:
>
> begin-select
>
> abbreviation    &code
> max (decode(bl.abbreviation, TRIM(&code),'Y',bl.abbreviation,'N'))  (,+5)
>
> from bl
>
> etc
>
> and this will not work.
>
> Maybe you should show us more of the select statement, and the loop if it
> is outside the select.
>
> Also, as usual, if you have any local procedures, make sure you have
> indicated $_global-variables correctly.
>
> Dave
>
>
> _______________________________________________
> 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