cancel
Showing results for 
Search instead for 
Did you mean: 

execute stored procedure in query manager

Former Member
0 Kudos

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
end

I 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

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi,

The procedure exist, if I put a select * from oitm in it the SP works

best regards

Mattias

Former Member
0 Kudos

Hi!

Whtz the error its throwing when u execute the SP in Query Manager?

Former Member
0 Kudos

Also, try by removing this condition on your query and execute

T1.CardCode like '%'*

Former Member
0 Kudos

The error I get is non relevant: 'service contracts'

Already removed the parameter, no difference

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Gordon,

This query runs without any problem, but gives me too mutch rows.

From eatch item I only need the last time discount, therefore I used the row function

Thx for the help

Former Member
0 Kudos

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

Former Member
0 Kudos

Hello Gordon,

first of all: thx for the help!

the query returns the same result as the previous on

I am also still looking, but did not found a solution yet.

best regards

Mattias

Former Member
0 Kudos

If you need less rows, you have to reduce the number of your columns in your query. From the minimum columns to increase one by one.