Skip to Content
avatar image
Former Member

replacing SQL parameters by [%] in sbo

Nov 24, 2017 at 10:29 AM | 110 Views

I have the following simple sql query which is part of longer SQL query .

I want to run the entire query in SBO .

question : How to replace SQL parameters in the query by SBO Parameters ( [%0] )

declare @start date = convert (date,'01/01/2017',103),

@end date = convert (date,'01/12/2017',103) ;

select convert (date,t0.DocDate,103) AS DocDate ,

'curentPeriod ' as curentPeriod, t0.DocTotal as DocTotal , t0.CardCode as cardCode, T0.SlpCode as Splcode

from OINV t0

where T0.DocDate between @start and @end

and t0.CardCode <>3000001

union all

select convert (date,t0.DocDate,103) AS DocDate ,

'curentPeriod ' as curentPeriod,

t0.DocTotal as DocTotal ,

t0.CardCode as cardCode,

T0.SlpCode as Spl from ORIN t0

where T0.DocDate between @start and @end

and t0.CardCode <>3000001 ;

SAP Business One
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    avatar image
    Johan Hakkesteegt
    Nov 24, 2017 at 11:01 AM

    Hi,

    Here is an example syntax:

    /* select  * from OJDT t1   */
DECLARE @EndDate DATETIME
SET @EndDate = /* t1.RefDate */  '[%0]'
SELECT @EndDate

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 24, 2017 at 12:29 PM

    Hi

    Thanks for both answers.

    CTE works , haven't tried yet TVF.

    dov

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 11, 2017 at 08:06 AM

    Hi John

    I guess it was my mistake, sorry.

    i am quite Newbie to SBO . can i ask you more questions concerning building complicated queries in query generator ?

    Best regards,

    Dov

    Add comment
    10|10000 characters needed characters exceeded

    • Johan Hakkesteegt
      Dec 11, 2017 at 09:30 AM

      Hi Dov,

      Don't worry about it, I don't think it was your mistake. This SCN platform is only a year old and there is still a lot wrong with it.

      Of course you can ask as many questions as you like. However, please post a new question for each issue. That way more people will try to help you.

      Regards,

      Johan

      P.S. when you clicked 'Accept' the first time, did you double-click, or did you have to click a second time because the page did not show you right away that the answer was accepted?

  • avatar image
    Former Member
    Dec 11, 2017 at 11:07 AM

    Hi;

    I really dont remember;

    I

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 11, 2017 at 06:38 PM

    Hi;

    Screen shoot 1 capture-1.png

    Screen shoot 2 after accepting capture-2.png

    I am using Google Chrome on Dell XPS laptop OS is Window 10 pro

    Add comment
    10|10000 characters needed characters exceeded