May 2, 2003 at 8:38 am
I am constructing a query which is being used by a piece of third-party software to construct a chart/graph. Thus, my query needs to be consistent with their model.
The query that I am trying to get working looks like this:
SELECT SampleDate, cast(d1 as int) as d1,d2,d3, cast((at2 <= '6') as int) as ad2 FROM dbo.QIMS_SPREADSHEET WHERE SampleDate <= '05/02/2003' AND ClientID = 'xyz') ORDER BY SampleDate
The charting software is going to plot the elements d1,d2,d3 and ad2 against the SampleDate. The software expects, and requires, that the elements d1, d2, d3 and ad2 to be numerical so that their values can be plotted.
Anyway, my challenge is that I need the value aliased to "ad2" to be non-zero if field "at2" is <= 6. Conversely, the value for "ad2" should be zero if the field at2 is greater than 6.
Obviously, my query above doesn't work, but I am providing it "as-is" to show my intent. I presume that I need to create a temp database, clean up my data as needed, then pass a query on the temp db to the charting software. Is this a good method? Is there an easier/faster way? I do care about performance, as this will be used frequently. Care to provide me with an example? Thanks!
Any help would be appreciated.
regards,
john
May 2, 2003 at 8:53 am
You need to use case instead of cast.
Is at2 a character field or a number field? I'm assuming it's a character field since you're comparing it to '6'. So the case part would be
case when at2 <= '6' then 1 else 0 end as ad2
Of course if at2 is numeric then just remove the quotes.
Jay Madren
Jay Madren
May 2, 2003 at 11:16 am
Jay, your suggestion works great. Thanks very much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply