SQL Syntax issue via Free-Form RPGLE

690 pts.
Tags:
RPGLE
SQL
SQL Syntax
SQLRPGLE
I'm having difficulty with a select statement.  As below, it works .  If I remove the '//' comment-out tags, it doesn't, so obviously there's an issue with those statements.  I'm trying to populate an output 3-char field with either Yes, No, or N/A.  The Y/N comes from the contents of a field from one file, the N/A comes from the contents of a field from a different file.   So, if the contents of cdfcod from file ccccd00 equals 'N', then I want 'N/A' in the output field.  Otherwise, if the contents of rhrfp from file cccrh01 equals 'Y', then output is 'Yes'.  If contents of rhfrp does not equal 'Y', then output is 'No'.
// create recordsetsqlTxt = 'select ' + 'char(rhterm, 10), ' + 'rhcon#, char(odlgth), ' + '(select char(cd.cddesc,13) FROM ccccd00 cd ' + 'where cd.cdgrcd = 824 ' + 'and cd.cdcode = rh.rhcode), ' + 'case ' + //     'when (select char(cd.cdfcod,1) from ccccd00 cd '         + //               'where cd.cdgrcd = 824 ' + //              'and cd.cdcode = rh.rhcode) = ''N'' ' + //              'then char(''N/A'',   3) ' + 'when      rh.rhrfp = ''Y'' then char(''Yes'', 3) ' + 'when      rh.rhrfp <> ''Y'' then char(''No'', 3) ' + 'end, ' + 'from cccrh01 rh '


Software/Hardware used:
v7r1

Answer Wiki

Thanks. We'll let you know when a new response is added.

A SQL syntax error could arise just from the length of the sqlTxt length value once the indicated comment tags are removed.

Discuss This Question: 10  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
  • philpl1jb
    I think this is what you were after ..
    select char(rhterm, 10) rhcon#, char(odlgth) 
    (select char(cd.cddesc,13) FROM ccccd00 cd where cd.cdgrcd = 824 and cd.cdcode = rh.rhcode),
      case when (select char(cd.cdfcod,1) from ccccd00 cd where cd.cdgrcd = 824 and cd.cdcode = rh.rhcode) = 'N'
           then 'N/A' 
           when rh.rhrfp = 'Y' then 'Yes' 
           when rh.rhrfp <> 'Y' then 'No'
       end 
       from cccrh01 rh      
    49,435 pointsBadges:
    report
  • philpl1jb

    This might be a simpler way to get it done

    select char(rhterm, 10) rhcon#, char(odlgth) char(cd.cddesc,13),
      case when char(cd.cdfcod,1) = 'N' then 'N/A' 
           when rh.rhrfp = 'Y' then 'Yes' 
           when rh.rhrfp <> 'Y'then 'No'
       end 
       from cccrh01 rh join cccd00 cd 
         on cd.cdcode = rh.rhcode
       where cd.cdgrcd = 824            

     

    49,435 pointsBadges:
    report
  • philpl1jb

    oops left out a comma

    select char(rhterm, 10) rhcon#, char(odlgth), 
      char(cd.cddesc,13),
      case when char(cd.cdfcod,1) = 'N' then 'N/A' 
           when rh.rhrfp = 'Y' then 'Yes' 
           when rh.rhrfp <> 'Y'then 'No'
       end 
       from cccrh01 rh join cccd00 cd 
         on cd.cdcode = rh.rhcode
       where cd.cdgrcd = 824            
    49,435 pointsBadges:
    report
  • RegnadKcin57
    The scalar subselect in the WHEN is not completed, per missing a right parenthesis before the equal symbol for that equal predicate. Also, presumably an issue introduced due to trimming the remainder of the actual select-clause, trying to limit the RPG expression, there is also an extraneous comma before the FROM. As to the recommendation to replace the two scalar subselects with a join, be aware that a LEFT OUTER JOIN to the file from which the data comes currently from the subselects, is the more accurate way to mimic them.
    95 pointsBadges:
    report
  • philpl1jb

    if cd.cdgrcd = 824 and cd.cdcode = rh.rhcode would fail to find a record and you still want the record .. then as suggested left join or left outer join should be used.

    With that case you will probably need to use coalesce from all fields from cccd00 becaue they will be null when the matching row isn't found.

    49,435 pointsBadges:
    report
  • pat400

    The matching row is definitely found, as it correctly passes the field above the one failing.  Yes, there is code snipped out, which is why there is a comma after the end.

    There is already a join on cesrh01, and as a newbie to SQL trying to join another file to it is proving to be beyond me at this time.  I tried several different syntaxes, and all of them failed.  So, I'd rather keep it as is for now, but I still don't know why that piece of code keeps failing.  I'm not sure what you mean, regnadkcin5, by the missing parenthesis.  I don't see one missing?

    75 pointsBadges:
    report
  • philpl1jb

    Run the code in my first box as an inteactive sql

    It will find the errors for you.

     

     

    49,435 pointsBadges:
    report
  • RegnadKcin57
    Apologies for the implication that there was a missing parenthesis.  I copied the text again... and this time when I reformatted the code for readability of the concatenation requests making up the expression of the RPG assignment i.e. the SQLTEXT = string-expression, I verified that the parenthesis is there.

    By the way, what exactly does "doesn't work" mean?  Does the statement fail in a PREPARE or an EXECUTE IMMEDIATE with the SQLCODE=-104?  If so, what is the value of SQLERRD(5) which should diagnose, according to the documentation, "the position of a syntax error"?
    95 pointsBadges:
    report
  • TomLiotta

    So far, most important is "...what exactly does 'doesn’t work' mean?" The question title refers to "syntax". Is it a SQL syntax error or a RPG syntax error? When does the error show up? At compile-time? At run-time? If a message is sent, what is the message identifier?

    The SQL could be more efficient (possibly a lot more), but we need to know what any error is before digging much deeper.

    Note that the entire SQL statement should be shown. In addition, the definition of the sqlTxt variable could also be needed. A SQL syntax error could arise just from the length of the full variable value once the indicated comment tags are removed.

    Tom

    125,585 pointsBadges:
    report
  • pat400

    Note that the entire SQL statement should be shown. In addition, the definition of the sqlTxt variable could also be needed. A SQL syntax error could arise just from the length of the full variable value once the indicated comment tags are removed.

    Tom, that was it.  It was defined too small.  Thank you so much!

     

    75 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