Conversion failed when converting varchar to int

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conversion failed when converting varchar to int

Author

Topic

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-26 : 22:23:07

Hi guys,

My query was working fine until I added the red writing below.

Basically what I'm doing with this is getting the lowest open purchase order number (docnum) and its matching docduedate.

Now when I run it I get

Conversion failed when converting the varchar value 'po_num' to data type int.

Any ideas? Thanks!

SELECT t3.product ,

t7.itemname ,

t2.u_vlgx_plc,

t3.shorted ,

t4.onhand ,

MIN(

CASE

WHEN t8.linestatus = 'O'

THEN t9.docnum

ELSE NULL

END) po_num,

t10.docduedate

FROM

(SELECT t0.product product ,

SUM(

CASE

WHEN t0.qty_topick <> t0.qty_picked

THEN t0.qty_topick - t0.qty_picked

ELSE 0

END) shorted

FROM rbeacon.dbo.shipline2 t0

INNER JOIN rbeacon.dbo.shiphist t1

ON t0.packslip = t1.packslip

WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-1, 101)

GROUP BY t0.product

) t3

INNER JOIN comparison.dbo.vlgxplc t2

ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS

LEFT JOIN

(SELECT t0.product AS product,

SUM(t0.quantity) AS onhand

FROM rbeacon.dbo.binlocat t0

GROUP BY t0.product

) t4

ON t3.product = t4.product

INNER JOIN wbau.dbo.oitm t5

ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS

LEFT JOIN wbau.dbo.ocrd t6

ON t5.cardcode = t6.cardcode

INNER JOIN wbau.dbo.oitm t7

ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS

LEFT JOIN wbau.dbo.por1 t8

ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS

LEFT JOIN wbau.dbo.opor t9

ON t8.docentry = t9.docentry

LEFT JOIN comparison.dbo.podatetest('po_num') t10

ON t10.docnum = t9.docnum

WHERE t3.shorted <> 0

GROUP BY t3.product ,

t7.itemname ,

t2.u_vlgx_plc,

t3.shorted ,

t4.onhand ,

t6.cardname,

t10.docduedate

ORDER BY t2.u_vlgx_plc,

t6.cardname ,

t3.product

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-26 : 22:51:40

Does the column have non-numeric value?

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-26 : 23:00:40

no, its numeric

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-05-26 : 23:34:26

Looks like you are passing 'po_num' (the string) into yout function, not po_num (the value). Remove the quotes
LEFT JOIN comparison.dbo.podatetest('po_num') t10
to
LEFT JOIN comparison.dbo.podatetest(po_num) t10

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-26 : 23:38:15

I get invalid column if I do that. I think it's because the joins are evaluated before the select statement, so po_num doesn't exist at the time it's looking for it, but I don't know how to get around it. Cheers!

nr
SQLTeam MVY

12543 Posts

Posted - 2008-05-26 : 23:53:53

LEFT JOIN comparison.dbo.podatetest('po_num') t10
That is passing the character value 'po_num' to the function not the value pf the column
.
Is podatetest a table valued function? If so you need to passa single value not a column.
If it's not then this won't work as it needs a table in the join statement.
Looks like you are using a table valued function in which case you can't base it on a column from another table in the query.

I suspect you either need a correlated subquery or a derived table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-27 : 00:11:23

Yes it is a table valued function.

I want to pass the value returned by the case statement and return the docduedate.... for each row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 00:18:22

Try putting ths case construct inside the function call like this

SELECT t3.product ,

t7.itemname ,

t2.u_vlgx_plc,

t3.shorted ,

t4.onhand ,

MIN(

CASE

WHEN t8.linestatus = 'O'

THEN t9.docnum

ELSE NULL

END) po_num,

t10.docduedate





FROM

(SELECT t0.product product ,

SUM(

CASE

WHEN t0.qty_topick <> t0.qty_picked

THEN t0.qty_topick - t0.qty_picked

ELSE 0

END) shorted

FROM rbeacon.dbo.shipline2 t0

INNER JOIN rbeacon.dbo.shiphist t1

ON t0.packslip = t1.packslip

WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-1, 101)

GROUP BY t0.product

) t3

INNER JOIN comparison.dbo.vlgxplc t2

ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS

LEFT JOIN

(SELECT t0.product AS product,

SUM(t0.quantity) AS onhand

FROM rbeacon.dbo.binlocat t0

GROUP BY t0.product

) t4

ON t3.product = t4.product

INNER JOIN wbau.dbo.oitm t5

ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS

LEFT JOIN wbau.dbo.ocrd t6

ON t5.cardcode = t6.cardcode

INNER JOIN wbau.dbo.oitm t7

ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS

LEFT JOIN wbau.dbo.por1 t8

ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS

LEFT JOIN wbau.dbo.opor t9

ON t8.docentry = t9.docentry

LEFT JOIN comparison.dbo.podatetest(CASE

WHEN t8.linestatus = 'O'

THEN t9.docnum

ELSE NULL

END) t10

ON t10.docnum = t9.docnum



WHERE t3.shorted <> 0

GROUP BY t3.product ,

t7.itemname ,

t2.u_vlgx_plc,

t3.shorted ,

t4.onhand ,

t6.cardname,

t10.docduedate

ORDER BY t2.u_vlgx_plc,

t6.cardname ,

t3.product

michaelb
Yak Posting Veteran

69 Posts

Posted - 2008-05-27 : 00:42:20

if i do that it cant get t8.linestatus and t9.docnum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-27 : 01:10:57

quote:
Originally posted by michaelb

if i do that it cant get t8.linestatus and t9.docnum

May be this:-

SELECT main.*,tmp.docduedate

FROM
(SELECT t3.product ,

t7.itemname ,

t2.u_vlgx_plc,

t3.shorted ,

t4.onhand ,

MIN(

CASE

WHEN t8.linestatus = 'O'

THEN t9.docnum

ELSE NULL

END) po_num,
t9.docnum,
t6.cardname




FROM

(SELECT t0.product product ,

SUM(

CASE

WHEN t0.qty_topick <> t0.qty_picked

THEN t0.qty_topick - t0.qty_picked

ELSE 0

END) shorted

FROM rbeacon.dbo.shipline2 t0

INNER JOIN rbeacon.dbo.shiphist t1

ON t0.packslip = t1.packslip

WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-1, 101)

GROUP BY t0.product

) t3

INNER JOIN comparison.dbo.vlgxplc t2

ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS

LEFT JOIN

(SELECT t0.product AS product,

SUM(t0.quantity) AS onhand

FROM rbeacon.dbo.binlocat t0

GROUP BY t0.product

) t4

ON t3.product = t4.product

INNER JOIN wbau.dbo.oitm t5

ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS

LEFT JOIN wbau.dbo.ocrd t6

ON t5.cardcode = t6.cardcode

INNER JOIN wbau.dbo.oitm t7

ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS

LEFT JOIN wbau.dbo.por1 t8

ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS

LEFT JOIN wbau.dbo.opor t9

ON t8.docentry = t9.docentry)main

LEFT JOIN comparison.dbo.podatetest(main.po_num) t10

ON t10.docnum = main.docnum



WHERE main.shorted <> 0

GROUP BY main.product ,

main.itemname ,

main.u_vlgx_plc,

main.shorted ,

main.onhand ,

main.cardname,

tmp.docduedate

ORDER BY main.u_vlgx_plc,

main.cardname ,

main.product

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources