Select with SQL in iSeries Free Format

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 recordset                                          
   sqlTxt = '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 '                                                                         +
nickmbnabbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
second last line has a trailing comma after end, remove that comma

This is validated syntax using http://www.sql-format.com/ 
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

Open in new window

0
nickmbnabbAuthor Commented:
I'm so sorry, I snipped out some of the code that I thought was unnecessary to the issue here.  There is another case,when group before the final 'end ', so I think I need that comma there.
0
PortletPaulfreelancerCommented:
>>"I snipped out some of the code that I thought was unnecessary to the issue here"
not a great idea I'm afraid

>>As below, it works .  
no, it will fail due to the trailing comma

>>If I remove the '//' comment-out tags, it doesn't,
to track that down requires the "as is" code that concatenates the sql string.

If the full code is long please contain it within a "code block", look for Code in the toolbar above the text box, click this and the cursor should be in the middle of 2 tags, paste your code there.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

nickmbnabbAuthor Commented:
Below is the actual code.  Sorry that the '+' signs at the end got out of line when I pasted.  To reiterate, it works as is below.  It does not work when I remove the '//' from the lines in question.                                                                                    

   // create recordset                                          
   sqlTxt = '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, '                                +
                                                                         
  'ifnull(case '                                    +     
               'when odextm < ''1'' then null '          +                
               'when odextm > ''6'' then null '                +                
               'when odextm = ''6'' then '                  +                
                   '(select cd.cdcdes from ccccd00 cd '      +                
                      'where cd.cdgrcd = 805 '               +                
                        'and cd.cdcode = od.odstat) '      +                
               'when odextm > ''0'' then '                        +                
                   '(select cd.cdcdes from ccccd00 cd '     +                
                      'where cd.cdgrcd = 804 '                  +                
                        'and cd.cdcode = od.odstat) '        +                
     'end, char('' '', 30) ), '                      +                
     'case when od.odecms = ''C'' and '       +                
     'ifnull((select exclear from cccex00 ex '       +                
               'where ex.exfil# = od.odfil# '           +                
                 'and rrn(ex) = '                       +                
        '(select max(rrn(ex2)) from cccex00 ex2 '                      +                
           'where ex2.exfil# = od.odfil# ) ) , ''xx'') = ''01'' '       +                
                     'then char(''Cleared'', 20) '                               +                
           'when od.odecms = ''H'' then char(''Held'',           20) ' +                
           'when od.odecms = ''S'' then char(''Seized'',         20) ' +                
           'when od.odecms = ''X'' then char(''Cancelled'', 20) ' +                
           'else char('' '', 20)   end '             +        
                                                                                       
             'from cesrh01 rh '                   +  
             '                join cccod00 od '      +  
             ' on rhcon# = odcon# '             +  
             'and odstat < ''9'' '            +
              'order by ' + curr_sort_seq      +  
             ' FOR READ ONLY ';        

Open in new window

0
PortletPaulfreelancerCommented:
Hi, thanks for the whole script. I stripped out the concatenations and no obvious syntax errors show up - that's good. So going back to the original point of your question - I'm not sure why you would get an error; the only thing I can think of is that a subquery in the selection list MUST only return one value and perhaps the one you have commented out wasn't doing that.

So, for both of those subqueries I have introduced a MAX( ... existing code ... ) which will ensure only a singe value is returned. OR, you could remove the MAX(   ) and introduce a "LIMIT 1" instead.

I've done my best to put the concatenation back in the following but it might not be 100%, so you may just want to edit your existing code for the suggested MAX( ) [or LIMIT 1] rather than rely on my code:
sqlTxt = '' +
    'SELECT CHAR(rhterm, 10) ' +
    '   , rhcon# ' +
    '   , CHAR(odlgth) ' +
    '   , ( SELECT MAX(CHAR(cd.cddesc, 13)) ' +
    '   FROM ccccd00 cd ' +
    '   WHERE cd.cdgrcd = 824 ' +
    '   AND cd.cdcode = rh.rhcode ' +
    '   ) ' +
    '   , CASE ' +
    '      WHEN ' +
    '         ( SELECT MAX(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 ' +
    '   , ifnull( ' +
    '   CASE ' +
    '      WHEN odextm < ''1'' THEN NULL ' +
    '      WHEN odextm > ''6'' THEN NULL ' +
    '      WHEN odextm = ''6'' THEN ' +
    '         ( SELECT cd.cdcdes ' +
    '         FROM ccccd00 cd ' +
    '         WHERE cd.cdgrcd = 805 AND cd.cdcode = od.odstat ' +
    '         ) ' +
    '      WHEN odextm > ''0'' THEN ' +
    '         ( SELECT cd.cdcdes ' +
    '         FROM ccccd00 cd ' +
    '         WHERE cd.cdgrcd = 804 AND cd.cdcode = od.odstat ' +
    '         ) ' +
    '   END, CHAR('' '', 30)) ' +
    '   , CASE ' +
    '      WHEN od.odecms = ''C'' AND ifnull( ' +
    '         ( SELECT exclear ' +
    '         FROM cccex00 ex ' +
    '         WHERE ex.exfil# = od.odfil# AND rrn(ex) = ' +
    '            ( SELECT MAX(rrn(ex2)) ' +
    '            FROM cccex00 ex2 ' +
    '            WHERE ex2.exfil# = od.odfil# ' +
    '            ) ' +
    '         ) ' +
    '         , ''xx'') = ''01''    THEN CHAR(''Cleared'', 20) ' +
    '      WHEN od.odecms = ''H'' THEN CHAR(''Held'', 20) ' +
    '      WHEN od.odecms = ''S'' THEN CHAR(''Seized'', 20) ' +
    '      WHEN od.odecms = ''X'' THEN CHAR(''Cancelled'', 20) ' +
    '      ELSE                      CHAR('' '', 20) ' +
    '   END ' +
    'FROM cesrh01 rh ' +
    'INNER JOIN cccod00 od ON rhcon# = odcon# AND odstat < ''9'' ' +
    'ORDER BY ' + curr_sort_seq +
    'FOR READ ONLY';
    

Open in new window

here is a raw sql version, slightly different in format as it depends on which tool I use (I used sqlinform for the concatenation output, sql-format.com & manual for the format below
SELECT
       CHAR(rhterm, 10)
     , rhcon#
     , CHAR(odlgth)
     , (
          SELECT MAX(CHAR(cd.cddesc, 13))
          FROM ccccd00 cd
          WHERE cd.cdgrcd = 824 AND cd.cdcode = rh.rhcode
          )
     , CASE 
          WHEN (
                    SELECT MAX(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
     , ifnull(CASE 
               WHEN odextm < '1' THEN NULL
               WHEN odextm > '6' THEN NULL
               WHEN odextm = '6' THEN (
                                      SELECT cd.cdcdes
                                      FROM ccccd00 cd
                                      WHERE cd.cdgrcd = 805
                                           AND cd.cdcode = od.odstat
                                      )
               WHEN odextm > '0' THEN (
                                      SELECT cd.cdcdes
                                      FROM ccccd00 cd
                                      WHERE cd.cdgrcd = 804
                                           AND cd.cdcode = od.odstat
                                      )
               END, CHAR(' ', 30))
     , CASE 
          WHEN od.odecms = 'C'
               AND ifnull((
                         SELECT exclear
                         FROM cccex00 ex
                         WHERE ex.exfil# = od.odfil#
                              AND rrn(ex) = (
                                   SELECT MAX(rrn(ex2))
                                   FROM cccex00 ex2
                                   WHERE ex2.exfil# = od.odfil#
                                   )
                         ), 'xx') = '01' THEN CHAR('Cleared', 20)
          WHEN od.odecms = 'H'           THEN CHAR('Held', 20)
          WHEN od.odecms = 'S'           THEN CHAR('Seized', 20)
          WHEN od.odecms = 'X'           THEN CHAR('Cancelled', 20)
          ELSE CHAR(' ', 20)
          END
FROM cesrh01 rh
INNER JOIN cccod00 od ON rhcon# = odcon# AND odstat < '9'
ORDER BY curr_sort_seq
FOR READ ONLY  

Open in new window

I notice now that I missed putting MAX( ) in some, only the first 2 were changed.

Hope this helps.
0
nickmbnabbAuthor Commented:
I just realized what it was....the sqlTxt field was defined as 1024 -- too small to hold what I was adding.  I changed it to 2056 and all is well.

It's always something stupid.  :(
0
PortletPaulfreelancerCommented:
:)
yep! ain't that the truth. well done. Cheers, Paul
0
PortletPaulfreelancerCommented:
oh, but you still want to protect your overall query by limiting the result of those subqueries

use either an aggregate function or 'limit 1'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
Thanks, much appreciated. Glad I could add something of value :) Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.