cancel
Showing results for 
Search instead for 
Did you mean: 

Sort the Prompting Parameters in SAP B1 SQL Server

former_member844160
Discoverer
0 Kudos

Hi

Kindly request your assistance to sort out the values in SAP B1 Query generator.

/*SELECT FROM [dbo].[ORCT] p1*/

declare @FROM as Date

declare @TO as Date

/* WHERE */

set @FROM = /* p1.docdate */ '[%0]'

set @TO = /* p1.docdate */ '[%1]'

----------------------------------------------------------------------

select ORCT.DocNum As 'Receipt Num' ,ORCT.DocDate AS 'Receipt Date'

from ORCT

where ORCT.NoDocSum<>0

AND ORCT.DocDate>=@FROM

AND ORCT.DocDate<=@TO

order by ORCT.DocNum

-------------------------------------------

Here above I have declared two parameters " @FROM as Date and @TO

The query is running OK and just need to know whether there is any method to sort these two DATES when it is being prompted in form .

Thanks

Accepted Solutions (0)

Answers (2)

Answers (2)

Johan_H
Active Contributor

Hi,

The order of the parameters when prompted is determined by the number you give it:

  1. [%0]
  2. [%1]
  3. [%2]
  4. etc.

Even when you use a parameter multiple times.

By the way, two small tips:

1. in case of dates and numbers, better not use quotes around the parameter. So this:

set @FROM = /* p1.docdate */ '[%0]'

would be better like this:

set @FROM = /* p1.docdate */ [%0]

2. With MS SQL Server, the type for dates is DATETIME. So this:

declare @FROM as Date

would be better like this:

declare @FROM as Datetime

Regards,

Johan

LoHa
Active Contributor

Hi Gamma,

the value asked for is every time the name of the used field.

But for that you can use each field that matches your needs.

Try this

/**SELECT FROM [OFPR] T0 **/
DECLARE @DocDateFrom AS Date
/* WHERE */
SET @DocDateFrom = /* T0.F_RefDate */ '[%0]'

/**SELECT FROM [OFPR] T1 **/
DECLARE @DocDateTo AS Date
/* WHERE */
SET @DocDateTo = /* T1.T_RefDate */ '[%1]'

If you won't find the right field, it is possible to setup a UDT just to use the names (I did so as well)

Like this. It is not comfortable, but it's worth.

/**SELECT FROM [@QRY_NAMES]  T0 **/
DECLARE @ShipDateFrom AS Date
/* WHERE */
SET @ShipDateFrom = /* T0.U_ShpDtFrm */ '[%0]'

/**SELECT FROM [@QRY_NAMES]  T1 **/
DECLARE @ShipDateTo AS Date
/* WHERE */
SET @ShipDateTo = /* T1.U_ShpDtTo */ '[%1]'

regards Lothar