sharronWilliams
asked on
AS400 If Statement, modifiy a current statement to include IF statements
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
"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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is a workable solution, Thank you
ASKER
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.
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
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
ASKER
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
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
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
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