cancel
Showing results for 
Search instead for 
Did you mean: 

passing arbitrary values to query

dov_luft3
Explorer
0 Kudos

When I execute the following query I can select value for parameter '[%]' only from a list of dates that exists in the column AITM.upDatedate .

How can I pass values to parameter '[%]' independent from the values in the column.

for example I to search data prior to 01/07/2020 also but 01/07/2020 it does not exists in AITM.upDatedate.

for now if I put that date I got the message that no data was found .

Thanks

Dov



/* select * from AITM T1 */   /* select * from AITM T2 */


DECLARE @Date1 DATETIME
DECLARE @SaleItem INT
DECLARE @MinQnty Numeric (19,6)


SET @Date1 = /* T2.upDatedate*/ '[%0]'
SET @SaleItem =  /* T1.U_SaleIetm*/ '[%1]'
SET @MinQnty =  /* T1.OnHand*/ '[%2]'


select t1.ItemCode,
t1.ItemName  ',
t1.OnHand ,
t1.LastPurCur ,
t1.LastPurDat ,
t1.LastPurPrc ,
t1.UpdateDate ,


from AITM  t1 
where t1.UpdateDate=(select max(t2.UpdateDate)
					from AITM  t2 
				               where  t2.ItemCode = t1.ItemCode
					and t2.UpdateDate<=@Date1
					group by t2.ItemCode
					)


and t1.LogInstanc = (select max(t2.LogInstanc)
                      from AITM t2
					  where t2.ItemCode = t1.ItemCode
					  and t2.UpdateDate<=@Date1
					  group by t2.ItemCode
					  )
and t1.U_SaleIetm=@SaleItem
and t1.OnHand>=@MinQnty
order by t1.ItemCode

Accepted Solutions (0)

Answers (2)

Answers (2)

Johan_H
Active Contributor
0 Kudos

Hi Dov,

When used like this:

/* select * from AITM T1 */ /* select * from AITM T2 */

you do not need to determine the table twice, also better not use the same table alias inside the comment and outside.

/* select * from AITM x */

When used like this:

SET @Date1 = /* x.upDatedate */ [%0]

The chosen field only servers to name the variable for the user. You can still fill in any date you like. Also, a variable [%0] only needs to be inside quotes when the expected value is of type string (NCHAR, NVARCHAR, VARCHAR, etc.)

Regards,

Johan

JesperB1
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hello Dov,

What is the logic of that, somehow if you see that the first date is lets say 01/07/2020, then you know there are no records before that. Correct?

Alternatively write a case query somehow using the first date -1 day.

Not sure this is helpful.

Jesper