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

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

Subject: Re: [sqr-users] Re: Passing a Variable Value as an ExpressionStringto DECODE(), is it possible?
From: Daniel Vandenberg <vandberg@uwosh.edu>
Date: Tue, 12 Sep 2006 13:12:35 -0500
Delivery-date: Tue, 12 Sep 2006 14:15:20 -0400
In-reply-to: <f7e108920609111421i74c6c2b5n8f119ddf67c0af1b@mail.gmail.co m>
List-id: "This list is for discussion about the SQR database reportinglanguage from Hyperion Solutions." <sqr-users.sqrug.org>
References: <62AFC819CCF3D611827A00065B3F7981101F95A2@sj_exchange.sjcity.net><f7e108920609111421i74c6c2b5n8f119ddf67c0af1b@mail.gmail.com>

Making another begin-select procedure works, but I think something like the 
following would be simpler and would not require as many calls to the database.

BEGIN-SELECT ON-ERROR =PROCESS_ERROR

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

     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

         LET $ABB = LTRIM(RTRIM($ABB,''),'')
         IF &bl.abbreviation = $ABB
              PRINT 'Y' (, +5)
         ELSE
              PRINT 'N' (, +5)
         END-IF

         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


At 04:21 PM 9/11/2006, you wrote:
>a MILLION thanks Steven!
>
>Well, lesson learned.
>>The Begin-Select loop forms a SQL statement once, and sends it to your
>>database server once.
>
>That was my logical error.
>
>I took your advice and encapsulated the MAX(DECODE) section into it's
>own begin-select procedure block.
>Then I simply called it from within my original begin-select
>procedure...so that it ran for each record returned therein, making
>sure to pass some key params, and viola!...same effect reached!!!
>
>in retrospect, that seems like such a simple solution, I guess I was
>being stubborn and just banging my head against the wall trying to get
>it to work the way I wanted.  In reality, I was just making the
>problem too compicated
>
>thanks again, to you and everyone else who provided input!!!
>
>
>
>On 9/11/06, Alexander, Steven <Steven.Alexander@sanjoseca.gov> wrote:
>>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
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org
>http://www.sqrug.org/mailman/listinfo/sqr-users


Daniel Vandenberg
Administrative Computing
University of Wisconsin Oshkosh

Email :  vandberg@uwosh.edu 



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

References:
- RE: [sqr-users] Re: Passing a Variable Value as an Expression Stringto DECODE(), is it possible?
  - From: "Alexander, Steven" <Steven.Alexander@sanjoseca.gov>
- Re: [sqr-users] Re: Passing a Variable Value as an ExpressionStringto DECODE(), is it possible?
  - From: "Saffiulla Mohamed" <mohigro@gmail.com>

Prev by Date: RE: [sqr-users] SQR Report + Java | Help
Next by Date: [sqr-users] Return a user defined PL/SQL table to Brio?
Previous by thread: Re: [sqr-users] Re: Passing a Variable Value as an ExpressionStringto DECODE(), is it possible?
Next by thread: [sqr-users] Return a user defined PL/SQL table to Brio?
Indexes: [ Thread] [ Author] [ Date]
SQR-USERS List and Archive Info
SQRUG Home Page