Query in Access vs VB 6 3

[JPimp]

I have this query, which works great when I run it in Access, then I move this over to my VB code, and it fails:

Dim cmdTabwareBase As New ADODB.Command
Dim rsTabwareBase As New ADODB.Recordset
Dim oracleTabwareBaseData As String


oracleTabwareBaseData = " SELECT [CMS_COMPLAINT].[COMPLAINT_ID], [CMS_COMPLAINT].[STATUS_CD], [CMS_COMPLAINT].[CREATE_DT], [CMS_COMPLAINT].[CLOSE_DT], [CMS_COMPLAINT].[SAMPLE_IN], [CMS_COMPLAINT].[SAMPLE_RECD_IN], [CMS_COMPLAINT].[SAMPLE_RECD_TEXT], [CMS_COMPLAINT].[SAMPLE_RECD_DT], [CMS_CONTACT].[FIRST_NAME], [CMS_CONTACT].[LAST_NAME], [CMS_CONTACT].[EMAIL_ADDRESS], [CMS_COMPLAINT_OWNER].[OWNER_NAME], [CMS_COMPLAINT_LINE_ITEM].[LOT_NO], [CMS_COMPLAINT_LINE_ITEM].[PRODUCT_ID], [CMS_COMPLAINT_LINE_ITEM].[LINE_ID]" & _



"FROM [CMS_COMPLAINT], [CMS_CONTACT], [CMS_COMPLAINT_OWNER], [CMS_COMPLAINT_LINE_ITEM]" & _


"WHERE [CMS_COMPLAINT].[COMPLAINT_ID] In (SELECT [COMPLAINT_ID] FROM [CMS_COMPLAINT_LINE_ITEM] As TmpID)

AND [CMS_COMPLAINT].[STATUS_CD]<>'Closed' AND 

[CMS_COMPLAINT].[CREATE_DT] <= SYSDATE-15 AND 

[CMS_COMPLAINT].[CLOSE_DT] IS NULL AND 

[CMS_COMPLAINT].[SAMPLE_IN]='N' AND 

[CMS_COMPLAINT].[SAMPLE_RECD_IN]='N' AND 

[CMS_COMPLAINT_OWNER].[OWNER_NAME]='Kennebunk Plant' 

GROUP BY [COMPLAINT_ID] HAVING Count(*)=1 "

The error is ORA-00936 missing expression, which could be almost anything...any ideas why VB does not like the format, what am I missing?

Thanks!!

Kai-What?

 

[BobRodes]

Ok, I think I'm getting the picture. You have an Access VBA application, and you're redoing it as a VB application with an Oracle back end. You're assuming that your SQL statements that you created in Access will be directly transferable to Oracle, and that isn't the case at all unless you stick to ANSI SQL. Which you haven't, because you probably created your queries using Access's QBE.

Error ORA-40096 is NOT a VB error. Check Err.Source and I suspect you'll find it's an Oracle error. (Now that I look at it, the fact that the error begins with "ORA" is a bit of a giveaway!) I'd also bet money that if you paste your code into Oracle's Query Analyzer (whatever they call it) it won't work there, and will give you the same error.

Assuming I've hit on the problem, you'll need to learn Oracle's particular flavor of SQL, and perhaps a good place to do that is in the Oracle forum as has been mentioned. I suspect that Oracle doesn't support the ability to create joins by putting a comma-separated list of tables in the FROM clause; that's pretty non-standard. ANSI standard SQL would be this:

"FROM [CMS_COMPLAINT] JOIN [CMS_CONTACT] ON [CMS_COMPLAINT].fk = [CMS_CONTACT].pk JOIN [CMS_COMPLAINT_OWNER] ON (and so on)

where fk is the foreign key name and pk is the primary key name.

HTH

Bob

 

[HarleyQuinn]

Bob - You're right ORA-40096 is an Oracle error (I should have noticed in the OP's first post )

Oracle will support joins in two ways. The way you posted using the named join operators (this is newer from memory), it will also support joins done in the WHERE clause e.g.

SELECT s.StoreName, Count(e.EmployeeID) FROM Store s, Employees e
WHERE s.StoreID = e.eStoreID
GROUP BY s.StoreID

Different types of joins can be implemented by adding (+) to either end of the join (depending on the type of join you are after).

Apart from the obvious error (and the complete lack of joins) I mentioned in my previous post it almost looks syntax-ically correct.

I'd say for the OP to paste the modified query into their query engine (SQL*Plus, TOAD, SQLDeveloper etc.) and see if it works in there, at the very least it'll give (occasionally) a more accurate idea of what's going wrong.

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[MakeItSo]

Eagly eye HarleyQuinn!
I didn't spot that incomplete SELECT in the WHERE clause.
Spot on!


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[HarleyQuinn]

Cheers MakeItSo

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[JPimp]

Thank you everyone, and sorry about the confusion there, I will make the proper changes and test in a Oracle query window and see if I can't make that work, thanks again!

Kai-What?

 

[Andrzejek]

You should also know that you have pretty nice tool in VB 6.0 to create and check SQLs in ORACLE, or Access, or any other data base - Data Environment.

You can find a little tutorial of how to set it up and use it at

http://www.geocities.com/vb_stuff/ADODB.html

Have fun.

---- Andy