Crystal/Oracle parameterized query to use variable

Question

I currently have a Database Command object in my Crystal Report that looks something like

SELECT *
    FROM table
    WHERE field = {?EndDate}

I want to change it so it looks more like

IF {?EndDate} = DATE '1900-01-01' 
    MyVariable = ADD_MONTHS(LAST_DAY(SYSDATE), -1)
ELSE
    MyVariable = {?EndDate}

SELECT * 
    FROM table
    WHERE field = MyVariable

I kind of get the idea of how to build a dynamic query to do this, but I don't know if that's really what I want to do. Could someone point me in the right direction, please? Thanks.

glasnt · Accepted Answer · 2010-02-10 21:38:19Z

1

Select * 
from table
where field = decode
         (myvariable,'1900-01-01',ADD_MONTHS(LAST_DAY(SYSDATE), -1)
                    ,myvariable)

[Reads: select * from my table where field is equal to.. Decode myvariable; if it's 1-1-1900, then get a month before the current sysdate, otherwise, use the variable]

Something like that.

answered Feb 10, 2010 at 21:38

glasnt

2,9838 gold badges35 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SarekOfVulcan Over a year ago

Ah, that makes it a bit easier. Wordy, since I use it about 5 times, but doable. Thanks!

glasnt Over a year ago

This way you don't need to worry about variable declarations, so it might be a tad easier for you. The duplication isn't as bad as it seems, but it's duplication none the less.

SarekOfVulcan Over a year ago

Well, I don't mind worrying about variable declarations, if it'll make the code any more efficient...

Collectives™ on Stack Overflow

Crystal/Oracle parameterized query to use variable

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related