ORDER BY clause returning invalid column

[former_member239716]

Hi guys - I am almost embarrassed to come to the community with this one, but a relatively simple query has me baffled as to why adding an ORDER BY clause, breaks the query.

The following query works fine until I add the ORDER BY and then it states the T2.ItemCode column is invalid. It must be something to do with the WHERE clause but it has me stumped... it has had me stumped all morning and part of the afternoon.

Any help will of course, be greatfully appreciated:

SELECT T1.[DocNum]
, T1.[CardCode] AS 'Customer/Supplier No.'
, T1.[CardName] AS 'Customer/Supplier Name'
, T2.[ItemCode]
, T2.[Price] AS 'Price Quoted'
, T1.[CreateDate] AS 'Date Quoted'

FROM OQUT T1
INNER JOIN QUT1 T2 ON T2.DocEntry = T1.DocEntry

WHERE T2.[ItemCode] = [%0]

ORDER BY T2.ItemCode

Regards

Roy Bright

[former_member185682 · Accepted solution]

Hi Roy,

The error occurs because the ItemCode field is a varchar field then you need put your parameter like this:

WHERE T2.[ItemCode] = '[%0]'

Your query should look like this:

SELECT 
	T1.[DocNum]
	, T1.[CardCode] AS 'Customer/Supplier No.'
	, T1.[CardName] AS 'Customer/Supplier Name'
	, T2.[ItemCode]
	, T2.[Price] AS 'Price Quoted'
	, T1.[CreateDate] AS 'Date Quoted'
FROM 
	OQUT T1
	INNER JOIN QUT1 T2 ON T2.DocEntry = T1.DocEntry
WHERE 
	T2.[ItemCode] = '[%0]'
ORDER BY 
	T2.ItemCode

Hope it helps.

Kind Regards,

Diego Lother

[former_member239716]

*slaps forehead* agggh - so simple - hahaha Thanks Diego, at this rate, I am gonna have to put you on the payroll hahaha

Regards

Roy

[former_member185682]

hahahhahahahah.

I stay happy to collaborate.

Have a nice day.

Kind Regards,

Diego Lother