on 08-21-2009 2:24 PM
Hello,
I am trying the folowing query to run in sbo, without success
A second idea is to put it in a stored procedure, but it doesn't run in SBO
ALTER procedure [dbo].[DS_LaatstePromos] as begin
select *
from (
select
ROW_NUMBER() OVER(PARTITION BY T0.ItemCode,T1.Listnum ORDER BY T0.ItemCode,T1.FromDate DESC) as row
, T0.ItemCode, T0.ItemName, T4.Price,CASE when T1.AutoUpdt = 'Y' then T4.Price-(T4.Price*T1.Discount/100) else T1.Price end as 'promo',
T1.Discount,T1.FromDate, T1.ToDate, T3.ListNum, T3.ListName
from OITM T0
inner join SPP1 T1 on T1.ItemCode = T0.ItemCode AND T1.CardCode like '*%'
inner join OPLN T3 on T3.ListNum = T1.ListNum
inner join ITM1 T4 on T4.ItemCode = T0.ItemCode AND T4.PriceList = T1.ListNum
Where T1.Price> 0) x
where x.ROW = 1
endI tought to run the query I just have to type "exec DS_LaatstePromos"
Already read post like
But the comment lines bring no release
Can somebody help me with this?
Mattias
Hi!
As your procedure seems to be a ALTER, do u have the Procedure first in your DB ?
Please check that. Also try once with CREATE Statement
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try this query first to see what you can get:
SELECT T0.ItemCode, T0.ItemName, T4.Price,
CASE when T1.AutoUpdt = 'Y' then T4.Price-(T4.Price*T1.Discount/100) else T1.Price end as 'promo',
T1.Discount,T1.FromDate, T1.ToDate, T3.ListNum, T3.ListName
from OITM T0
inner join SPP1 T1 on T1.ItemCode = T0.ItemCode AND T1.Price> 0
inner join OPLN T3 on T3.ListNum = T1.ListNum
inner join ITM1 T4 on T4.ItemCode = T0.ItemCode AND T4.PriceList = T1.ListNum
Where T1.CardCode like '[%\]%'
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try this one then:
SELECT T0.ItemCode, T0.ItemName, T4.Price,
CASE when T1.AutoUpdt = 'Y' then T4.Price-(T4.Price*T1.Discount/100) else T1.Price end as 'promo',
T1.Discount,T1.FromDate, Max(T1.ToDate), T3.ListNum, T3.ListName
from OITM T0
inner join SPP1 T1 on T1.ItemCode = T0.ItemCode AND T1.Price> 0
inner join OPLN T3 on T3.ListNum = T1.ListNum
inner join ITM1 T4 on T4.ItemCode = T0.ItemCode AND T4.PriceList = T1.ListNum
Where T1.CardCode like '[%\]%'
Group By T0.ItemCode, T0.ItemName, T4.Price,T1.AutoUpdt,T1.FromDate, T1.Discount, T3.ListNum, T3.ListName,T1.Price
| User | Count |
|---|---|
| 2 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.