locked
Making SQL code work via Excel RRS feed

  • Question

  • Hi All

    I've got the below code which I am trying to get to work via excel. Does anyone know how to translate it?

    Its a program that requires a warehouse job number to be entered which has been set to gp.PickingAssignmentUserGroupId = 10964 Note: (10964 is the warehouse system job number).

    It is supposed to create an SQ import from a VMS Group Reference which moves component lines from hyphen kits and takes the hyphen off too and may link all SQs and Gds linked to a single VMS warehouse group.

    */

    Select DISTINCT '' AS "Blank",
      CASE               /* Get rid of hyphen kit */
        WHEN SUBSTRING(od.STCODE,LEN(od.STCODE),1) = '-' THEN SUBSTRING(od.STCODE,1,LEN(od.STCODE) -1)
        WHEN SUBSTRING(od.STCODE,LEN(od.STCODE),1) <> '-' THEN od.STCODE
      END
      AS "Stcode",
      (od.DELQTY*-1) AS "Del Qty", od.CLCODE,
      gp.PickingAssignmentUserGroupId AS "Warehouse Group",
      CONVERT(NVARCHAR, GETDATE(), 103) AS "Date",
      od.LOCCODE ,
      CONCAT(od.DOCUMENTNO,
      ' ',
      oh.EXORNO)  AS "Stdesc"

     from [RenovotecVMS].[dbo].PickingAssignment_PickingAssignmentUserGroup  gp WITH (NOLOCK)

     INNER JOIN [RenovotecVMS].[dbo].PickingAssignment pa WITH (NOLOCK) ON
      pa.id = gp.PickingAssignmentId
     
     INNER JOIN [RenovotecVMS].[dbo].PickingAssignmentItem i ON
      i.PickingAssignmentId = pa.Id AND
      i.[PickingAssignmentItemTypeId] <> 30           -- Take out componenet items
     
     -- VMS Product table
     JOIN [RenovotecVMS].[dbo].Product pr ON
      pr.Id = i.ProductId
     
     -- Kypera Order detail
     INNER JOIN [KyperaLive].[dbo].[orderdog] od 

     WITH (NOLOCK) ON
      od.docketno = pa.[HostId] AND
      od.STCODE = pr.ProductCode AND
      od.TCODE = 'SD' AND
      od.DOCUMENTNO = pa.MiscAlpha1 AND
      od.POSTNOM = 1 AND od.DELIVERED = 1 AND
      od.CANCELLED = 0
     
     -- Kypera Order Header
     JOIN [KyperaLive].[dbo].[orderdoc] oh
     WITH (NOLOCK) ON
      oh.documentno = od.documentno AND
      oh.DOCKETNO =  pa.[HostId] AND
      oh.bno = od.bno

     /* ENTER VMS WAREHOUSE GROUP NUMBER HERE TO SELECT APPROPRIATE GROUP      */

    WHERE gp.PickingAssignmentUserGroupId = 10964

      AND gp.PickingAssignmentId = i.PickingAssignmentId

    GROUP BY i.[PickingAssignmentItemTypeId], gp.PickingAssignmentUserGroupId , od.[STCODE],  i.[PickingAssignmentItemTypeId], od.delqty, od.clcode, od.loccode, od.DOCUMENTNO, oh.exorno

    ORDER BY  "stcode"

    Any help would be great as I'm not great with code but my colleague wants this doing and thinks that my basic understand of VBA code somehow stretches to this. (It doesn't).

    Thanks in Advance

    P.S Not entirely sure I've posted this in the correct place.


    Red Hood - Y.O.L.T

    Monday, April 20, 2015 3:59 PM

Answers

  • So what exactly is not working?  The only issues I see are

    • the string literal delimiter in tsql is the single quote, not double quote. 
    • the order by clause does not need to delimit the column alias of stcode. 
    • I don't know what (od.DELQTY*-1) is supposed to do - that isn't proper tsql.

    And I suggest that you don't use the nolock hint without a GOOD reason.  It is frequently misused and rarely understood. And a last comment.  The use of DISTINCT and GROUP by (especially without any aggregate) is a big red flag that the query might be logically incorrect - these are often bandaids used to cover up the resulting duplicate rows. 

    Monday, April 20, 2015 4:16 PM
    Answerer
  • And another thing.  This:

    CASE               /* Get rid of hyphen kit */
         WHEN SUBSTRING(od.STCODE,LEN(od.STCODE),1) = '-' THEN SUBSTRING(od.STCODE,1,LEN(od.STCODE) -1)
         WHEN SUBSTRING(od.STCODE,LEN(od.STCODE),1) <> '-' THEN od.STCODE
       END

    Is far more complex than it needs to be.  I won't quibble with the first case branch, but there is no need for the second.  Either the last character is a hyphen or it isn't.  So it can be simplified to:

    case when ... else od.STCODE end as Stcode

    And notice how you are changing case on the column alias.  Is that intentional? You are not consistent (order by stcode is an example) and that type of thing can cause problems later on.

    Monday, April 20, 2015 4:20 PM
    Answerer

All replies

  • Hi,

    If you are looking to translate this code to query between excel sheets then I don't think I can help that much. 

    But if you want to run your query in excel then you can follow below tutorial. 

    http://www.wikihow.com/Embed-a-SQL-Query-in-Microsoft-Excel

    to run that you must have proper connection string to SQL Server and proper authentication and authorization in SQL Server.

    Hope this will help.

    Monday, April 20, 2015 4:10 PM
  • So what exactly is not working?  The only issues I see are

    • the string literal delimiter in tsql is the single quote, not double quote. 
    • the order by clause does not need to delimit the column alias of stcode. 
    • I don't know what (od.DELQTY*-1) is supposed to do - that isn't proper tsql.

    And I suggest that you don't use the nolock hint without a GOOD reason.  It is frequently misused and rarely understood. And a last comment.  The use of DISTINCT and GROUP by (especially without any aggregate) is a big red flag that the query might be logically incorrect - these are often bandaids used to cover up the resulting duplicate rows. 

    Monday, April 20, 2015 4:16 PM
    Answerer
  • And another thing.  This:

    CASE               /* Get rid of hyphen kit */
         WHEN SUBSTRING(od.STCODE,LEN(od.STCODE),1) = '-' THEN SUBSTRING(od.STCODE,1,LEN(od.STCODE) -1)
         WHEN SUBSTRING(od.STCODE,LEN(od.STCODE),1) <> '-' THEN od.STCODE
       END

    Is far more complex than it needs to be.  I won't quibble with the first case branch, but there is no need for the second.  Either the last character is a hyphen or it isn't.  So it can be simplified to:

    case when ... else od.STCODE end as Stcode

    And notice how you are changing case on the column alias.  Is that intentional? You are not consistent (order by stcode is an example) and that type of thing can cause problems later on.

    Monday, April 20, 2015 4:20 PM
    Answerer