Making SQL code work via Excel

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

Answer 1

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.

Answer 2

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. 

Answer 3

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.