Help on query

[Former Member]

Hi Experts,

I got this query from the forum which is very helpful to me, however, I want to insert the Item Description during execution.

SELECT T0.ItemCode, T0.[ItemName] (SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 4) AS 'Vendor Price List 1',

(SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 5) AS 'Vendor Price List 2', (SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 6) AS 'Vendor Price List 3'

FROM dbo.OITM T0

WHERE T0.ItemCode between '[%0]%' and '[%1]%'

Thanks,

Don

[Former Member · Accepted solution]

Hi Don,

Try this.

SELECT T0.ItemCode, T0.ItemName, 
(SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 4) AS 'Vendor Price List 1',
(SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 5) AS 'Vendor Price List 2', 
(SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 6) AS 'Vendor Price List 3'
FROM DBO.OITM T0
WHERE T0.ItemCode >= '[%0]' AND T0.ItemCode <= '[%1]'

Regards,

Madhan.

[Former Member]

SELECT T0.ItemCode, T0.ItemName (SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 4) AS 'Vendor Price List 1',
(SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 5) AS 'Vendor Price List 2', (SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 6) AS 'Vendor Price List 3'

FROM dbo.OITM T0
WHERE T0.ItemCode between '[%0]' and '[%1]' and
T0.ItemName between '[%2]' and '[%3]'

[Former Member]

Hi,

I do not need to make the item name as variable, my query then is still:

SELECT T0.ItemCode, T0.[ItemName] (SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 4) AS 'Vendor Price List 1',

(SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 5) AS 'Vendor Price List 2', (SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 6) AS 'Vendor Price List 3'

FROM dbo.OITM T0

WHERE T0.ItemCode between '[%0]%' and '[%1]%'

But I get an error: Incorrect syntax near the keyword 'SELECT', 2).....

Thanks,

Don

[Former Member]

Hi !

Check this

SELECT T0.ItemCode, T0.ItemName, (SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 4) AS 'Vendor Price List 1',
(SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 5) AS 'Vendor Price List 2', (SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 6) AS 'Vendor Price List 3'
 
FROM dbo.OITM T0
WHERE T0.ItemCode between '[%0]' and '[%1]'

[Former Member]

Thanks Thanja,

Also, can I include in the column the Last Purchase Price?

Thanks.

Don

[Former Member]

Hi Don,

IF you want to print the Last Buy Price, Try this Query Report.

SELECT T0.ItemCode, T0.ItemName, T0.LastPurPrc AS 'Last Purchase Price',
(SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 4) AS 'Vendor Price List 1',
(SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 5) AS 'Vendor Price List 2', 
(SELECT Price FROM ITM1 WHERE ItemCode = T0.ItemCode AND PriceList = 6) AS 'Vendor Price List 3'
FROM DBO.OITM T0
WHERE T0.ItemCode BETWEEN '[%0]' AND '[%1]'

Regards,

Madhan.