SQL Query and Formula Fields 1

[davew123]

Is there any way to include a formula field in an SQL query written using 'Add Command'?

I need to take in a parameter and process it in order to derive the value needed for the query.

Regards

David Williams

 

[synapsevampire]

Not sure what a formula field has to do with a parameter?

In the Add Command you can create parameters and paste/use them in the SQL within the Add Command SQL.

I think that's what you want.

-k

 

[davew123]

Sorry, I probably didn't explain myself clearly. I have a report based on a command which takes in 2 parameters, a start date and an end date. In our system we store dates in a completely different manner (i.e. not M/D/Y or any combination of that format).

To make life easier I want to be able to take in dates in a standard M/D/Y format and the convert them using a formula field to our internal format. I then want to use these values in the command query.

If I insert the formula field into the 'record selection' it screws up the indexing.

 

[StanKorn]

Create a formula that converts a date out of your internal layout into a traditional mm/dd/yyyy format. set up a parameter using input based on the formula you just created and then write an if/then statement to generate your report parameters using standard date formats.
It takes a few steps but once you get the sequence down it is an easy task.
Hope this helps,
stan


Remember - only dead fish go with the flow.

 

[synapsevampire]

Are you using a proprietary database?

Formats of dates aren't as important as the data type.

I will guess that what you're trying to say is that you have a non date type field, and you wish to treat it as a date.

First, tell your dba that you have a surprise for them outside, and when they leave, lock the door and have their badge cancelled.

Using a SQL based database you can create a SQL Expression in Crystal and base record selections off of the SQL Expression. Since you're using an Add Command, I would suggest that you build Views for any date field that isn't a date type and use the Views in lieu of the tables going forward.

-k