?
Solved

ASp page gets error while retrieving recordset from Oracle source

Posted on 2011-10-21
17
Medium Priority
?
370 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Mswetsky
16 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 37006257
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 37006367
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 37007395
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 1

Author Comment

by:Mswetsky
ID: 37007407
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
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 37007809
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
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 37007821
"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
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 37007835
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 37008323
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
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 37008379
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 37008709
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
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 37008943
can you write strSQL2 to the screen and paste what the output here?
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 37008956
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
 
LVL 17

Expert Comment

by:jrm213jrm213
ID: 37009004
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 37014036
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
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 37049848
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
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 37062542
That did it! Thank you all for your help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month15 days, 20 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question