selecting data from the as400

gnivkor
gnivkor used Ask the Experts™
on
okay, so i am a bit confused as to how to select data from an as400 because of the structure of the libraries on the as400, i have been successful
in selecting data by using

select column_name from library.table

but when i do inner joins on a query i must specify all three and i am not sure how to do this, if a library was not involved i would be doing "inner join table_name.column_name"

but i cannot inner join by doing

library.table.column

---------

if the above does not make any sense, please let me know and i will try to clarify
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can't join columns tho you can join tables by specifying the column to be used when joining.

select column from schema.tableX inner join schema.tableY on tableX.pk_column = tableY.pk_column

as a result you need to use :  "inner join table_name ON column_name = OTHER_TAB_COL_NAME"
Taking in consideration you posted the question in "Oracle Database" i just assumed you are using Oracle SQL.

Thanks,
  Dan.

Author

Commented:
i am using oracle, connected to as400 via odbc. link is working properly, i am just having problems with certain data

on the as400
my libraries are rap003003 and rapfiles
my tables are IDCODEQRY and CUSMAS
everything else is columns, when i run my access converted to SQL qry, i get the follow error

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 8 Column: 2

Open in new window


this is my oracle sql
select 
  CM_PRIMRY_ROUTNO AS ROUTE, 
  CM_CUSTNO AS CUSTOMER_NUMBER,
  CM_CUSTMR_NAME AS NAME,
  CM_CUSTMR_ADDR_1 AS ADDRESS,
  CM_TKTMSG AS TICKET_MSG,
  ID_DESCRP AS DESCRIPTION
  case when CM_PRIMRY_DLVSEQ_1 > 0 then 'M' else ' ' end  as MON,
  case when CM_PRIMRY_DLVSEQ_2 > 0 then 'T' else ' ' end  as TUE,
  case when CM_PRIMRY_DLVSEQ_3 > 0 then 'W' else ' ' end  as WED,
  case when CM_PRIMRY_DLVSEQ_4 > 0 then 'H' else ' ' end  as THU,
  case when CM_PRIMRY_DLVSEQ_5 > 0 then 'F' else ' ' end  as FRI,
  case when CM_PRIMRY_DLVSEQ_6 > 0 then 'S' else ' ' end  as SAT,
FROM RAP003003.CUSMAS@"orcl.mycompany.com" INNER JOIN RAPFILES.IDCODEQRY@"orcl.mycompany.com" ON CUSMAS.CM_TKTMSG = IDCODEQRY.ID_FLDNUM  
WHERE LIBRARY_TABLE.CM_PRIMRY_ROUTNO Not In (74,999) 
AND RAP003003.CUSMAS.CM_TKTMSG <> 4 
AND RAP003003.CUSMAS.CM_NOWDUE > =1000
AND RAPFILES.IDCODEQRY.ID_FLDLBL = 'TKTMSG'
AND RAP003003.CUSMASTABLE.CM_BILCOD= 2; 

Open in new window



This is the MS access SQL
SELECT 
	RAP003003_CUSMAS.CM_PRIMRY_ROUTNO AS ROUTE, 
	RAP003003_CUSMAS.CM_CUSTNO AS [CUST#], 
	RAP003003_CUSMAS.CM_CUSTMR_NAME AS NAME, 
	RAP003003_CUSMAS.CM_CUSTMR_ADDR_1 AS ADDRESS, 
	RAP003003_CUSMAS.CM_TKTMSG AS [TKT MSG], 
	RAPFILES_IDCODEQRY.ID_DESCRP AS DESCRIPTION, 
		IIf([CM_PRIMRY_DLVSEQ_1]>0,"M"," ") AS MON, 
		IIf([CM_PRIMRY_DLVSEQ_2]>0,"T"," ") AS TUE, 
		IIf([CM_PRIMRY_DLVSEQ_3]>0,"W"," ") AS WED, 
		IIf([CM_PRIMRY_DLVSEQ_4]>0,"H"," ") AS THU, 
		IIf([CM_PRIMRY_DLVSEQ_5]>0,"F"," ") AS FRI, 
		IIf([CM_PRIMRY_DLVSEQ_6]>0,"S"," ") AS SAT, 
	RAP003003_CUSMAS.CM_NOWDUE AS BALANCE, 
	RAP003003_CUSMAS.CM_SLSMAN AS [SALES REP]
FROM RAP003003_CUSMAS INNER JOIN RAPFILES_IDCODEQRY ON RAP003003_CUSMAS.CM_TKTMSG = RAPFILES_IDCODEQRY.ID_FLDNUM
WHERE (((RAP003003_CUSMAS.CM_PRIMRY_ROUTNO) Not In (74,999)) 
	AND ((RAP003003_CUSMAS.CM_TKTMSG)<>4) 
	AND ((RAP003003_CUSMAS.CM_NOWDUE)>=1000) 
	AND ((RAPFILES_IDCODEQRY.ID_FLDLBL)="TKTMSG") 
	AND ((RAP003003_CUSMAS.CM_BILCOD)=2))
ORDER BY RAP003003_CUSMAS.CM_NOWDUE DESC;

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>RAP003003_CUSMAS.CM_SLSMAN AS [SALES REP]

Oracle does not use square brackets.
You can easily reproduce the error without the link: select 'Hello' as [hello world] from dual;


Try:
RAP003003_CUSMAS.CM_SLSMAN AS SALES_REP

Author

Commented:
got it going, proper code is

select 
  CM_PRIMRY_ROUTNO AS ROUTE, 
  CM_CUSTNO AS CUSTOMER_NUMBER,
  CM_CUSTMR_NAME AS NAME,
  CM_CUSTMR_ADDR_1 AS ADDRESS,
  CM_TKTMSG AS TICKET_MSG,
  ID_DESCRP AS DESCRIPTION,
  case when CM_PRIMRY_DLVSEQ_1 > 0 then 'M' else ' ' end  as MON,
  case when CM_PRIMRY_DLVSEQ_2 > 0 then 'T' else ' ' end  as TUE,
  case when CM_PRIMRY_DLVSEQ_3 > 0 then 'W' else ' ' end  as WED,
  case when CM_PRIMRY_DLVSEQ_4 > 0 then 'H' else ' ' end  as THU,
  case when CM_PRIMRY_DLVSEQ_5 > 0 then 'F' else ' ' end  as FRI,
  case when CM_PRIMRY_DLVSEQ_6 > 0 then 'S' else ' ' end  as SAT,
  CM_NOWDUE AS BLNC, 
	CM_SLSMAN AS SALES_REP
FROM RAP003003.CUSMAS@"orcl.mycompany.com" INNER JOIN RAPFILES.IDCODEQRY@"orcl.mycompany.com" ON CUSMAS.CM_TKTMSG = IDCODEQRY.ID_FLDNUM  
WHERE CUSMAS.CM_PRIMRY_ROUTNO Not In (74,999) 
AND RAP003003.CUSMAS.CM_TKTMSG <> 4 
AND RAP003003.CUSMAS.CM_NOWDUE > =1000
AND RAPFILES.IDCODEQRY.ID_FLDLBL = 'TKTMSG'
AND RAP003003.CUSMAS.CM_BILCOD= 2; 

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>question be closed by accepting gnivkor's comment #37857478 (0 points)

Is this how you meant to close this?  If so, I'm afriad I'll have to object.

Even if you might have had the Inner Join suggested by CarlsbergFTW, you still needed help woth the syntax issue.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Why did you accept http:#a37856061.  It has nothing to do with the solution nor does it aid in solving the syntax error.

Since I am involved in the question, I'll have a Zone Advisor take a look at this.

Author

Commented:
the solution i chose to award was the most relevant to me solving the problem at hand
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
gnivkor,

Can I ask why I did not get some of the points for solving your syntax error you posted in http:#a37857415 ?
I would like to point out that you (slightwv) posted an oracle syntax error on the MS access select:

EXPERT COMMENT
by: slightwvPosted on 2012-04-17 at 11:48:50ID: 37857462

>>RAP003003_CUSMAS.CM_SLSMAN AS [SALES REP]

Oracle does not use square brackets.
You can easily reproduce the error without the link: select 'Hello' as [hello world] from dual;


Try:
RAP003003_CUSMAS.CM_SLSMAN AS SALES_REP

Open in new window


Actually there are only 2 minor syntax errors in the Oracle SQL posted. Before the 1st case a "," is missing and there is an extra "," before 'FROM'.

that was actually throwing this error:
ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 8 Column: 2
                             

Open in new window


My apologies if my comment is unnecessary but from my understanding, the experts community should try to help others fix their issues, not hunting for points - points come as a bonus.

If you truly believe you brought contribution to the matter by posting an oracle syntax error on a ms access sql you might want to reconsider.

Regards,
  Dan.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
CarlsbergFTW,

Thanks for point this out.  I now see my mistake.

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