RE: [sqr-users] dynamic sql

["Turner, Ivan" <Ivan.Turner@qwest.com>]

I'm calling the procedure at the end of the program to find mismatched 
assignments and correct them.  To correct them I must reset $credit_class to a 
new value and call another procedure to insert the new value to another table.  

begin-SELECT ON-ERROR=SQL-Error

/*+ index(P PSMCUSTOMER) index(I PS_CUST_CREDIT) */
I.CUST_ID
  let #misassigned_accounts = #misassigned_accounts + 1
  show &i.cust_id ' in in credit class GG with no associated federal GAG'
  show 'Account being set to credit class A and customer type GB'
      Let $credit_class = 'A'
      do 250-update-ps-customer
      do 325-insert-credit-classFROM PS_CUST_CREDIT I, PS_CUSTOMER P
WHERE P.AR_CUST_TYPE_L IN ('GG','GO')
and I.SETID = 'LCI'
AND I.CUST_ID = P.CUST_ID
AND I.EFFDT = (SELECT MAX(EFFDT) 
          FROM PS_CUST_CREDIT 
          WHERE SETID = I.SETID 
          AND CUST_ID = I.CUST_ID)
AND I.CREDIT_CLASS = $credit_class
MINUS
SELECT TO_CHAR(MEMBER_ID)
FROM GROUP_MEMBERS
WHERE GROUP_ID IN (32319,32320,33236,35016,35017)
end-select

-----Original Message-----
From: sqr-users-bounces+ivan.turner=qwest.com@sqrug.org 
[mailto:sqr-users-bounces+ivan.turner=qwest.com@sqrug.org] On Behalf Of Charly 
Lebrun
Sent: Monday, August 30, 2004 11:16 AM
To: This list is for discussion about the SQR database reporting languagefrom 
Hyperion Solutions.
Subject: RE: [sqr-users] dynamic sql

 --- "Turner, Ivan" <Ivan.Turner@qwest.com> a écrit : 
> Thank you.  What I'm doing is changing the value of $credit_class inside the 
>select loop.  I was
> wondering if the cursor gets setup first and allows me to change the value of 
>$credit_class
> inside the select loop.
> 
> Again, I'm changing the value of $credit_class inside the select loop.  Is 
>that legal?
> 

Nope...

Think of big systems like Oracle and so on :
1. You write a query
2. You run (execute) it
3. If the query is SELECT then you may fetch through the cursor (the data).

You can change the value of the $credit_class variable, but unless you re-run 
the query (i.e. in
SQR : passing the BEGIN-SELECT statement), the cursor will not be updated.

You can either call again your procedure once the variable is updated, or, 
write another
procedure... I never tried to do recursive calls with SQR, and I'm not sure it 
is good idea (just
because the local/global memory areas management which is quiet strange... and 
the cursors seam to
be globals).

Charly


        

        
                
Vous manquez d'espace pour stocker vos mails ? 
Yahoo! Mail vous offre GRATUITEMENT 100 Mo !
Créez votre Yahoo! Mail sur http://fr.benefits.yahoo.com/

Le nouveau Yahoo! Messenger est arrivé ! Découvrez toutes les nouveautés pour 
dialoguer instantanément avec vos amis. A télécharger gratuitement sur 
http://fr.messenger.yahoo.com

_______________________________________________
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