asked on

ASp page gets error while retrieving recordset from Oracle source

I have an ASP page that has 2 recordsets.
1 works and the 2nd does not.
I created the 2 sql statements in MS access aand they both work but in the ASAP page with the direct conn to the oracle source I get an error at the following line:
rs2.Open strSQL2, conn,1,2
	
Microsoft OLE DB Provider for Oracle error '80040e14'
ORA-00923: FROM keyword not found where expected

Can someone help me understand my problem?

Vpart = request.querystring("Part")
'Create the Connection Object
	connStr = "Provider=msdaora;Data Source=PDW2; User Id=SRVC; Password=sw81361"
	Set conn = server.createobject("ADODB.Connection")
	Conn.open connStr

' Define List Record Set	
	Set rs = Server.CreateObject("ADODB.Recordset")	

	strSQL = "SELECT WHISC.SOF_ITEMS.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION, WHISC.SOF_ITEMS.PRIME, WHISC.GSCB_OHB_ORG.ORG_ID, WHISC.GSCB_OHB_ORG.TOT_OHB, WHISC.GSCB_OHB_ORG.DWL_DATE"
    strSQL = strSQL & " FROM WHISC.SOF_ITEMS"
    strSQL = strSQL & "	INNER JOIN (WHISC.SOF_OHB_CSE_ORG INNER JOIN WHISC.GSCB_OHB_ORG ON WHISC.SOF_OHB_CSE_ORG.ITEM_NO"
    strSQL = strSQL & "	= WHISC.GSCB_OHB_ORG.ITEM_NO) ON WHISC.SOF_ITEMS.ITEM_NO = WHISC.SOF_OHB_CSE_ORG.ITEM_NO"
    strSQL = strSQL & "	GROUP BY WHISC.SOF_ITEMS.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION, WHISC.SOF_ITEMS.PRIME, WHISC.GSCB_OHB_ORG.ORG_ID, WHISC.GSCB_OHB_ORG.TOT_OHB, WHISC.GSCB_OHB_ORG.DWL_DATE"
    strSQL = strSQL & "	HAVING (((WHISC.SOF_ITEMS.ITEM_NO)='" & Vpart & "'))"
	rs.Open strSQL, conn,1,2
	
'Define List Record Set	
	Set rs2 = Server.CreateObject("ADODB.Recordset")	

	strSQL2 = "SELECT WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION AS Description, WHISC.SOF_ITEMS.PLI, Sum(WHISC.SOF_OHB_CSE_ORG.TOT_OHB) AS OHB QTY FROM WHISC.BCTBL INNER JOIN WHISC.SOF_ENTITY_PROFILE INNER JOIN WHISC.SOF_OHB_CSE_ORG)    INNER JOIN WHISC.SOF_ITEMS ON WHISC.SOF_OHB_CSE_ORG.ITEM_NO = WHISC.SOF_ITEMS.ITEM_NO) ON WHISC.SOF_ENTITY_PROFILE.ENTITY_NO = WHISC.SOF_OHB_CSE_ORG.SUBINV) ON WHISC.BCTBL.BC = WHISC.SOF_ENTITY_PROFILE.SRVC_DIST GROUP BY WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION, WHISC.SOF_ITEMS.PLI HAVING (((WHISC.SOF_OHB_CSE_ORG.ITEM_NO)='" & Vpart & "'))"
	rs2.Open strSQL2, conn,1,2

Open in new window

jrb1

It's your "AS OHB QTY". Is QTY a different field? If so, you need a comma after OHB. Or do you want "AS OHB_QTY"?

Mitch Swetsky

ASKER

The AS was just to create an alias.
The following doesn't work and gives the same error
Sum(WHISC.SOF_OHB_CSE_ORG.TOT_OHB) AS [OHB_QTY] FROM WHISC.BCTBL

Mitch Swetsky

ASKER

When I change the statement to

Sum(WHISC.SOF_OHB_CSE_ORG.TOT_OHB) AS OHB_QTY FROM

I get the following error:

Microsoft OLE DB Provider for Oracle error '80040e14'
ORA-00905: missing keyword
/datawarehouse/PartOHB.asp, line 25

Mitch Swetsky

ASKER

In MS Access the following works fine:

SELECT WHISC_SOF_OHB_CSE_ORG.ITEM_NO, WHISC_SOF_ITEMS.DESCRIPTION AS Description, WHISC_SOF_ITEMS.PLI, Sum(WHISC_SOF_OHB_CSE_ORG.TOT_OHB) AS [OHB QTY]
FROM WHISC_BCTBL INNER JOIN (WHISC_SOF_ENTITY_PROFILE INNER JOIN (WHISC_SOF_OHB_CSE_ORG INNER JOIN WHISC_SOF_ITEMS ON WHISC_SOF_OHB_CSE_ORG.ITEM_NO = WHISC_SOF_ITEMS.ITEM_NO) ON WHISC_SOF_ENTITY_PROFILE.ENTITY_NO = WHISC_SOF_OHB_CSE_ORG.SUBINV) ON WHISC_BCTBL.BC = WHISC_SOF_ENTITY_PROFILE.SRVC_DIST
GROUP BY WHISC_SOF_OHB_CSE_ORG.ITEM_NO, WHISC_SOF_ITEMS.DESCRIPTION, WHISC_SOF_ITEMS.PLI
HAVING (((WHISC_SOF_OHB_CSE_ORG.ITEM_NO)="013E35080"));

jrm213jrm213

Try not using "AS" and put the alias in quotes...

"SELECT WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION "Description", WHISC.SOF_ITEMS.PLI, Sum(WHISC.SOF_OHB_CSE_ORG.TOT_OHB) AS "OHB QTY" FROM WHISC.BCTBL INNER JOIN WHISC.SOF_ENTITY_PROFILE INNER JOIN WHISC.SOF_OHB_CSE_ORG)    INNER JOIN WHISC.SOF_ITEMS ON WHISC.SOF_OHB_CSE_ORG.ITEM_NO = WHISC.SOF_ITEMS.ITEM_NO) ON WHISC.SOF_ENTITY_PROFILE.ENTITY_NO = WHISC.SOF_OHB_CSE_ORG.SUBINV) ON WHISC.BCTBL.BC = WHISC.SOF_ENTITY_PROFILE.SRVC_DIST GROUP BY WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION, WHISC.SOF_ITEMS.PLI HAVING (((WHISC.SOF_OHB_CSE_ORG.ITEM_NO)='" & Vpart & "'))"

Open in new window

jrm213jrm213

"SELECT WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION "Description", WHISC.SOF_ITEMS.PLI, Sum(WHISC.SOF_OHB_CSE_ORG.TOT_OHB) "OHB QTY" FROM WHISC.BCTBL INNER JOIN WHISC.SOF_ENTITY_PROFILE INNER JOIN WHISC.SOF_OHB_CSE_ORG)    INNER JOIN WHISC.SOF_ITEMS ON WHISC.SOF_OHB_CSE_ORG.ITEM_NO = WHISC.SOF_ITEMS.ITEM_NO) ON WHISC.SOF_ENTITY_PROFILE.ENTITY_NO = WHISC.SOF_OHB_CSE_ORG.SUBINV) ON WHISC.BCTBL.BC = WHISC.SOF_ENTITY_PROFILE.SRVC_DIST GROUP BY WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION, WHISC.SOF_ITEMS.PLI HAVING (((WHISC.SOF_OHB_CSE_ORG.ITEM_NO)='" & Vpart & "'))"

Open in new window

sorry missed an as

jrm213jrm213

man i am off today, and you also would need to escape or double up the quotes around the aliases so the string works.

Mitch Swetsky

ASKER

I tried double quotes and no As
Now I get

error '80040e14'
ORA-00905: missing keyword

strSQL2 = "SELECT WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION ""Description"", WHISC.SOF_ITEMS.PLI, Sum(WHISC.SOF_OHB_CSE_ORG.TOT_OHB) ""OHB QTY"" FROM WHISC.BCTBL INNER JOIN WHISC.SOF_ENTITY_PROFILE INNER JOIN WHISC.SOF_OHB_CSE_ORG)    INNER JOIN WHISC.SOF_ITEMS ON WHISC.SOF_OHB_CSE_ORG.ITEM_NO = WHISC.SOF_ITEMS.ITEM_NO) ON WHISC.SOF_ENTITY_PROFILE.ENTITY_NO = WHISC.SOF_OHB_CSE_ORG.SUBINV) ON WHISC.BCTBL.BC = WHISC.SOF_ENTITY_PROFILE.SRVC_DIST GROUP BY WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION, WHISC.SOF_ITEMS.PLI HAVING (((WHISC.SOF_OHB_CSE_ORG.ITEM_NO)='" & Vpart & "'))"

Open in new window

jrm213jrm213

ok looking at the rest of the query and how it is laid out, I am not used to seeing joins done in the order that you have them....

you have a number of closing parenthesis with no opening one in your joins and

I usually see them specifically noting the "on" directly after the join not after all the inner join statements.

strSQL2 = "SELECT WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION ""Description"", WHISC.SOF_ITEMS.PLI, Sum(WHISC.SOF_OHB_CSE_ORG.TOT_OHB) ""OHB QTY"" 
FROM WHISC.BCTBL INNER JOIN WHISC.SOF_ENTITY_PROFILE ON WHISC.BCTBL.BC = WHISC.SOF_ENTITY_PROFILE.SRVC_DIST 
INNER JOIN WHISC.SOF_OHB_CSE_ORG  ON WHISC.SOF_ENTITY_PROFILE.ENTITY_NO = WHISC.SOF_OHB_CSE_ORG.SUBINV
INNER JOIN WHISC.SOF_ITEMS ON WHISC.SOF_OHB_CSE_ORG.ITEM_NO = WHISC.SOF_ITEMS.ITEM_NO
GROUP BY WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION, WHISC.SOF_ITEMS.PLI HAVING (((WHISC.SOF_OHB_CSE_ORG.ITEM_NO)='" & Vpart & "'))"

Open in new window

Mitch Swetsky

ASKER

I really appreciate your input and patience. I know a smaller amout related to what I don't.
The solutions above including yours keep toggling between
ORA-00905: missing keyword
and
ORA-00923: FROM keyword not found where expected

I am getting ORA-00905: missing keyword with the code following:

strSQL2 = "SELECT WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION ""Description"", WHISC.SOF_ITEMS.PLI, Sum(WHISC.SOF_OHB_CSE_ORG.TOT_OHB) ""OHB QTY"" FROM WHISC.BCTBL INNER JOIN WHISC.SOF_ENTITY_PROFILE INNER JOIN WHISC.SOF_OHB_CSE_ORG)    INNER JOIN WHISC.SOF_ITEMS ON WHISC.SOF_OHB_CSE_ORG.ITEM_NO = WHISC.SOF_ITEMS.ITEM_NO) ON WHISC.SOF_ENTITY_PROFILE.ENTITY_NO = WHISC.SOF_OHB_CSE_ORG.SUBINV) ON WHISC.BCTBL.BC = WHISC.SOF_ENTITY_PROFILE.SRVC_DIST GROUP BY WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION, WHISC.SOF_ITEMS.PLI HAVING (((WHISC.SOF_OHB_CSE_ORG.ITEM_NO)='" & Vpart & "'))"

Open in new window

jrm213jrm213

can you write strSQL2 to the screen and paste what the output here?

Mitch Swetsky

ASKER

SELECT WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION AS Description, WHISC.SOF_ITEMS.PLI, Sum(WHISC.SOF_OHB_CSE_ORG.TOT_OHB) AS OHB_QTY FROM WHISC.BCTBL INNER JOIN WHISC.SOF_ENTITY_PROFILE INNER JOIN WHISC.SOF_OHB_CSE_ORG) INNER JOIN WHISC.SOF_ITEMS ON WHISC.SOF_OHB_CSE_ORG.ITEM_NO = WHISC.SOF_ITEMS.ITEM_NO) ON WHISC.SOF_ENTITY_PROFILE.ENTITY_NO = WHISC.SOF_OHB_CSE_ORG.SUBINV) ON WHISC.BCTBL.BC = WHISC.SOF_ENTITY_PROFILE.SRVC_DIST GROUP BY WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION, WHISC.SOF_ITEMS.PLI HAVING (((WHISC.SOF_OHB_CSE_ORG.ITEM_NO)='013E35080'))

jrm213jrm213

hi,

see that code is badly formatted. notice after your first inner join you have a ) after the table name, that is not right...

please use the code I posted in codeblock 37008379 and write that to screen and post it.

Mitch Swetsky

ASKER

SELECT WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION "Description", WHISC.SOF_ITEMS.PLI, Sum(WHISC.SOF_OHB_CSE_ORG.TOT_OHB) "OHB QTY" FROM WHISC.BCTBL INNER JOIN WHISC.SOF_ENTITY_PROFILE INNER JOIN WHISC.SOF_OHB_CSE_ORG) INNER JOIN WHISC.SOF_ITEMS ON WHISC.SOF_OHB_CSE_ORG.ITEM_NO = WHISC.SOF_ITEMS.ITEM_NO) ON WHISC.SOF_ENTITY_PROFILE.ENTITY_NO = WHISC.SOF_OHB_CSE_ORG.SUBINV) ON WHISC.BCTBL.BC = WHISC.SOF_ENTITY_PROFILE.SRVC_DIST GROUP BY WHISC.SOF_OHB_CSE_ORG.ITEM_NO, WHISC.SOF_ITEMS.DESCRIPTION, WHISC.SOF_ITEMS.PLI HAVING (((WHISC.SOF_OHB_CSE_ORG.ITEM_NO)='013E35080'))

Thanks again for your patience.

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mitch Swetsky

ASKER

That did it! Thank you all for your help.