Solved

Long query in VBscript issue

Posted on 2014-03-19
9
601 Views
Last Modified: 2014-03-25
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:

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

Open in new window


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

Open in new window

0
Comment
Question by:bmsande
  • 5
  • 2
9 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39940995
In your last WHERE clause there are two conditions in parenthesis. There is no OR/AND operator between them:
where ('PLANT (ROF)' IS NULL OR UPPER(a.ASSET_ID)= UPPER('PLANT (ROF)')) -- there is nothing here: insert OR/AND depending on your requirement
('DH027602-000-SOW-0000-008' IS NULL OR UPPER(d.CONTRACTOR_DOCUMENT_NUMBER)= UPPER('DH027602-000-SOW-0000-008')) 

Open in new window


BTW, if you copy your statement to a editor with a syntax highlighting (such as an open source editor called Notepad++), and select "SQL" as a language, you will see everything highlighted nicely. With Notepad++ you will also see the matching closing bracket:
Notepad   query
0
 

Author Comment

by:bmsande
ID: 39942269
I had my hopes up that the missing expression would fix it, wrong.... same error.
0
 

Assisted Solution

by:bmsande
bmsande earned 0 total points
ID: 39942437
found it.  lines 22 & 29 had single quotes.  I removed them and the query worked.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39943433
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_DOCUMENT_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-0000-008'
is *NOT* a problem.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:bmsande
ID: 39943462
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39943587
"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.
0
 

Author Comment

by:bmsande
ID: 39944149
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 ;)
0
 

Author Closing Comment

by:bmsande
ID: 39952718
My comment provided the final solution to my problem.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now