Query question 1

[nicks60610]

I don't know if this is a VB question or Oracle, but the error I get is an ORA-00936 error, missing expression. This is the query I have:

oracleTabwareBaseData = " SELECT DISTINCT WO_WORK_ORDER.WO_BASE,
WO_WORK_ORDER.DESCRIPTION,
WO_WORK_ORDER.PLANNER,
WO_WORK_ORDER.REQUESTED_COMPLETION, WO_WORK_ORDER.ENTERED_DATE,
WO_WORK_ORDER.COMPLETED_DATE,
EM_EMPLOYEE.EMPLOYEE_NAME,
EM_EMPLOYEE.EMAIL_ADDRESS,
EM_EMPLOYEE.PLANNER_CODE " & _

"FROM WO_WORK_ORDER, SY_WO_STATUS , EM_EMPLOYEE " & _

"WHERE WO_WORK_ORDER.WO_BASE NOT LIKE 'MP*' AND

WO_WORK_ORDER.PLANT = SY_WO_STATUS.PLANT AND

WO_WORK_ORDER.STATUS = SY_WO_STATUS.CODE AND

WO_WORK_ORDER.PLANNER = EM_EMPLOYEE.PLANNER_CODE AND

SY_WO_STATUS.PLANT = EM_EMPLOYEE.PLANT AND

WO_WORK_ORDER.ENTERED_DATE < Date()-14 AND

WO_WORK_ORDER.COMPLETED_DATE Is Null AND

WO_WORK_ORDER.STATUS <>'80' And WO_WORK_ORDER.STATUS <> '79' " & _


"ORDER BY WO_WORK_ORDER.WO_BASE

 

[SantaMufasa]

Nick,

Your "Date()-14" reference is one that jumps off the page as invalid. Although there may be other issues that I cannot see at the moment, that one is certainly a non-compliant item as far as Oracle is concerned. It generates the same error that you received:

select Date()-14 from dual
       *
ERROR at line 1:
ORA-00936: missing expression

Let us know if adjusting the code to read "SYSDATE-14" works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[nicks60610]

Aha, that did work, hehehe, thanks!!

 

[HarleyQuinn]

Hi,

The first few things I would suggest (from both a VB and Oracle perspective) would be to change the wilcard character from a * to %

Next thing would be to replace the VB function Date() with SYSDATE (which is pretty much the oracle equivilant, or use TRUNC(SYSDATE) to take the time portion of SYSDATE out). Another option if you prefer the Date() function is to sub in the variable e.g.

"WO_WORK_ORDER.ENTERED_DATE < '" & Date()-14 & "' AND"

You can see what the query you are trying to pass to Oracle by typing

Debug.Print oracleTabwareBaseData

If you type this after you set the oracleTabwareBaseData variable then you will see the real string (with any variable substitutions in) in your immediate window e.g.

dim myvariable as string
dim strSQL as string
myvariable = "A_VALUE"
strSQL = "SELECT * FROM MYTABLE WHERE COL = '" & myvariable & "'"
debug.print strSQL

Would return

SELECT * FROM MYTABLE WHERE COL = 'A_VALUE'

In the immediate window.

Sorry it's a b it of a long post but I hope it helps



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

 

[HarleyQuinn]

Beat me too the punch there Santa, damn my lack of consice posting

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

 

[karluk]

In the line

WO_WORK_ORDER.ENTERED_DATE < Date()-14 AND

"Date()" is the source of your problem. It doesn't mean anything in Oracle syntax. If you want 14 days before today, your query should be something like

WO_WORK_ORDER.ENTERED_DATE < sysdate-14 AND

Or maybe, if you want 14 days before midnight this morning

WO_WORK_ORDER.ENTERED_DATE < trunc(sysdate)-14 AND

 

[nicks60610]

Thanks to all of you guys, I really appreciate all the answers!