RE: [sqr-users] dynamic sql

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

You're starting with one value of $credit_class and telling your database to
return a set of rows.  The database collects the set of rows and begins to
feed them to you one at a time.  You can change the value of $credit_class
and that will not change the set of rows you're going to get.  Your database
doesn't know what's happening to variables in memory.  It only knows what
you send it in SQL statements.  I think that's what you want in this case.

This brings up the question of whether you should do what you can do.
Changing the value of $credit_class in this way could lead to programming
bugs and maintenance difficulty.  The value of $credit_class after this
select statement executes depends on whether there are any rows selected,
which you can't tell from the program logic.  Why not use $new_credit_class?

-----Original Message-----
From: Turner, Ivan [mailto:Ivan.Turner@qwest.com]
Sent: Monday, August 30, 2004 8:27 AM
To: This list is for discussion about the SQR database reportinglanguage
fr omHyperion Solutions.
Subject: RE: [sqr-users] dynamic sql


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

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