CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2013
    Posts
    5

    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

  2. #2
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  3. #3
    Join Date
    Aug 2013
    Posts
    5

    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"

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  5. #5
    Join Date
    Aug 2013
    Posts
    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.

  6. #6
    Join Date
    Aug 2013
    Posts
    5

    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.

  7. #7
    Join Date
    Jul 2005
    Posts
    1,083

    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 ...

  8. #8
    Join Date
    Aug 2013
    Posts
    5

    Re: How to run a very long SQL statement

    Thanks jggtz

    I made it
    Code:
    strSQL = strSQL & "WHERE " & ODBC1_FIELD_NAME & " = ' "& strKeyValue & "'"

  9. #9
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    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.
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured