cancel
Showing results for 
Search instead for 
Did you mean: 

About query generator.

Former Member
0 Kudos
45

SAP B1 2007 A PL46

Hi All,

I have many questions about query generator :

1. It is possible to use or to call a stored procedure ? If yes, how can we implement this ?

2. I try to use this query :

declare @commercial integer

set @commercial = [%0]

declare @prjbegin nvarchar(20)

set @prjbegin = '[%1]'

declare @prjend nvarchar(20)

set @prjend = '[%2]'

SELECT T2.[DocNum], T2.[DocDate], T2.[CardCode], T2.[CardName], T3.[ItemCode], T3.[Dscription], T3.[Quantity] FROM [dbo].[OINV] T2 INNER JOIN [dbo].[INV1] T3 ON T2.DocEntry = T3.DocEntry WHERE T2.[SlpCode] =@commercial AND T3.[Project] >=@prjbegin AND T3.[Project] <=@prjend

but it don't work. How can we do to use this ?

Thanks for your help.

Best regards.

Michael

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi!

%0 can't assigned directly to a Variable you should assign them to a table-Field , example

set @commercial = (Select max(N0.docnum) from oinv n0 where n0.docnum='[%0]'

Former Member
0 Kudos

Hi,

I try this but it's not working :

declare @commercial integer

set @commercial = (SELECT SlpCode FROM OSLP WHERE SlpCode=[%0])

declare @prjbegin nvarchar(20)

set @prjbegin = 'N000001'

declare @prjend nvarchar(20)

set @prjend = 'R000003'

SELECT T2.[DocNum], T2.[DocDate], T2.[CardCode], T2.[CardName], T3.[ItemCode], T3.[Dscription], T3.[Project] FROM [dbo].[OINV] T2 INNER JOIN [dbo].[INV1] T3 ON T2.DocEntry = T3.DocEntry WHERE T2.[SlpCode] =@commercial AND T3.[Project] >=@prjbegin AND T3.[Project] <=@prjend

I have an error that is : you must specify a table name where execute the selection ...

I think we can't use %0 and the statement DECLARE.

Thanks

Michael

Former Member
0 Kudos

Hi!

Replace that with this

set @commercial = (SELECT max(SlpCode) FROM OSLP WHERE SlpCode='[%0]')

also in the same way you need to assign values to all the variables

Former Member
0 Kudos

Hi,

I try this :

declare @commercial integer

set @commercial = (SELECT Max(SlpCode) FROM OSLP WHERE SlpCode='[%0]')

SELECT T2.[DocNum], T2.[DocDate], T2.[CardCode], T2.[CardName], T3.[ItemCode], T3.[Dscription], T3.[Project] FROM [dbo].[OINV] T2 INNER JOIN [dbo].[INV1] T3 ON T2.DocEntry = T3.DocEntry WHERE T2.[SlpCode] =@commercial

I have the same error : you must specify a table...

Thanks

Michael

Former Member
0 Kudos

Hi!

Run this and try

declare @commercial integer

set @commercial = (SELECT max(T0.[SlpCode]) FROM OSLP T0 WHERE T0.[SlpCode] ='[%0]' )
SELECT T2.DocNum, T2.DocDate, T2.CardCode, T2.CardName, T3.ItemCode, T3.Dscription, T3.Project FROM dbo.OINV T2 INNER JOIN dbo.INV1 T3 ON T2.DocEntry = T3.DocEntry WHERE T2.SlpCode =@commercial

Former Member
0 Kudos

Hi,

It's work fine.

I have an another question :

i succeed to run a stored procedure in the query generator.

Now i want to pass some parameters like this :

DECLARE @return_value int

EXEC @return_value = [dbo].[MyStoredProcedure]

@Commercial = (SELECT max(T0.[SlpCode]) FROM OSLP T0 WHERE T0.[SlpCode]=[%0])

SELECT 'Return Value' = @return_value

I have an error that is

Incorrect Syntax ' 'User-defined Values' (CSHS) ('.

Could you help me ? Thanks.

Best regards,

Michael