Re: Pass back values from a SQL statement

[Paul Hoyte <phoyte@SHARIDIONNE.COM>]

Denise:

Here is a snippet of code that works using dynamic columns -

!
!    this include will process the request for a single
!    alignment
!
!

Begin-Procedure GET_INDIVIDUAL_ALIGNMENT

   Print-Direct printer=html '%%ResetColor'
   Print-Direct printer=html '%%ResetBorder'

Begin-Select

    Alter-Printer Font=901 Point-Size=10    ! [SQR.INI} 901=MS Shell
Dlg,proportional

!OPS
[$column1]      &level2_code=CHAR       ()  on-break level=1 print=never
[$column2]      &level2_id=CHAR         ()  on-break level=1 print=never


!DIVISION/TERRITORY
[$column3]      &level3_code=CHAR       ()  on-break level=2 print=never
[$column4]      &level3_id=CHAR         ()  on-break level=2 print=never


!REGION/ZONE
[$column5]      &level4_code=CHAR       ()  on-break level=3 print=never
[$column6]      &level4_id=CHAR         ()  on-break level=3 print=never


!MKT
[$column7]      &level5_code=CHAR       ()  on-break level=4 print=never
[$column8]      &level5_id=CHAR         ()  on-break level=4 print=never


!AREA/RSM
[$column9]      &level6_code=CHAR       ()  on-break level=5 print=never
[$column10]     &level6_id=CHAR         ()  on-break level=5 print=never


!STORE
[$column111]     &level7_code=CHAR       ()  on-break level=6 print=never
[$column122]     &leveL7_id=CHAR        ()  on-break level=6 print=never



   Print-Direct printer=html '%%ResetColor'

!       set up the header fields for the single alignment
!       fields will be blank if heading does not apply

        move &leveL7_id         to      $level7_legacy_heading
        move &leveL6_id         to      $level6_legacy_heading
        move &leveL5_id         to      $level5_legacy_heading
        move &leveL4_id         to      $level4_legacy_heading
        move &leveL3_id         to      $level3_legacy_heading
        move &leveL2_id         to      $level2_legacy_heading


>From  DVLP.CSTCNTR_ALGN_PER DVLP

Where DVLP.BRAND_SHRT_NM        =   'TBC'
  And DVLP.ALGNMNT_CD           =   'OPS'
  And DVLP.ROLL_STRUC_TYP_CD    =   'OPS'
  And DVLP.AGRMT_CD             =   'COM'
  And DVLP.CSTCNTR_STAT_CD      =   'OPN'
  And DVLP.CSTCNTR_SPCL_CD      <>  'MSC'
  And DVLP.ROW_OUTEFF_DT        =   '12/31/9999'
  And DVLP.ACTV_ALGNMNT_IN      =   'A'

  AND [$SELECT_COLUMN1]          =   $starting_alignment_id


Hope this helps


Paul Hoyte
Sharidionne, Inc.
(248) 559-6868
www.sharidionne.com
phoyte@sharidionne.com

-----Original Message-----
From: Discussion of SQR, Brio Software's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Denise Goin
Sent: Wednesday, April 03, 2002 6:55 PM
To: SQR-USERS@list.iex.net
Subject: Re: Pass back values from a SQL statement


I have tried different variations of the begin-select, and as long as the
[$fieldname] is in there it errors-

Error on line 122:
   (SQR 3743) Dynamic columns must have a &pseudonym.
[$field]

Error on line 129:
   (SQR 4702) Line found outside paragraph.
end-procedure GET-VALUES


This might just be a SYBASE  problem, the previous suggestion from David-
begin-sql
select [$fieldname] as fieldone
INTO $somefieldtostorevalues
FROM [$FROM]
where [$WHERE]
end-sql
doesn't work in SYBASE either.

I am looking into doing -

begin-sql On-Error=VALUE-ERROR
insert into PS_Z_AUDIT_TEMP
select [$fieldname]

FROM [$FROM]
where [$WHERE]

end-sql

begin-select
AUDITFIELD

  let $AUDIT_FIELD = &AUDITFIELD
  show $FIELDNAME '|' $AUDIT_FIELD '|'

from PS_Z_AUDIT_TEMP
end-select