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

LVL 1
Mitch SwetskyBusiness AnalystAsked:
Who is Participating?
 
johnsoneSenior Oracle DBACommented:
I found a few extra )s in your latest query.  Try this:

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'
0
 
jrb1Commented:
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"?
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Mitch SwetskyBusiness AnalystAuthor Commented:
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
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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"));
0
 
jrm213jrm213Commented:
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

0
 
jrm213jrm213Commented:
"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
0
 
jrm213jrm213Commented:
man i am off today, and you also would need to escape or double up  the quotes around the aliases so the string works.
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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

0
 
jrm213jrm213Commented:
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

0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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

0
 
jrm213jrm213Commented:
can you write strSQL2 to the screen and paste what the output here?
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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'))
0
 
jrm213jrm213Commented:
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.

0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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.
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
That did it! Thank you all for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.