SQL JOIN Statement on AS400 1

[tboston]

I am trying to run the following SQL query on an AS400:

update cdbtstd_aa16nt
set cdbtstd_aa16nt.aa1qa = (aa1aqa + ' ' + aa1ara + ' ' + aa1asa)
from istestlib_psr0104 inner join cdbtstd_aa16nt
on cdbtstd_aa16nt.aaefa = istestlib_psr0104.aaefa
and cdbtstd_aa16nt.aaama = istestlib_psr0104.aaama
and cdbtstd_aa16nt.aaeba = istestlib_psr0104.aaeba
and cdbtstd_aa16nt.aa1aoa = istestlib_psr0104.aa1aoa

When checked in SQL server the query parses correctly leading me to believe that the syntax is correct. However, when I run the query on the AS400 I get the following error message:&quot;Keyword FROM not expected. Valid tokens: <END-OF-STATEMENT>&quot;

Anyone have any thoughts regarding running this query on an AS400?

Thanks,
tb

 

[swampBoogie]

Some non-standard syntax in that query

It depends on in which table the columns aa1qa, a1ara and aa1asa are located. If they all are in the istestlib_psr0104 table:

update cdbtstd_aa16nt
set aa1qa = (select aa1qa  || ' ' || aa1ara || ' ' || aa1asa
from istestlib_psr0104 
where cdbtstd_aa16nt.aaefa = istestlib_psr0104.aaefa
and cdbtstd_aa16nt.aaama = istestlib_psr0104.aaama
and cdbtstd_aa16nt.aaeba = istestlib_psr0104.aaeba
and cdbtstd_aa16nt.aa1aoa = istestlib_psr0104.aa1aoa

Actually I don't see why you should do the update at all. Do the concatenation when retrieving data.

 

[swampBoogie]

One further thing though if you do the update I forget a where condition

update cdbtstd_aa16nt
set aa1qa = (select aa1qa  || ' ' || aa1ara || ' ' || aa1asa
from istestlib_psr0104
where cdbtstd_aa16nt.aaefa = istestlib_psr0104.aaefa
and cdbtstd_aa16nt.aaama = istestlib_psr0104.aaama
and cdbtstd_aa16nt.aaeba = istestlib_psr0104.aaeba
and cdbtstd_aa16nt.aa1aoa = istestlib_psr0104.aa1aoa)

where exists (select * from istestlib_psr0104.aaefa
where cdbtstd_aa16nt.aaefa = istestlib_psr0104.aaefa
and cdbtstd_aa16nt.aaama = istestlib_psr0104.aaama
and cdbtstd_aa16nt.aaeba = istestlib_psr0104.aaeba
and cdbtstd_aa16nt.aa1aoa = istestlib_psr0104.aa1aoa)

 

[tboston]

Thanks, that worked!

You may or maynot have noticed but I was trying to run a Microsoft T-SQL query. Obviously, the syntax is different with ANSI-SQL. Do you have suggestions for reference material?

Also when you say that I should run the concatenation when retreiving the data rather than running the update, do you mean run the select statement first and then update the field with the concatenated information?

thanks again for the help,
tb

 

[swampBoogie]

Also when you say that I should run the concatenation when retreiving the data rather than running the update, do you mean run the select statement first and then update the field with the concatenated information?

No, I mean that you shouldn't do any update at all. There is no need to store the same data in multiple places. And how do you make sure that the data is always kept up to date?

 

[r937]

&lt;blockquote&gt;&lt;h4&gt;Quote:&lt;/h4&gt;

how'd you do that, that is so cool!!

rudy

http://r937.com/

SQL Consulting

 

[RiverGuy]

tboston,

Go to this website to learn about AS400/DB2 implementation

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmst02.htm