Thanks but I knew about this method. I wanted to find a SQL solution if there was one. I'm beginning to think, though, that my wish was in vain.
-Patti ----- Original Message ----- From: "Shawn Regan" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, September 18, 2002 12:22 PM Subject: RE: Returning columns with null values when specific columns are unknown at runtime > You could get the fieldnames of the table. Then check every record for a > null value for every field. > > lets say table1 has these fields: key | col1 | col2 | col3 | col4 > > But I don't know the fieldnames of this table. In CF you can get the > fieldnames of a table with this queryname.ColumnList > > then hand each of those to a select statement checking to see if any records > have that field's value as NULL > > <cfloop query="fieldname"> > <cfquery> > select * from table1 where fieldname IS NULL > or > select key,fieldname from table1 where fieldname IS NULL > </cfquery> > </cfloop> > > Not sure if this is still even close to the results your trying to get. > > Shawn Regan > pacifictechnologysolutions > 15530-B Rockfield Blvd. Suite 4 > Irvine, CA 92618 > 949.830.1623 > w w w . p t s 1 . c o m > > > > -----Original Message----- > From: Patti G. L. Hall [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, September 18, 2002 8: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 > > > > > > > > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
