Need help in SQL

Databases
1

I have a query that has many joins and I am trying to select the description for an item based off of the substring result:

SELECT BP.[CardCode] AS ‘customer Code’, BP.[CardName] AS ‘customer Name’, BP.[Phone1] AS ‘Customer Phone’, Inv.[DocNum] AS ‘Invoice’, InvLin.[ItemCode] AS ‘Item No.’, InvLin.[Dscription] AS ‘Item Description’, InvLin.[Quantity] AS ‘Quanity Due’, InvLin.[whscode] as ‘Selling Warehouse’, InvLin.[U_WHSSale] as ‘Stocking Warehouse’ ,InvLin.[Price] AS ‘Price’, InvLin.[DocDate] AS ‘Date Shipped’, CM.[DocNum] AS ‘Credit Memo Number’ ,
CASE WHEN SUBSTRING(InvLin.ItemCode, 1, 2) = ‘CC’ THEN SUBSTRING(InvLin.ItemCode, 3, 6) ELSE InvLin.[ItemCode] END AS ‘New Item No.’
FROM [dbo].[INV1] InvLin Need
Inner Join [dbo].OINV Inv on InvLin.[docentry] = Inv.[docentry]
INNER JOIN [dbo].[OCRD] BP ON BP.[CardCode] = Inv.[CardCode]
LEFT OUTER JOIN [dbo].[ORIN] cm ON CM.[U_DocNumLnk] = convert(NVARChar,Inv.[DOCNum] )
INNER JOIN [dbo].[OITM] ITM ON ITM.[ItemCode] = InvLin.[ItemCode]
WHERE ITM.[ItmsGrpCod] In (101,102,103,104) AND CM.[DocEntry] IS NULL AND Inv.U_CoreStatus = ‘R’

Please help, I can’t not get it to work and give me the description for the new Item No. that I have result set for.

2

first of all, notbody can red queries that are written that way (including you)

here is your query, reformatted for humans –

SELECT BP.CardCode AS 'customer Code'
     , BP.CardName AS 'customer Name'
     , BP.Phone1 AS 'Customer Phone'
     , Inv.DocNum AS 'Invoice'
     , InvLin.ItemCode AS 'Item No.'
     , InvLin.Dscription AS 'Item Description'
     , InvLin.Quantity AS 'Quanity Due'
     , InvLin.whscode as 'Selling Warehouse'
     , InvLin.U_WHSSale as 'Stocking Warehouse' 
     , InvLin.Price AS 'Price'
     , InvLin.DocDate AS 'Date Shipped'
     , CM.DocNum AS 'Credit Memo Number' 
     , CASE WHEN SUBSTRING(InvLin.ItemCode,1,2) = 'CC' 
            THEN SUBSTRING(InvLin.ItemCode,3,6) 
            ELSE InvLin.ItemCode END AS 'New Item No.'
  FROM dbo.INV1 InvLin [COLOR="Red"]Need[/COLOR]
INNER 
  JOIN dbo.OINV Inv 
    ON Inv.docentry  = InvLin.docentry
INNER 
  JOIN dbo.OCRD BP 
    ON BP.CardCode = Inv.CardCode 
LEFT OUTER 
  JOIN dbo.ORIN cm 
    ON CM.U_DocNumLnk = convert(NVARChar,Inv.DOCNum) 
INNER 
  JOIN dbo.OITM ITM 
    ON ITM.ItemCode = InvLin.ItemCode
 WHERE ITM.ItmsGrpCod In (101,102,103,104) 
   AND CM.DocEntry IS NULL 
   AND Inv.U_CoreStatus = 'R'

you did not really explain what “can’t get it to work” means (wrong results? no results? error message? crashes the server?)

however, there is an obvious syntax error

:slight_smile:

3

:blush:

The issue is that the Item description that is being displayed in the report is that of the core item number. What I am doing is using the substring to check and see if the first 2 characters of the item number are ‘CC’ if so then i go out to the 3 character and get the original item number and call it New item number. I want to use the New item number to get the description out of the table and display that on the report.

I am new to sql and just can’t seem to figure out a solution.

4

out of what table?

5

The issue is that the Item description that is being displayed in the report is that of the core item number. What I am doing is using the substring to check and see if the first 2 characters of the item number are ‘CC’ if so then i go out to the 3 character and get the original item number and call it New item number. I want to use the New item number to get the description out of the INV1(InvLin) table and display that value on the report.

6

sorry for the delay, try this –

SELECT BP.CardCode AS 'customer Code'
     , BP.CardName AS 'customer Name'
     , BP.Phone1 AS 'Customer Phone'
     , Inv.DocNum AS 'Invoice'
     , InvLin.ItemCode AS 'Item No.'
     , InvLin.Dscription AS 'Item Description'
     , InvLin.Quantity AS 'Quanity Due'
     , InvLin.whscode as 'Selling Warehouse'
     , InvLin.U_WHSSale as 'Stocking Warehouse' 
     , InvLin.Price AS 'Price'
     , InvLin.DocDate AS 'Date Shipped'
     , CM.DocNum AS 'Credit Memo Number' 
     , CInvLin.ItemCode AS 'New Item No.'
  FROM dbo.OITM ITM 
[COLOR="Blue"]INNER 
  JOIN dbo.INV1 InvLin 
    ON InvLin.ItemCode = 
       CASE WHEN SUBSTRING(ITM.ItemCode,1,2) = 'CC' 
            THEN SUBSTRING(ITM.ItemCode,3,6) 
            ELSE ITM.ItemCode END[/COLOR] 
INNER 
  JOIN dbo.OINV Inv 
    ON Inv.docentry  = InvLin.docentry
INNER 
  JOIN dbo.OCRD BP 
    ON BP.CardCode = Inv.CardCode 
LEFT OUTER 
  JOIN dbo.ORIN cm 
    ON CM.U_DocNumLnk = convert(NVARChar,Inv.DOCNum) 
 WHERE ITM.ItmsGrpCod In (101,102,103,104) 
   AND CM.DocEntry IS NULL 
   AND Inv.U_CoreStatus = 'R'