Using a variable in the LABEL ON SQL statement

180 pts.
Tags:
AS/400 SQL
RPGLE
SQL
SQL Server
Is it possible to use a variable in the label-string of the LABEL ON sql statement so that the variable can be changed dynamically in an SQLRPGLE program ?

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Are you already reasonably familiar with ILE RPG and embedded SQL? If so, all you probably need to know is that a SQL LABEL statement can be PREPAREd and EXECUTEd with a :host variable reference. If this is for column headings, the variable should 60 characters. The first 20 chars are for part 1 of the heading; the second 20 are for part 2; and the third for part 3. Parts 2 & 3 are used for the 2nd and 3rd heading line if the heading is that big. I don't have an example handy, but one should be easy enough if really necessary. I don't think I've ever done column heading changes in a program. Tom
    125,585 pointsBadges:
    report
  • aderene
    I am already familiar with ILE RPG and embedded SQL, but I have never user LABEL ON in a program. Here some sample logic from my program. d label_on1 s 47a INZ('label on column ARDBF##033/ARP+ d ATB2 (A2OVR30 IS ') * The LABEL continued here d label_cont ds d label_tick1L 1 1a INZ('''') Left Tick d label_over 2 6a INZ('OVER ') d label_day 7 9s 0 INZ(0) d label_tick1R 10 10a INZ('''') Right Tick * label_on2 field will hold the full label on statement * from label_on1 and label_cont d label_on2 s 100a INZ(' ' ) c label_on1 cat label_cont label_on2 MY PROGRAM DOES NOT COMPILE BECAUSE OF THE BELOW LOGIC. WHAT AM I MISSING ? c/exec sql c+ prepare sel from : label_on2 c/end-exec c/exec sql c+ label on from sel c/end-exec
    180 pointsBadges:
    report
  • TomLiotta
    ...a SQL LABEL statement can be PREPAREd and EXECUTEd with a :host variable reference Unfortunately, your report of an error got me looking at details. It appears that the actual column heading string itself must physically be a string-constant. That is, the complete statement can be constructed dynamically and executed, but the blasted column heading itself isn't allowed to be passed in as a parameter! How stupid is that? (Not to mention my missing it in the first place.) Apparently, you have to create the entire LABEL on statement as a SQL statement string and execute that as a dynamic statement if the column heading itself needs to be variable. Kind of makes me wonder why the statement is allowed to be PREPARED and EXECUTEd in the first place. What good is preparing that statement when the useful piece is restricted? My apologies for only going half the way the first time. This is actually tested:
         D myLabel         s             60
         D SQLStmt         s            128
          /free
           myLabel = 'Customer            Number' ;
           SQLStmt = 'LABEL ON COLUMN mylib/QCUSTCDT (CUSNUM IS ''' +
                     myLabel +''' )' ;
    
           exec sql EXECUTE IMMEDIATE :SQLStmt                              ;
           exec sql COMMIT                                                  ;
    
            *inLR = *on ;
            return ;
          /end-free
    No reliance on documentation without seeking implications! Tom
    125,585 pointsBadges:
    report
  • aderene
    Thanks for trying Tom. I think your 2nd suggestion below just might work. Will keep you posted.
    180 pointsBadges:
    report
  • TomLiotta
    BTW, those marks that "look like" double-quotes in the code above are actually consecutive apostrophes (two single-quotes). I wish there was a way to get apostrophes across only as apostrophes rather than having them be transformed into leading- and trailing-quotes or as double-quotes. Just be aware. Tom
    125,585 pointsBadges:
    report
  • aderene
    My program contains the following code. The program compiles and executes, but for some reason it does not update the column A2OVR30. I set on STRDBG to follow the execution of the program and do not see any problems. d label_on1 s 47a INZ('label on column ARDBF##033/ARP+ d ATB2 (A2OVR30 IS ') * The LABEL continued here d label_cont ds d label_tick1L 1 1a INZ('''') Left Tick d label_over 2 6a INZ('OVER ') d label_day 7 9s 0 INZ(0) d label_tick1R 10 10a INZ('''') Right Tick * label_on2 field will hold the full label on statement * from label_on1 and label_cont d label_on2 s 100a INZ(' ' ) eval key_aging# = 'STATB'; chain (key_aging#) arlap; the value of field APAGE1is obtained from file arlap and moved to statement label_cont eval label_day = APAGE1; // Concatenate fields: label_on1 & label_cont // into field label_on2 label_on2 = label_on1 + label_cont; exec sql EXECUTE IMMEDIATE :label_on2; exec sql COMMIT;
    180 pointsBadges:
    report
  • aderene
    Tom Please ignore my last email. I found the probem. I overlooked something in my code. The program works now.
    180 pointsBadges:
    report
  • TomLiotta
    Well, whatever you're doing looks interesting. Glad it's working. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following