Error when joining data using temp tables

[Former Member]

Hi all

I am making amendments to a query.

I need to add on some BP info, and as the query has all the different info in temp tables (info from Sales orders, Deliveries and Purchase Orders), i created a temp table for the customer info as well.

when I'm trying to run the query (in SQL2012), I get the following error:

Conversion failed when converting the nvarchar value 'Ship To' to data type int. Warning: Null value is eliminated by an aggregate or other SET operation.

When I take the shiptocode out,, then the error happens on the contact person.

I tried to convert the fields to int and even tried to text...same error.

any way of getting around it?

[former_member186712 · Accepted solution]

Hi Jerusha,

It will be easier if you post the query, so please post the query that you are running

Kind regards,

[Former Member · Accepted solution]

Hi Augusto

Here is the query:

I did not originally write the query and just added the Customer temp table and 2 extra fields to the orders temp table.

I have only tested adding the fields to two of the sections - i added comments in the query to show up to which point.

with

customer as (

SELECT T0.CardCode, T0.CardName 'Supplier/Customer', (T0.CreditLine - T0.Balance) as 'Credit Available', T0.CntctPrsn as 'Contact Person', T0.Phone1 as 'Tel', T0.Cellular as 'Cell'  from

OCRD T0),

deliver as (

SELECT        T1.[BaseEntry], T1.[BaseRef], T1.[ItemCode], T1.[Dscription], - sum(T1.OpenInvQty) AS 'Total Delivered', t1.uomcode, T1.BaseLine

FROM            ODLN T0 INNER JOIN

                         DLN1 T1 ON T0.DocEntry = T1.DocEntry

GROUP BY T1.[BaseRef], T1.[ItemCode], T1.[Dscription], T1.[BaseEntry], t1.uomcode, T1.BaseLine),

orders AS

    (SELECT        'SO' AS 'Tr', T1.ItemCode AS 'Product', T2.WhsCode AS 'Location', T0.[Project] AS 'Vessel', t4.prjname AS 'Vessel Name', T0.[DocNum] AS 'Contract',

                                T0.[CardName] AS 'Supplier/Customer', T1.[Price], t1.uomcode AS 'UoM', t1.numpermsr AS 'UoM Factor', t1.Quantity, - t1.OpenQty AS 'Open Qty',

                                - t1.OpenQty * t1.NumPerMsr AS 'Qty (MT)', T0.[DocCur] AS 'Currency', T1.Rate, CASE WHEN t0.DocCur <> 'USD' THEN ((t1.Price / t1.numpermsr) * t1.Rate)

                                ELSE t1.Price / t1.numpermsr END AS 'USD Price/MT', T1.LineNum, T0.CardCode, T0.ShipToCode as 'Deliver Address', T0.DocDueDate

      FROM            ordr T0 INNER JOIN

                                rdr1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN

                                OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN

                                OWHS T3 ON T3.WhsCode = T2.WhsCode LEFT JOIN

                                oprj t4 ON t4.prjcode = t0.project

      WHERE        T2.WhsCode = T1.WhsCode AND T0.Docstatus = 'O' AND T1.uomcode <> 'BAGS'/*and (-t1.OpenQty*t1.NumPerMsr) <> 0*/ ),

stock AS

    (SELECT        'STK' AS 'Tr', t1.ItemCode AS 'Product', '0' AS 'Contract', '0' AS 'Currency', t1.WhsCode AS 'Location', '0' AS 'Openqty', '0' AS 'Price', '0' AS 'Qty (MT)',

                                '0' AS 'Quantity', '0' AS 'Rate', '0' AS [Supplier/Customer], t1.AvgPrice AS [USD Price/MT], '0' AS 'UoM', '0' AS 'UOMFactor', '0' AS 'Vessel', '0' AS 'Vessel Name',

                                '0' AS 'BaseRef', '0' AS 'Total Delivered', '0' AS 'Qty2', CASE WHEN t2.opencreqty <> '0' THEN (t2.OpenCreQty * t2.NumPerMsr) ELSE t1.onhand END AS onhand,

                                t1.iscommited, t1.onorder, (t1.OnHand - t1.IsCommited + t1.OnOrder) AS 'Available'

      FROM            OITW t1 FULL OUTER JOIN

                                DLN1 t2 ON t2.ItemCode = t1.ItemCode AND t1.WhsCode = t2.WhsCode INNER JOIN

                                OITM t3 ON t3.ItemCode = t1.ItemCode

      WHERE        t3.ItmsGrpCod <> '101' AND t2.OpenQty > 0),

Stk2 AS

    (SELECT        CASE WHEN isnull(stock.Tr, 0) = '0' THEN 'STK' ELSE stock.Tr END AS 'Tr', stock. Product, stock.contract, stock.currency, stock.location, stock.openqty,

                                stock.price, stock.[Qty (MT)], stock.quantity, stock.rate, stock.[Supplier/Customer], oitw.AvgPrice AS [USD Price/MT], stock.UoM, stock.UOMFactor, stock.Vessel,

                                stock.[Vessel Name], stock.BaseRef, stock.[Total Delivered], stock.Qty2, '0' AS onhand, '0' AS 'commited', 0 AS 'available', OITW.ItemCode,

                                CASE WHEN isnull(SUM(stock.onhand), 0) = '0' THEN oitw.OnHand ELSE sum(stock.onhand) + oitw.OnHand END AS 'Tot', oitw.WhsCode, sum(stock.onhand)

                                + oitw.OnHand AS 'TotalOnHand'

      FROM            stock FULL OUTER JOIN

                                OITW ON oitw.ItemCode = stock. Product AND stock.Location = oitw.WhsCode FULL OUTER JOIN

                                OITM t3 ON t3.ItemCode = oitw.ItemCode AND oitw.OnHand > 0 AND t3.ItmsGrpCod <> '101'

      GROUP BY oitw.whscode, stock. Product, oitw.OnHand, stock.Tr, oitw.ItemCode, stock.contract, stock.currency, stock.location, stock.openqty, stock.price, stock.[Qty (MT)],

                                stock.quantity, stock.rate, stock.[Supplier/Customer], oitw.AvgPrice, stock.UoM, stock.UOMFactor, stock.Vessel, stock.[Vessel Name], stock.BaseRef,

                                stock.[Total Delivered], stock.Qty2)

-----------------------------------END of TEMP TABLES ---------------------------------------------------------------------

SELECT        tr, CASE WHEN isnull(Product, 0) = '0' THEN itemcode ELSE Product END AS 'Product', Contract, Currency, CASE WHEN ISNULL(LOCATION, 0)

                              = '0' THEN WhsCode ELSE Location END AS 'LOCATION', Openqty, Price, Tot AS 'Qty(MT)', Quantity, rate, [Supplier/Customer], [USD Price/MT], UOM, UOMFactor,

                              Vessel, [Vessel Name], BaseRef, [Total Delivered], Qty2, onhand, commited, '0' AS 'onorder', available,

   0 as 'Due Date', 0 as 'Credit Avail', 0 as 'Contact person', 0 as ' Telephone', 0 as 'Cell'

     FROM            Stk2

     WHERE        ItemCode NOT LIKE '%bag%' AND Tot > 0

UNION

SELECT        t9.Tr, t9. Product, t9.Contract, t9.Currency, t9.Location, t9.[Open Qty], t9.Price, CASE WHEN t8.[Total Delivered] > 0 THEN sum(- t8.[Total Delivered] * t9.[UoM Factor])

                         ELSE t9.[Qty (MT)] END AS [Qty (MT)], t9.Quantity, t9.Rate, t9.[Supplier/Customer], t9.[USD Price/MT], t9.UoM, t9.[UoM Factor], t9.Vessel, t9.[Vessel Name],

                         t8.BaseRef, t8.[Total Delivered], t8.[Total Delivered] + (t9.[Open Qty] * t9.[UoM Factor]) AS 'QTY2', '0' AS 'onhand', '0' AS 'iscommited', '0' AS 'onorder',

                         '0' AS 'Available', t9.DocDueDate, t10.[Credit Available], t10.[Contact Person], T10.Tel, T10.Cell

FROM            orders t9 LEFT JOIN

                         deliver t8 ON t8.BaseRef = t9.Contract AND t9.uom = t8.uomcode AND T9.LineNum = T8.BaseLine

  LEFT JOIN customer t10 on t10.CardCode = t9.CardCode

GROUP BY t9. Product, t9.Contract, t9.Currency, t9.Location, t9.[Open Qty], t9.Price, t9.[Qty (MT)], t9.Quantity, t9.Rate, t9.[Supplier/Customer], t9.[USD Price/MT], t9.UoM,                  

          t9.[UoM Factor], t9.Vessel, t9.[Vessel Name], t8.BaseRef, t9.Tr, t8.[Total Delivered], t9.[Deliver Address] , t9.DocDueDate, t10.[Credit Available],

  T10.Tel, T10.Cell

------only testing the above 2 sections

UNION

SELECT        'PO' AS 'Tr', t9.ItemCode, t8.DocNum, t9.Currency, t9.WhsCode, t9.OpenQty, t9.Price, t9.OpenQty * t9.NumPerMsr AS [Qty (MT)], t9.Quantity, t9.Rate, t8.CardName,

                         CASE WHEN t8.DocCur <> 'USD' THEN ((t9.Price / t9.numpermsr) * t9.Rate) ELSE t9.Price / t9.numpermsr END AS [USD Price/MT], t9.UomCode, t9.NumPerMsr,

                         t8.Project, t5.prjname, '0', '0', '0' AS 'QTY MT2', '0' AS 'onhand', '0' AS 'iscommited', '0' AS 'onorder', '0' AS 'Available'

FROM            opor T8 INNER JOIN

                         por1 T9 ON T9.DocEntry = T8.DocEntry INNER JOIN

                         OITW T2 ON T9.ItemCode = T2.ItemCode INNER JOIN

                         OWHS T3 ON T3.WhsCode = T2.WhsCode INNER JOIN

                         OITM t4 ON t4.ItemCode = t9.ItemCode LEFT JOIN

                         OPRJ t5 ON t5.prjcode = t8.project

WHERE        t4.ItmsGrpCod <> '101' AND (t9.OpenQty * t9.NumPerMsr) <> '0'

GROUP BY t9.ItemCode, t8.DocNum, t9.Currency, t9.WhsCode, t9.OpenQty, t9.Price, t9.Quantity, t9.Rate, t8.CardName, t9.UomCode, t9.NumPerMsr, t8.Project, t8.DocCur,

                         t5.prjname

UNION

SELECT        'SO CLOSE' AS 'Tr', t9.ItemCode, t8.DocNum, t9.Currency, t9.WhsCode, t4.[Total Delivered], t9.Price,

                         CASE WHEN t4.[total delivered] <= 0 THEN t4.[Total Delivered] ELSE t4.[Total Delivered] * t9.NumPerMsr END AS [Qty (MT)], t9.Quantity, t9.Rate, t8.cardname,

                         CASE WHEN t8.DocCur <> 'USD' THEN ((t9.Price / t9.numpermsr) * t9.Rate) ELSE t9.Price / t9.numpermsr END AS [USD Price/MT], t9.UomCode, t9.NumPerMsr,

                         t8.Project, t5.prjname, '0', '0', '0' AS 'QTY MT2', '0' AS 'onhand', '0' AS 'iscommited', '0' AS 'onorder', '0' AS 'Available'

FROM            ordr T8 INNER JOIN

                         rdr1 T9 ON T9.DocEntry = T8.DocEntry INNER JOIN

                         OITW T2 ON T9.ItemCode = T2.ItemCode INNER JOIN

                         OWHS T3 ON T3.WhsCode = T2.WhsCode LEFT JOIN

                         deliver T4 ON T4.BaseRef = T8.DocNum AND T4.ItemCode = T9.Itemcode AND T4.BaseLine = T9.LineNum LEFT JOIN

                         oprj t5 ON t8.project = t5.prjcode

WHERE        (t8.DocStatus = 'C') AND T2.WhsCode = T9.WhsCode AND T9.uomcode <> 'BAGS' AND (t4.[Total Delivered] * t9.NumPerMsr) <> 0

GROUP BY t9.ItemCode, t8.DocNum, t9.Currency, t9.WhsCode, t4.[Total Delivered], t9.Price, t9.Quantity, t9.Rate, t8.CardName, t9.UomCode, t9.NumPerMsr, t8.Project,

                         t5.prjname, t8.DocCur

[former_member186712 · Accepted solution]

One error is that the  t10.[Contact Person] is missing in the group by, after the select from the Orders 

[Former Member · Accepted solution]

Hi

Thanks for that...missed that

I added that...but now get this error:

Conversion failed when converting the nvarchar value 'persons name' to data type int.

Warning: Null value is eliminated by an aggregate or other SET operation.

[former_member186712 · Accepted solution]

The query has more errors.

I also changed this  

, '0' as 'Contact person', '0' as ' Telephone', '0' as 'Cell'

Because Contact person is a nvarchar

Change in your query

But the biggest problem is that you dont have the same columns is each query and qhen you use union you hall the queries must retrieve the same columns. If you Run the query in SQL you will see

Msg 205, Level 16, State 1, Line 3

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

If you run the query from your comment ------only testing the above 2 sections ,

to the top of the SQL Statment it works.

The problem is after your comment

I hope it helps.

[former_member186712 · Accepted solution]

The 3rd query has different columns than the 1st and 2nd.

You have to check

[Former Member · Accepted solution]

Hi Augusto.

I was testing one section at a time.I didnt put the new fields in the rest of the sections.

The 1st 2 sections are working after making the same changes... And i learnt something new today

put the 0 in  ' ' when its nvarchar...didnt realise that.

You're a star.

Thank you very much.

Regards,

Jerusha

[former_member186712 · Accepted solution]

Hi Jerusha,

Don't forget to mark the answers as a helpfull answer or as a correct answer.

Regards,