null values are not indexed btw. (maybe thigs have changed in the last
few years but) using a value like 0 as a quasi null means that
select user_id from users where b_sysadmin is null
versus
select user_id from users where b_sysadmin = 0
do an explain plan in oracle or execution plan in sql
I figured I'd jump in late and add my $.02 worth! The following will return
a result set with all of the null columns values. If you need the names you
can always retrieve that in CF (query.ColumnList).
CREATE PROCEDURE rGetNullColumns
@tnPrimaryKey int
AS
SET NOCOUNT ON
DECLARE
Issac,
I hope you understand that CURSORS are not the FASTEST thing
in SQL Server.. i have personally written many cursors apps to
do BATCH Processing.. but i wouldnt recommend SQL Server Cursors
for a REAL TIME processing.. Well in Oracle its a different Story...
Depends on the DB. I agree T-SQL
Issac,
Isaac actually...
I hope you understand that CURSORS are not the FASTEST
thing in SQL Server..
Absolutely... i use them ever so sparingly...
i have personally written many cursors
apps to
do BATCH Processing.. but i wouldnt recommend SQL Server
Cursors
for a REAL TIME
But then, to know for sure, we'd have to load test both. :)
Am getting Brain Farted with all the testing am doing...
Nah..NOT Another One :)
Joe
-Original Message-
From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 11:41 PM
To: CF-Talk
Subject:
No particular wisdom to impart -- I just wanted to post to this thread
that is using so much bandwith that could otherwise be devoted to the
Re: Jsp Vs Cfm (CFMX) -- Test Code thread-- %-0)
Apologize in advance!
Dick
__
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 2nullnull
2null4
The only thing I can think to do is create a bunch of OR statements i.e.:
WHERE colA IS NULL OR colB IS NULL OR colN IS NULL
~Simon
Simon Horwith
Macromedia Certified Instructor
Certified Advanced ColdFusion 5 Developer
Fig Leaf Software
1400 16th St NW, # 220
Washington DC 20036
Thanks, but that doesn't get me where I want.
I ONLY want to return the columns that ARE null, and I never know which ones
those are when I run this query... so what I need is something for the
select statement.
-Patti
- Original Message -
The only thing I can think to do is create a
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
How about this or am I still missing something
Select * from TABLENAME
WHERE col1 IS NULL OR col2 IS NULL OR col3 IS NULL OR col4 IS NULL
Candace K. Cottrell, Web Developer
The Children's Medical Center
One Children's Plaza
Dayton, OH 45404
937-641-4293
http://www.childrensdayton.org
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
Sorry, but you made the same mistake that simon did.
I don't want to return select *
I want to return select (only columns that are null and I don't know what
they are up front) where primarykey = some number
I didin't actually have any questions regarding the where statement at all.
I'm aware
Candace Cottrell wrote:
How about this or am I still missing something
Select * from TABLENAME
WHERE col1 IS NULL OR col2 IS NULL OR col3 IS NULL OR col4 IS NULL
I think he wants the names of the colums that have null values as a
result. Though I have no idea how to do that, or what he
Patti,
One thing you've got to keep in mind is that if your query finds NULLs in
100 rows and amongst those 100 rows there is at least one instance of a NULL
value in each of your 26 columns, you WILL get back all columns for all
rows. Just want to make sure you're clear on that.
~Simon
Simon
As I told Patti off-list, this could be done with a cursor. That said, I
hazard to guess that whatever database solution you find that does JUST
return the data she's looking for, it won't offer much (if any) performance
advantage over parsing things out manually.
~Simon
Simon Horwith
Always for a single record at a time?
-Original Message-
From: Patti G. L. Hall [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 10:58 AM
To: CF-Talk
Subject: Re: Returning columns with null values when specific columns are
unknown at runtime
Sorry, but you made the same
I think you would need to right some type of union statement that would have
to look at each field in your table separately. it would then return a
record set with only the fields that were blank, actually it would only
return the key of the record to cold fusion but in query ann it would show
Ok, I think I get it :) duh. Ahh your right it will return all columns.
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: Simon Horwith [mailto:[EMAIL PROTECTED]]
Sent: Wednesday,
Do you know the fieldnames? Or is this something that has to be figured out
also?
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:
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,
You might want to try something like
declare @fields varchar(200)
set @fields='';
select
@fields=@fields+ CASE WHEN FieldName1 IS NULL THEN 'FieldName1,' END,
@fields=@fields+ CASE WHEN FieldName2 IS NULL THEN 'FieldName2,' END
from TableName
select @fields as ColumnName
You can loop around
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
If you're talking about returning the names of the columns that are null,
you might be able to accomplish this with a creative stored procedure... You
would have to draw a query from the syscolumns table to get the names of the
columns in that table ( unless you knew all the column names ahead of
Why wouldn'g restricting by the primary key force the query to look at only
the row I've specified?
- Original Message -
From: Simon Horwith [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, September 18, 2002 12:09 PM
Subject: RE: Returning columns with null values when
yes
- Original Message -
From: Smith, Matthew P -CONT(DYN) [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, September 18, 2002 12:10 PM
Subject: RE: Returning columns with null values when specific columns are
unknown at runtime
Always for a single record at a time?
no I don't. That's the problem. At any time it could be any random number
of fields that come up empty. I know how to parse out the empty fieldnames
in CF, I wanted to find a SQL solution cause I'm going to have to do this
same thing for a lot of tables one right after the other and I wanted
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
*ahem* he? Damn, who told me about the sex change? I really wish people
would keep me up to date on this stuff.
- Original Message -
From: Jesse Houwing [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, September 18, 2002 12:07 PM
Subject: Re: Returning columns with null
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
interesting...
Unfortnately I don't know how to write a cursor, and there's no way I'm
gonna ask for help on that! But this, and the last offering from Jim, look
like they're somewhat along the lines of what i need.
Thanks - Patti
- Original Message -
From: Joe Eugene [EMAIL
Accept that versions of CF prior to MX don't handle nulls, and so the values
returned as null from SQL Server to CF become empty strings instead with no
way to differentiate them from a column which has a valid empty-string value
vs. a column which should contain data but does not.
As I told
Hey Patti,
Try this stored procedure:
alter procedure sp_fetchNullColumnNames
tablename sysname,
pk sysname,
uid int,
schema sysname = 'dbo'
AS
declare colname sysname;
declare strSQL nvarchar(1000);
create table #c ( column_name sysname);
insert into #c
Don't feel to bad...
Walked into an auto-parts supply store to pick up a socket... Had my head
shaved and several days growth of hair on my face... Walked up to the
counter, girl behind the register looks me dead in the face and says:
Did you find everything you need mam? ...
Funny thing is,
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
nodding Yeah, sounds like it.
Ok, that cinches it. I'm gonna have to bite the bullet and do it the CF
way, I think.
Thanks for the help, though! Appreciate the time..
-Patti
- Original Message -
From: S. Isaac Dealey [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday,
Ooops... should be create procedure ... :)
Hey Patti,
Try this stored procedure:
alter procedure sp_fetchNullColumnNames
tablename sysname,
pk sysname,
uid int,
schema sysname = 'dbo'
AS
declare colname sysname;
declare strSQL nvarchar(1000);
create table
Gee thanks! Ok, I will. I'll have to wait a couple of days, though. While
I'm offsite I don't have access to the SQL server due to the client's rather
anal security.
I've read it through a couple of times and I'm pretty sure I see what's
going on. The only question I have, though it may be
I was once mistaken for a woman by a man - I cried for days and would not be
consoled until I went out and bought 2 pairs of shoes g.
-Original Message-
From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 18, 2002 1:39 PM
To: CF-Talk
Subject: Re: Returning
I've been playing with this method for a while and it's not doing it. It
always returns all of the columns in the case statment (even without the
else). Seemed like a good solution at first, but nope.
Ah well. It was a fighting try it were.
-Patti
- Original Message -
You'd also
Funny thing is, this happens to me perpetually ... not sure why, maybe I
was
a woman in my last life or something. :)
p.s. So what else has changed about your life since the sex change? :)
Laughing quite joyously at thoughts of your past life as the bearded
lady
mock seriousness Thanks
Why do u have to do the below... u can contruct the SQL in CF and just pass
it.
select * from TableName where 1=0
This gives you Query.ColumnList.. now.. all you got to do is loop through
the Query.ColumnList and Dynamically build the CASE Statement for the Query
cfquery ...
declare @fields
Gee thanks! Ok, I will. I'll have to wait a couple of days, though.
While I'm offsite I don't have access to the SQL server due to the
client's rather anal security.
I've read it through a couple of times and I'm pretty sure I see what's
going on. The only question I have, though it may
Why do u have to do the below... u can contruct the SQL in
CF and just pass
it.
select * from TableName where 1=0
This gives you Query.ColumnList.. now.. all you got to do
is loop through
the Query.ColumnList and Dynamically build the CASE
Statement for the Query
cfquery ...
declare
44 matches
Mail list logo
