Skip to Content
0
Former Member
Apr 12, 2013 at 05:11 PM

Unable to take prompt fields from a different table.

42 Views

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,