CASE function

Hi,
I have a date column where the application users puposely enter a date way
in the future as part of their business rule. For instance, entering the yea
r
2033 if the given value for this date column is unknown.
I need to programmatically retrieve this date and represent those
out-of-whack dates to show as the current date + 10 days.
I created the following SELECT stmt. using the CASE function:
SELECT ....
CASE post_datetime
WHEN post_datetime > getdate()+365 THEN getdate()+10
...
However, SQL QA returns an error (Incorrect syntax near '>') when I try to
execute this stmt. What am I doing wrong here. Please help. Thanks.
Regards,
- Rob.Rob wrote:
> Hi,
> I have a date column where the application users puposely enter a date way
> in the future as part of their business rule. For instance, entering the y
ear
> 2033 if the given value for this date column is unknown.
> I need to programmatically retrieve this date and represent those
> out-of-whack dates to show as the current date + 10 days.
> I created the following SELECT stmt. using the CASE function:
> SELECT ....
> CASE post_datetime
> WHEN post_datetime > getdate()+365 THEN getdate()+10
> ...
> However, SQL QA returns an error (Incorrect syntax near '>') when I try to
> execute this stmt. What am I doing wrong here. Please help. Thanks.
> Regards,
> - Rob.
Try this instead:
CASE WHEN post_datetime > GETDATE() + 365 THEN GETDATE() + 10 ELSE
post_datetime END|||> SELECT ....
> CASE post_datetime
> WHEN post_datetime > getdate()+365 THEN getdate()+10
There are two general forms of the CASE expression (it is not a function).
You can either say
CASE [expression] WHEN [value] THEN [value] END
or
CASE WHEN [expression][operator][value] THEN [value] END
You combined the two in a way I don't recall ever seeing (and as you have
found out, the syntax is invalid). You need the latter, because you are
testing a more complex expression than simple equality.
Try:
SELECT
CASE WHEN post_datetime > getdate()+365 THEN getdate()+10 ENDsql

Cascading(?) Parameters Help

I have 2 parameters that are of type string. The user can enter anything they want in them. The third parameter is query based and uses the first 2 parameters to get a list of people. Is there a way I can prevent the third parameter from propigating until the first 2 are both filled in? The first 2 cannot be drop downs however, they are used in a wild card fashion.

Thanks!

Yes. Set the default on the text boxes to NULL.

Then it shouldnt try to populate until both are not null.

I just created a test report and it did not populate until both names were entered.

BobP

|||

In RS2005? I just did the same thing, and the 3rd Parameter became available after I entered something in the 1st parameter (no values because the query is where firstname = @.param1 and lastname = @.param2). However, the real query is using firstname LIKE @.param1 and lastname LIKE param2 and thus populates the list as soon as I enter the first name. Which is confusing because param2 is empty. Any ideas?

|||

Ah.. I see. I had the 'Allow blank value' selected. However, not pretty, it works.

|||

So it IS working for you, right?

BobP

|||Yes.. thanks.

carriage return problem..

While retrieving user input from an input control, eg: multi-line textbox, and inserting it into the database, the carriage return or the 'Enter' key is not getting inserting into the database.. instead it inserts a quad ( square ) in the database.. also the text typed after the 'Enter' key is not getting inserted into the database.. please help.The carriage returnis getting inserted. The square box confirms that. When rendering it to a web page, you need to replace the carriage returns with their html equivalent - "<br />"|||I'm having the same problem, how would this be accomplished using VB?|||

VB.Net
<%# Eval("MyValue").ToString().Replace(vbcrlf,"<br />") %>

C#
<%# Eval("MyValue").ToString().Replace("\r","<br />") %>

|||

Thanks!

It works great!

I was also able to get it going with the following a few minutes ago:

<%# Eval("MyValue").Replace(Environment.NewLine, "<br />") %>

Which method do you recommend or are they both good?

|||

With the .NET framework, there are approximately 63 ways to skin most particular cats. The difference between them is most often negligible, and you should use whatever you prefer so long as your page load doesn't appear to be adversely affected. Occasionally you will get a guru tell you to use one option rather than another, because it shaves nanoseconds off the operation, and they will have benchmark tests to prove it. Personally, I think life is too short. I usually use the option that requires less typing, unless I am informed of a convincing reason to use another.

Environment.NewLine has the benefit that it can be used regardless of page language, so I shall use it in future when I answer this question without knowing the language the poster is using. Quite simply, it meets my desire to do less typing.