Linked to AS400 from SQL Server 1

[eselhassan]

Guys,

I'm dooing an openquery after successfully creating a linked server using iSeries ODBC driver as follows:

Select * from OpenQuery(MyServer, ' ')

I need some one to help me rewrite the following query in the above OpenQuery statement. Here is my AS400 query which by the works just fine in retrieving data inside the AS400

SELECT adarnb.01, adagtx.01, adahcd.01, adaicd.01, avclas.02, avhscd.02, avevla.02, avevbl.02, avevob.02
FROM Library/aparl7, Library/aavlul1
JOIN adarnb.01=avbonb.02 AND adaynb.01=avskey.02
WHERE adarnb.01=2003


Thanks for any help and all suggestions.

 

[JayKusch]

May try this, but I am not sure it will suite your needs ...


SELECT [adarnb.01],
[adagtx.01],
[adahcd.01],
[adaicd.01],
[avclas.02],
[avhscd.02],
[avevla.02],
[avevbl.02],
[avevob.02]
FROM Library.DBO.aparl7
JOIN Library.DBO.aavlul1
ON [adarnb.01] = [avbonb.02]
AND [adaynb.01] = [avskey.02]
WHERE [adarnb.01] = 2003




Thanks

J. Kusch

 

[eselhassan]

Thanks Jay for the quick response. I tried your suggestion and I got the follwoing error:

Token [ was not valid. Valid tokens: ( + * - ? : DAY RRN CASE CAST CHAR DAYS HOUR LEFT TRIM USER YEAR.]

I'm not an AS400 programmer, so please help me through this. Oh by the way is there a book, manual, website that I can take a look at for learning the syntax. Thanks

 

[lienyi]

SELECT adarnb, adagtx,adahcd, adaicd, avclas,
avhscd, avevla, avevbl, avevob
FROM Library.aparl7
JOIN Library.aavlul1
ON adarnb = avbonb
AND adaynb = avskey
WHERE adarnb = 2003

** check your as/400 ODBC drive setup. usually the / after the library in native mode is replaced by a period as ANS92 standard. But in case you have changed it back to /, then just replace the it with Libarary/apar17 etc.

 

[eselhassan]

Thanks to both Jay and lienyi. lienyi you are exactly right when I replaced the period with / it worked. You guys are good. Can you guys point me to a book or manual on how those queries work on the AS400. I appreciate that.


Els2

 

[eselhassan]

Thanks to both Jay and lienyi. lienyi you are exactly right when I replaced the period with / it worked. You guys are good. Can you guys point me to a book or manual on how those queries work on the AS400. I appreciate that.


Els2

 

[lespaul]

There's a book: SQL/400 Developer's Guide by Paul Conte & Mike Cravitz that we have around our office that comes in handy for writing SQL to the AS400.