You'd also need an AS after the END for each column ... and I'm not sure what happens when the column is not null, but I suspect it would return NULL unless there's an ELSE clause in which case you'd be saying CASE WHEN col1 is null THEN col1 ELSE col1 END which ends up being the same thing as SELECT * FROM mytable...
S. Isaac Dealey Certified Advanced ColdFusion 5 Developer www.turnkey.to 954-776-0046 > interesting..... > -Patti > ----- Original Message ----- > From: "Jim Curran" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Wednesday, September 18, 2002 12:15 PM > Subject: RE: Returning columns with null values when specific columns are > unknown at runtime >> You can try this, I'm not sure wif this will work if col1 is null for > record >> 1, and has a value for record 2 but you can give it a try: >> >> >> SELECT >> CASE WHEN col1 is Null THEN col1 END, >> CASE WHEN col2 is Null THEN col2 END, >> CASE WHEN col3 is Null THEN col3 END, >> CASE WHEN col4 is Null THEN col4 END, >> FROM mytable >> >> - j >> >> >> -----Original Message----- >> From: Patti G. L. Hall [mailto:[EMAIL PROTECTED]] >> Sent: Wednesday, September 18, 2002 11:56 AM >> To: CF-Talk >> Subject: Re: Returning columns with null values when specific columns >> are unknown at runtime >> >> >> Ok, I'm willing to give this a try... but I still have a question. >> >> My real table has 27 columns. I never know which columns will be null. >> Does that mean I want a case statement for each column that could >> possibly >> be null then? >> >> It seems like the select statment here is directly tailored to the >> example > I >> used below... I will never know that much detail beforehand. I only put > the >> example to give a visual of what I may need to return. >> >> If I send in primary key 1 then I want to return columns 3 and 4, but >> only >> if they acutally are null. The next time I run the query I'd be sendign > in >> primary key 2 and in that case I'm returning columns 1, 3 and 4. >> >> So, I just wanted to check ... Can you explain how this is actually > working? >> Is it relying on my explictly stating which columns I'm returning at any > one >> time? If it is, then it's still not what I'm looking for. And perhaps > what >> I'm looking for isn't possible. >> >> Thanks - Patti >> ----- Original Message ----- >> From: "Jim Curran" <[EMAIL PROTECTED]> >> To: "CF-Talk" <[EMAIL PROTECTED]> >> Sent: Wednesday, September 18, 2002 11:26 AM >> Subject: RE: Returning columns with null values when specific columns are >> unknown at runtime >> >> >> > SELECT CASE WHEN pk = 1 THEN col3 END AS newcol1, >> > CASE WHEN pk = 1 THEN col4 END AS newcol2, >> > CASE WHEN pk = 2 THEN col1 END AS newcol1, >> > CASE WHEN pk = 2 THEN col2 END AS newcol2 >> > FROM mytable >> > >> > >> > -----Original Message----- >> > From: Patti G. L. Hall [mailto:[EMAIL PROTECTED]] >> > Sent: Wednesday, September 18, 2002 10:54 AM >> > To: CF-Talk >> > Subject: SQL: Returning columns with null values when specific columns >> > are unknown at runtime >> > >> > >> > Is there a way to write a MSSQL 2k query that will return a result set >> that >> > contains only columns with null values when you don't know explicitly >> which >> > columns those will be? >> > >> > So if I have this data >> > >> > pk | col 1 | col 2 | col 3 | col 4| >> > 1 1 2 null null >> > 2 null 4 null null >> > >> > I'd like a query that would return col 3 and col 4 where pk = 1 or col > 1, >> > col 3 and col 4 where pk=2. >> > >> > Is this possible? >> > -Patti >> > >> > >> > >> >> > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
