-
August 2nd, 2013, 03:16 PM
#1
How to run a very long SQL statement
Hey I was wondering if someone here can help me. I have a very long sql statement but I'm running into errors when trying to run as part of my script. My normal sql statements are like
Code:
' Set SQL statement
' strSQL = "SELECT custno, Company, Contact, Address1, Address2, "
' strSQL = strSQL & "City, State , Zip, Country, Phone, Email, "
' strSQL = strSQL & "shipvia, ponum "
' strSQL = strSQL & "FROM " & TABLE_NAME
' strSQL = strSQL & " WHERE " & ODBC1_FIELD_NAME & " = '" & strKeyValue & "'"
The problem is that now I have a very large statement and I'm lost as how to get the syntax correct so it will run in VBA. It works as is in razorSQL. I can't get it to work in WinSql. My question is HOW and WHERE do I add the
strSQL = "strSQL = " so it works in VBA?
Code:
SELECT TOP 1
ShipHead.PackNum,
ShipHead.ShipViaCode,
ShipHead.NotifyEMail,
OrderRel.OrderNum,
CASE WHEN OrderRel.UseOTS = 0 THEN CustCnt.Name ELSE OrderRel.OTSContact END AS STContact,
CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Name ELSE OrderRel.OTSName END AS STName,
CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Address1 ELSE OrderRel.OTSAddress1 END AS STAdd1,
CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Address2 ELSE OrderRel.OTSAddress2 END AS STAdd2,
CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Address3 ELSE OrderRel.OTSAddress3 END AS STAdd3,
CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.City ELSE OrderRel.OTSCity END AS STCity,
CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.State ELSE OrderRel.OTSState END AS STState,
CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.ZIP ELSE OrderRel.OTSZIP END AS STZIP,
CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.PhoneNum ELSE OrderRel.OTSPhoneNum END AS STPhoneNum,
CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.CountryNum ELSE OrderRel.OTSCountryNum END AS STCountryNum
FROM
MFGSYS.PUB.ShipHead ShipHead
JOIN MFGSYS.PUB.ShipDtl ShipDtl ON ShipHead.Company = ShipDtl.Company AND ShipHead.PackNum = ShipDtl.PackNum
JOIN MFGSYS.PUB.OrderRel OrderRel ON ShipDtl.Company = OrderRel.Company AND ShipDtl.OrderNum = OrderRel.OrderNum
JOIN MFGSYS.PUB.ShipTo ShipTo ON OrderRel.ShipToNum = ShipTo.ShipToNum AND OrderRel.ShipToCustNum = ShipTo.CustNum
LEFT JOIN MFGSYS.PUB.CustCnt CustCnt ON OrderRel.ShipToCustNum = CustCnt.CustNum AND OrderRel.ShpConNum = CustCnt.ConNum
WHERE
ShipHead.Company = '68138' AND
ShipHead.PackNum = strval
Thanks Bryan
-
August 2nd, 2013, 11:36 PM
#2
Re: How to run a very long SQL statement
Would be better to create a stored procedure and execute it from VB
To answer your question you would have to build it like you do the one in the top example
Code:
strSQL="SELECT TOP 1 "
strSQL=strSQL & "ShipHead.PackNum,"
' and so on
Always use [code][/code] tags when posting code.
-
August 5th, 2013, 11:29 AM
#3
Re: How to run a very long SQL statement
Thanks DataMiser
So would you think this should work?
I'm concerned about the FROM and WHERE.
Code:
strSQL = "SELECT TOP 1"
strSQL = strSQL & "ShipHead.PackNum,"
strSQL = strSQL & "ShipHead.ShipViaCode,"
strSQL = strSQL & "ShipHead.NotifyEMail,"
strSQL = strSQL & "OrderRel.OrderNum,"
strSQL = strSQL & "CASE WHEN OrderRel.UseOTS = 0 THEN CustCnt.Name ELSE OrderRel.OTSContact END AS STContact,"
strSQL = strSQL & "CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Name ELSE OrderRel.OTSName END AS STName,"
strSQL = strSQL & "CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Address1 ELSE OrderRel.OTSAddress1 END AS STAdd1,"
strSQL = strSQL & "CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Address2 ELSE OrderRel.OTSAddress2 END AS STAdd2,"
strSQL = strSQL & "CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.Address3 ELSE OrderRel.OTSAddress3 END AS STAdd3,"
strSQL = strSQL & "CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.City ELSE OrderRel.OTSCity END AS STCity,"
strSQL = strSQL & "CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.State ELSE OrderRel.OTSState END AS STState,"
strSQL = strSQL & "CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.ZIP ELSE OrderRel.OTSZIP END AS STZIP,"
strSQL = strSQL & "CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.PhoneNum ELSE OrderRel.OTSPhoneNum END AS STPhoneNum,"
strSQL = strSQL & "CASE WHEN OrderRel.UseOTS = 0 THEN ShipTo.CountryNum ELSE OrderRel.OTSCountryNum END AS STCountryNum"
strSQL = strSQL & "FROM" & "MFGSYS.PUB.ShipHead ShipHead"
strSQL = strSQL & "JOIN MFGSYS.PUB.ShipDtl ShipDtl ON ShipHead.Company = ShipDtl.Company AND ShipHead.PackNum = ShipDtl.PackNum"
strSQL = strSQL & "JOIN MFGSYS.PUB.OrderRel OrderRel ON ShipDtl.Company = OrderRel.Company AND ShipDtl.OrderNum = OrderRel.OrderNum"
strSQL = strSQL & "JOIN MFGSYS.PUB.ShipTo ShipTo ON OrderRel.ShipToNum = ShipTo.ShipToNum AND OrderRel.ShipToCustNum = ShipTo.CustNum"
strSQL = strSQL & "LEFT JOIN MFGSYS.PUB.CustCnt CustCnt ON OrderRel.ShipToCustNum = CustCnt.CustNum AND OrderRel.ShpConNum = CustCnt.ConNum"
strSQL = strSQL & "WHERE" & "ShipHead.Company = '68138' & ShipHead.PackNum = strval"
-
August 5th, 2013, 01:19 PM
#4
Re: How to run a very long SQL statement
Try it and see
To much there to read and analyze.
I still think you would be better calling a stored procedure rather than trying to do all that in one string.
Edit: one thing I do notice at a glance is that you are missing some spaces and that will be an issue
Here for example
Code:
strSQL = strSQL & "FROM" & "MFGSYS.PUB.ShipHead ShipHead"
strSQL = strSQL & "JOIN MFGSYS.PUB.ShipDtl ShipDtl ON ShipHead.Company = ShipDtl.Company AND ShipHead.PackNum = ShipDtl.PackNum"
You need a space between ShipHead and JOIN
I see several places where this is an issue
Last edited by DataMiser; August 5th, 2013 at 01:22 PM.
Always use [code][/code] tags when posting code.
-
August 5th, 2013, 01:34 PM
#5
Re: How to run a very long SQL statement
I wish I could try it now, but the db is at another customers location and while there I don't have accses to anything other than WinSQL to trouble shoot.
Thanks for the heads up on the spaces. I'll search for more.
-
August 5th, 2013, 01:35 PM
#6
Re: How to run a very long SQL statement
Also I'm going to look up creating a stored procedure and see if that looks better.
-
August 5th, 2013, 02:03 PM
#7
Re: How to run a very long SQL statement
Also...
If strval is a variable form your app, then you should change the WHERE part to :
Code:
strSQL = strSQL & "WHERE ShipHead.Company = '68138' AND ShipHead.PackNum = " & strval
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
August 5th, 2013, 02:16 PM
#8
Re: How to run a very long SQL statement
Thanks jggtz
I made it
Code:
strSQL = strSQL & "WHERE " & ODBC1_FIELD_NAME & " = ' "& strKeyValue & "'"
-
August 6th, 2013, 01:59 AM
#9
Re: How to run a very long SQL statement
Use a STORED PROCEDURE that allows ONLY VALID DATA. If strKeyValue happens to be ''Drop Table *'' or something like that, you could be in trouble.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|