cancel
Showing results for 
Search instead for 
Did you mean: 

Query Parameter Question

Former Member
0 Kudos

I'm trying to add a parameter range to an existing query but I'm never prompted to enter a parameter, I just get 0 records returned- I'm sure I'm missing something , here is the query-

SELECT TOP 100 PERCENT dbo.OITM.ItemCode AS Part, dbo.OITM.ItemName AS Description, dbo.OITM.OnHand, dbo.OITM.IsCommited AS Allocated,

dbo.OITM.U_toOrdPt AS OrdPt, dbo.OITM.OnHand - dbo.OITM.IsCommited AS Available, dbo.OITM.OnOrder,

CASE WHEN dbo._FSsalestots.YTD IS NULL OR

dbo._FSsalestots.YTD LIKE ' ' THEN dbo.[_YTD Sales].SAPYTD WHEN dbo.[_YTD Sales].SAPYTD IS NULL OR

dbo.[_YTD Sales].SAPYTD LIKE ' ' THEN dbo._FSsalestots.YTD ELSE dbo._FSsalestots.YTD + dbo.[_YTD Sales].SAPYTD END AS YTD,

dbo._FSsalestots.PYTD, CASE WHEN U_toPhased = 'Y' THEN 'PHASED' ELSE NULL END AS Phased

FROM dbo.OITM INNER JOIN

dbo._ItemMaster ON dbo.OITM.ItemCode = dbo._ItemMaster.[FOURTH SHIFT DATA] INNER JOIN

dbo._FSsalestots ON dbo._ItemMaster.F2 = dbo._FSsalestots.IMPN LEFT OUTER JOIN

dbo.[_YTD Sales] ON dbo.OITM.ItemCode = dbo.[_YTD Sales].ItemCode

WHERE dbo.OITM.ItemCode = '%0'

ORDER BY dbo.OITM.ItemCode

Accepted Solutions (1)

Accepted Solutions (1)

former_member583013
Active Contributor
0 Kudos

From seeing your Query I believe there are two things you can do.

1. For the Parameter use square brackets example: ItemCode = '[[%0]]'

2. Instead of using a fully qualified reference for table names try to use Alias example [[dbo]].[[OITM]] T0

Suda

Former Member
0 Kudos

When I use bracketed '[%0]' I get an invalid column name error, could this be due to the fully qualified table names?

I am used to using the full table name, do I simply substitute TO for all columns using OITM and after 'from' specify OITM versus dbo.OITM, or do I need to do anything else to correlate T0 to that table? Thx,

CH

Edited by: Chris Holmes on Jan 9, 2008 8:22 PM

former_member583013
Active Contributor
0 Kudos

I even got the same error the first time when I tried.

Please try to re-write you Query using Alias like [[dbo]].[[OITM]] T0 and refer fields with T0.ItemCode, T0.ItemName, ..

Suda

Former Member
0 Kudos

I aliased all tables and that did the trick

One other question, is it possible to change the parameter text to something more friendly than %0 item code and %1 item code?

I am doing a range for the parameters so it would be nice to have 'From Item' and 'To Item' so the user knows what info to input- Thanks!

former_member583013
Active Contributor
0 Kudos

The parameters need to have the '[[%0]]' Syntax as it is driven by the system. i don't think if would be possible to have something more user friendly

Suda

Answers (1)

Answers (1)

Former Member
0 Kudos

WHERE dbo.OITM.ItemCode = '%0'

If you mean for the %0 to be your parameter placeholder, it should be in brackets.

WHERE dbo.OITM.ItemCode = '[%0]'