AS400 If Statement, modifiy a current statement to include IF statements

sharronWilliams
sharronWilliams used Ask the Experts™
on
AS400 Sql Statement need to be modified. To look in 2 different files before it looks in the below file for the correct address.

                "SELECT pat.bdat, pat.lname, pat.pname, pat.pmidi, pat.medrcc, " +
                "pat.race, pat.sexcd, pat.status, pat.pssno, pat.pdocno, pat.garno, pat.fmnum, " +                                                                          
                "pat.pcode1, pat.pphon1, pat.pcode2, pat.pphon2, gar.addr1, gar.addr2, " +
                "gar.city, gar.st, gar.zipcd " +
                "FROM TCRAR001.patmasl pat " +
                "LEFT OUTER JOIN TCRAR001.garmasl gar " +
                "ON pat.garno = gar.garno", connection);



The first file  the statement should look at is
TCRAR001/CONTAC1 - Contact authorization by account, if there is no address in this file it would then look at TCRAR001/PATMASL
  File  . . . : CONTAL1                  Member . . : CONTAL1                 Record Length . :      376  
   Library . : TCRAR001                 Format . . : CONTAR                  File Access . . :  Keyed    
 Page# . . . :  1 of  2                 Mode . . . : Display                 Record Number . :     42804
 Control . . .                          Text . . . : Contact authorization by account                    
                                                                                                                                                                               
          Account #           20                                        
               Fm #           602                                              
           Standard                                                    
   Standard W/email                                                    
      Email Address                                                    
         Phone Only  X                                                  
          Area Code  951                                                
            Phone #  6561171                                            
   Alt Address Flag                                                    
          Address 1                                                    
          Address 2                                                    
               City                                                    
              State                                                    
           Zip Code                                                    
   City-ref Zipcd+4                                                    
          Area Code                                                    
 
2nd file to look at
TCRAR001/Patmasl      
                                       DATA BASE UTILITY (DBU)                                                      
File  . . . : PATMASL                  Member . . : PATMASL                 Record Length . :      520              
  Library . : TCRAR001                 Format . . : PATMAS                  File Access . . :  Keyed                
Page# . . . :  1 of  5                 Mode . . . : Display                 Record Number . :     19517              
Control . . .                          Text . . . : Patient Master Logical File By GA                                
                                                                                                                     
            Clinic Number    1                                                                                      
         Guarantor Number            3                                                                              
     Family-member Number      1                                                                                    
 
Patient Address 1  3044 HORACE ST                                                                            
         Patient Address 2                                                                                            
         Patient Address 3                                                                                            
              Patient City  RIVERSIDE                                                                                  
    Patient Home Area Code  714                                                                                        
  Patient Employ Area Code                                                                                            
     Patient Msg Area Code                                                                                            
     Primary Doctor Number                                                                                            
   Patient Employment Code                                                                                            
   Patient Employment Date  0001-01-01                                                                                
          Patient Employee              


If there is an address here that is where it would stop, but if not it would go on to the address that is in TCRAR001/GARMASL as it is now in the above sql statement.  

Can I change the above sql statement to do what I need done, or must this b
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
sharronWilliams:

You have the SQL quoted and show "+" signs to indicate continuation of lines. Is this embedded SQL? Are you preparing a statement? If so, what language?

What condition tells you there "is no address" in the tables? Are the values blank? ...null? ... some special value?

Tom

Author

Commented:
It will be embedded in C# by an outside programmer.
I am working in the AS400 preparing this SQL Statement so that it can be embedded to pull the data from the AS400 by the program into an XML file.  This out side programmer really isn't familiar with our AS400 program.
I am an Analyst, who is trying to lead this programmer, after our programmer left in the middle of this project.  I am the new project lead, I know were the files live and I am able to write queries to produce information but I am not a programmer.  
What should happen is that this embedded sql statement will pull from the AS400 the correct data, and then transfer the data into XML.  Our programmer/Analyst left and was in a hurry and forgot this important part.
The fields in the AS400 files are blank, if it is blank it should look at the next file, if that is blank then it should look at the last file which houses the ACCOUNT number's Address, but it might not be the address that the client wants contacted, if the client has instructed us to call at a certain address it would be in the first file or maybe the second file.  This is a health facility and we must be HIPPA compliant when sending information about a client by mail or phone.
sharronWilliams:

Not being anything resembling a C# developer, I can only make a general suggestion.

The SELECT includes [gar.addr1] explicitly in the column list along with other address columns from the guarantor table. Consider instead a CASE structure --

  case when pat.addr1 = "  " then gar.addr1 else pat.addr1 end as ADDR1,
  case when pat.addr2 = "  " then gar.addr2 else pat.addr2 end as ADDR2,

You might want to test each address column separately like above or have them all conditioned on pat.addr1. Also, if you must cascade through three tables or more, you'll probably need a nested structure of CASEs.

If the columns report as NULL, you might use [ coalesce(pat.addr1, gar.addr1, othertable.addr1,"*NOADDRESS") ] for a cascading series of tests.

Heh... I figured HIPAA was involved as soon as I recognized the Patient/Guarantor structure. Lots of fun.

Tom
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Author

Commented:
This is a workable solution, Thank you

Author

Commented:
Tom,
Ok, I think I know something and then I go blank.  Do I define CASE in DEFINE RESULT of the query builder. .
"case when pat.addr1 = "  " then gar.addr1 else pat.addr1 end as ADDR1,
case when pat.addr2 = "  " then gar.addr2 else pat.addr2 end as ADDR2,"
sounds great but I am very green at this but learning everyday.  I do not know how to define .  I tried and tried, it sound so good but you have to know were to do this and apparently I don't.  it was worth the 500 points just to get an idea.  Thank you,  if you can just point me were I would do the above.  
Sharron:

Again, I'm not familiar on the C# side, so I can only give a suggested sample. You originally had:

                "SELECT pat.bdat, pat.lname, pat.pname, pat.pmidi, pat.medrcc, " +
                "pat.race, pat.sexcd, pat.status, pat.pssno, pat.pdocno, pat.garno, pat.fmnum, " +                                                                          
                "pat.pcode1, pat.pphon1, pat.pcode2, pat.pphon2, gar.addr1, gar.addr2, " +
                "gar.city, gar.st, gar.zipcd " +
                "FROM TCRAR001.patmasl pat " +
                "LEFT OUTER JOIN TCRAR001.garmasl gar " +
                "ON pat.garno = gar.garno", connection);

From that, I would think in terms of:

                "SELECT pat.bdat, pat.lname, pat.pname, pat.pmidi, pat.medrcc, " +
                "pat.race, pat.sexcd, pat.status, pat.pssno, pat.pdocno, pat.garno, pat.fmnum, " +                                                                          
                "pat.pcode1, pat.pphon1, pat.pcode2, pat.pphon2, " +
                    "case when pat.addr1 = ""  "" then gar.addr1 else pat.addr1 end as ADDR1, " +
                    "case when pat.addr2 = ""  "" then gar.addr2 else pat.addr2 end as ADDR2, " +
                "gar.city, gar.st, gar.zipcd " +
                "FROM TCRAR001.patmasl pat " +
                "LEFT OUTER JOIN TCRAR001.garmasl gar " +
                "ON pat.garno = gar.garno", connection);

In short, a CASE structure simply replaces the column in the column list. Where you originally had [ gar.addr1 ], you would replace it with a CASE structure. It _might_ also need to be CAST() to the appropriate data type, but that's another guess on my part. You'll have an indication of whether or not that's required when the statement runs. I wouldn't expect it to be needed.

Tom

Author

Commented:
Tom,

Ok now we are cooking, I showed this to the our outside programmer and he said he knew what to do, and understood

I tried it in strqrl  without the + and it pulls the data.  So he is embedding it into the C# thank you again.  I would give you another 500 if possible.

Sharron

Sharron:

Glad it helped. SQL is useful but different clients have their quirks. CASE is a standard, and a standard feature is the first to try. It should work in nearly all clients, so testing in interactive STRSQL helps know what to do in another client even if the detailed syntax is a bit off (semi-colons, quotes, continuation, etc.)

Don't hesitate to continue extending this questions if problems arise. A 500-pointer gets privileges.

Tom

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial