Unable to take prompt fields from a different table.

[Former Member]

Hi,

I've been working on creating a pretty simple query to display certain documents with a selected creation range.  I've been able to get as far as getting the query to work with the following code:

/* Select from dbo.[@CRCSJobmaster] T0 */

declare @CreateDaylower as Date

declare @CreateDayupper as Date

/* WHERE */

set @CreateDaylower = /* T0.[CreateDate]  */  '[%0]'

set @CreateDayupper = /* T0.[CreateDate]  */   '[%1]'

/* Main Query Definition */

SELECT T0.[CreateDate] AS 'CreateDate', T0.[Code] AS 'Code', T0.[U_cardName] AS 'Customer Name'

FROM  [dbo].[@CRCSJOBMASTER] T0

WHERE (T0.[CreateDate]>='[%0]' AND T0.[CreateDate]<='[%1]')

ORDER BY T0.[CreateDate]

FOR BROWSE

This code works fine, however when I try to redirect the declared variables to a separate table in order to make the prompt text less confusing I will always receive some kind of error.  The modified code that doesn't work is:

/* SELECT FROM [dbo].[OSRT] P0 */

declare @StartDate as date

/* WHERE */

set @StartDate =  /* P0.FromDate */ ‘[%0]‘

/* SELECT FROM [dbo].[OSRT] P1 */

declare @EndDate as date

/* WHERE */

set @EndDate = /* P1.ToDate */ ‘[%1]‘

/* Main Query Definition */

SELECT T0.[CreateDate] AS 'CreateDate', T0.[Code] AS 'Code', T0.[U_cardName] AS 'Customer Name'

FROM  [dbo].[@CRCSJOBMASTER] T0

WHERE (T0.[CreateDate]>'[%0]' AND T0.[CreateDate]<'[%1]')

ORDER BY T0.[CreateDate]

FOR BROWSE

Of course, the error I receive is always no matching records from some random database table not even referenced in the query.  I know there is something I'm missing terribly here, but I haven't been able to figure it out with the research I've done.  I know that the first code works, but users will likely feel confused with two prompts that pop up as 'CreateDate'.  Any advice you might have is appreciated.

Running SAP B1 8.8

Thank you,

[KennedyT21 · Accepted solution]

Hi

Try This

/* SELECT FROM [dbo].[OSRT] P1 */

declare @StartDate as date

declare @EndDate as date

set @StartDate =  /* P0.FromDate */ '[%0]'

set @EndDate = /* P1.ToDate */ '[%1]'

SELECT T0.[CreateDate] AS 'CreateDate', T0.[Code] AS 'Code', T0.[U_cardName] AS 'Customer Name'

FROM  [dbo].[@CRCSJOBMASTER] T0

WHERE (T0.[CreateDate]>@StartDate  AND  T0.[CreateDate]<   @EndDate )

ORDER BY T0.[CreateDate]

FOR BROWSE

Regards

Kennedy

[Former Member · Accepted solution]

Thank you Kennedy,

The code you provided is working, I just had to change the reference to P0 to P1.  I guess referencing the same table twice for my variables was what somehow caused my error.

Brandyn

[former_member325312]

Hi Brandyn Baryski

Check  the Following Query

Declare @sdate as datetime 

Declare @edate as datetime 

Select @sdate = C.[FromDate] From [dbo].[OSRT]  C Where C.[FromDate]  = '[%0]'

Select @edate = C.[ToDate]   From [dbo].[OSRT]  C Where  C.[ToDate]  =  '[%1]'

SELECT T0.[CreateDate] AS 'CreateDate', T0.[Code] AS 'Code', T0.[U_cardName] AS 'Customer Name'

FROM  [dbo].[@CRCSJOBMASTER] T0

WHERE T0.[CreateDate]>=@sdate  AND  T0.[CreateDate]<= @edate

ORDER BY T0.[CreateDate]

Regards

Jenny