bmsande
asked on
Long query in VBscript issue
I've hit a road block with my vb script in regards to a long sql query. When i execute my script, I'm receiving an error message from oracle, complaining about the syntax of the sql statement. So, my guess is that the problem lies within the formatting of the query string in the script, but I can't find where. Hopefully someone else can spot it.
Script:
Output: (sorry for the word wrap from cmd prompt)
Script:
HOST = "dbhost"
PORT = "1522"
SID = "TEST"
USER = "TESTUSER"
PASSWORD = "PASSWORD"
Srvname="Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST="& HOST &")(PORT="& PORT &"))" & _
"(CONNECT_DATA=(SID="& SID &"))); uid="& USER &";pwd="& PASSWORD &";"
Set Rec = CreateObject("ADODB.Recordset")
Set conn=Createobject("ADODB.Connection")
conn.open Srvname
Set objFSO = CreateObject("Scripting.FileSystemObject")
strSQL = "select a.DataID from" &_
" (select LLAttrData.ID as DataID, DTree.ParentID, DTree.SubType, DTree.Name," &_
" LLAttrData.DefID, LLAttrData.ValStr as 'ASSET_ID'" &_
" from LLAttrData, DTree" &_
" where LLAttrData.DefID = 9477354" &_
" and LLAttrData.AttrID = 2" &_
" and LLAttrData.ID = DTree.DataID and LLAttrData.VerNum = DTree.VersionNum) a" &_
" inner join" &_
" (select LLAttrData.ID as DataID, DTree.ParentID, DTree.SubType, DTree.Name," &_
" LLAttrData.DefID, LLAttrData.ValStr as 'CONTRACTOR_DOCUMENT_NUMBER'" &_
" from LLAttrData, DTree" &_
" where LLAttrData.DefID = 9477354" &_
" and LLAttrData.AttrID = 6" &_
" and LLAttrData.ID = DTree.DataID and LLAttrData.VerNum = DTree.VersionNum) d" &_
" on a.DataID = d.DataID" &_
" where ('PLANT (ROF)' IS NULL OR UPPER(a.ASSET_ID)= UPPER('PLANT (ROF)'))" &_
" ('DH027602-000-SOW-0000-008' IS NULL OR UPPER(d.CONTRACTOR_DOCUMENT_NUMBER)= UPPER('DH027602-000-SOW-0000-008'))" &_
" order by a.DataID"
WScript.Echo(strSQL)
Rec.Open strSQL, conn
dataid=Rec.fields("DataID")
WScript.Echo(dataid)
Rec.Close
conn.Close
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("D:\opt\Scripts\sourcefile.txt", 1)
do while not (objTextFile.AtEndOfStream)
arrStr = Split(objTextFile.ReadLine, ",")
WScript.Echo(arrStr(3))
Loop
objTextFile.close
Output: (sorry for the word wrap from cmd prompt)
C:\Documents and Settings\svc_dev>cscript "D:\opt\Scripts\getDataIDs.vbs"
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.
select a.DataID from (select LLAttrData.ID as DataID, DTree.ParentID, DTree.SubType, DTree.Name, LLAttrData.DefID, LLAttrData.Va
lStr as 'ASSET_ID' from LLAttrData, DTree where LLAttrData.DefID = 9477354 and LLAttrData.AttrID = 2 and LLAttrData.ID = DTree.D
ataID and LLAttrData.VerNum = DTree.VersionNum) a inner join (select LLAttrData.ID as DataID, DTree.ParentID, DTree.SubType, DTr
ee.Name, LLAttrData.DefID, LLAttrData.ValStr as 'CONTRACTOR_DOCUMENT_NUMBER' from LLAttrData, DTree where LLAttrData.DefID = 947
7354 and LLAttrData.AttrID = 6 and LLAttrData.ID = DTree.DataID and LLAttrData.VerNum = DTree.VersionNum) d on a.DataID = d.Data
ID where ('PLANT (ROF)' IS NULL OR UPPER(a.ASSET_ID)= UPPER('PLANT (ROF)')) ('DH027602-000-SOW-0000-008' IS NULL OR UPPER(d.CONTRACTOR_DOCUMENT_NUMBER)= UPPER('DH027602-000-SOW-0000-00
8')) order by a.DataID
D:\opt\Scripts\getDataIDs.vbs(41, 1) Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC driver for Oracle][Oracle]ORA-
00923: FROM keyword not found where expected
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm glad that you found the error. I'm concerned by a few other things I see in your query though:
1. where [some thing] is null
2. and UPPER(a.ASSET_ID) = [a value]
3. UPPER(d.CONTRACTOR_DOCUMEN T_NUMBER) = [a value]
All of those conditions can impose huge performance penalties on Oracle queries! That is especially true if the "is null" condition applies to a database column or if either the ASSET_ID or the CONTRACTOR_DOCUMENT_NUMBER columns are indexed.
Why? Applying any SQL operators (like: nvl, upper, lower, substr, to_char, to_date, to_number, etc.) to any column value prevents Oracle from using an index on that column (unless your database is set up to support "function-based" indexes *AND* you have index(es) that exactly match this query syntax). Depending on the numbers of records in the table, and on the speed and capacity of your server and storage system, the performance impact of queries like this can be significant.
Using these operators on your query bind variables, like this:
UPPER('DH027602-000-SOW-00 00-008'
is *NOT* a problem.
1. where [some thing] is null
2. and UPPER(a.ASSET_ID) = [a value]
3. UPPER(d.CONTRACTOR_DOCUMEN
All of those conditions can impose huge performance penalties on Oracle queries! That is especially true if the "is null" condition applies to a database column or if either the ASSET_ID or the CONTRACTOR_DOCUMENT_NUMBER
Why? Applying any SQL operators (like: nvl, upper, lower, substr, to_char, to_date, to_number, etc.) to any column value prevents Oracle from using an index on that column (unless your database is set up to support "function-based" indexes *AND* you have index(es) that exactly match this query syntax). Depending on the numbers of records in the table, and on the speed and capacity of your server and storage system, the performance impact of queries like this can be significant.
Using these operators on your query bind variables, like this:
UPPER('DH027602-000-SOW-00
is *NOT* a problem.
ASKER
this query is used on demand to fetch a unique ID based on a USER PROVIDED list of attributes. So, in some cases a field is left blank, and CASE does not match up. The query is meant to be flexible in these cases. I'm sure performance could be improved, but i'm not an oracle guy, and this works for my need.
"The query is meant to be flexible...". Yes, I understand that. Unfortunately, in SQL queries (and in other things too at times) "flexible" and "fast" do not go together. Usually in SQL, the most-flexible queries perform the slowest. So, if the users or application demand flexibility, SQL statements can be written to be flexible. The users should then not expect good performance.
If good performance is an issue, often some flexibility needs to be sacrificed.
If good performance is an issue, often some flexibility needs to be sacrificed.
ASKER
Users won't be interacting with this query at all. This is strictly used for bulk attribute updates, performed by me, on demand--maybe once a quarter. I appreciate your concern ;)
ASKER
My comment provided the final solution to my problem.
ASKER