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

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

---

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"

---

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

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.

Also I'm going to look up creating a stored procedure and see if that looks better.

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

---

Thanks jggtz

I made it

Code:

---

strSQL = strSQL & "WHERE " & ODBC1_FIELD_NAME & " = ' "& strKeyValue & "'"

---

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.