Where clause needs to equal a value that is in a table that is not in the report

[shannonlp]

Hi,

I have a report where I need the eff_date to = a value in a table that is not able to be joined to the tables currently being used in my report.

There is a calendar table within our DBMS that has 4 columns (date_id, prev_bus_date, curr_bus_date, and next_bus_date). It always has only one row of data. The tables I’m using in my report can’t be joined to this table because none of them have date_id as a foreign key.

Suggestions I've received are to either use a variable or create a stored procedure. To create a variable, wouldn't the calendar table need to be available in my report? Also, I’m not clear on how to approach using a stored procedure. If I need to create a stored procedure it would be something like

CREATE PROCEDURE PrevBusDate
AS
SELECT prev_bus_date
FROM dbo.metric_sys_date
GO

From there I’m not clear on whether this Stored Procedure would need to be joined to the tables that already exist in my report.

If anyone has any ideas on how to approach this I would greatly appreciate hearing back!

 

[IdoMillet]

Since the table has only one row, simply add it to the report without any joins. You can then gain access to the information in the table without risking record inflation.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[shannonlp]

Adding this table without any joins give me the following error when refreshing the report:

‘Failed to retrieve data from the database. Details: Only one command can execute at a time.’

It’s generating two sql statements which Crystal Reports cannot handle.

My next step is going to attempt to create a variable based on a sql statement. I would need the variable to store the value of

SELECT prev_bus_date
FROM dbo.metric_sys_date

Anyone know if this is possible?