Having Problems with a Simple Query

Hey guys,
I am trying to run a query that will return all rows where a column is
completely empty of data.
I try this query:
select * from patients_visitInsurers
where CompanyID is NULL
But it does not return anything for me. If I try the reverse
select * from patients_visitInsurers
where CompanyID is not NULL
It returns rows that contain CompanyId values and rows that are blank.
What am I missing here? Thanks!
Do you have those blank values for CompanyID being NULL or just blank
string? Assuming CompanyID is character format, you can check:
SELECT <columns>
FROM patients_visitInsurers
WHERE COALESCE(CompanyID, '') = ''
Or
SELECT <columns>
FROM patients_visitInsurers
WHERE CompanyID IS NULL
OR CompanyID = ''
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||Is CompanyID a string? "Empty of data" and "blank" are two different
things, in my opinion. Empty of data means NULL (and you have the correct
syntax, of that were the case). Blank means an empty string, e.g. ''. In
which case,
WHERE CompanyID = ''
You may also be safer to trim the data first, since it could contain a
space...
WHERE RTRIM(CompanyID) = ''
However, you should not insert a blank string when you really meant NULL.
They are different in implementation, and they are different on a conceptual
level, as well.
<alvinstraight38@.hotmail.com> wrote in message
news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
> Hey guys,
> I am trying to run a query that will return all rows where a column is
> completely empty of data.
> I try this query:
> select * from patients_visitInsurers
> where CompanyID is NULL
> But it does not return anything for me. If I try the reverse
> select * from patients_visitInsurers
> where CompanyID is not NULL
> It returns rows that contain CompanyId values and rows that are blank.
> What am I missing here? Thanks!
|||Plamen Ratchev was thinking very hard :
> Do you have those blank values for CompanyID being NULL or just blank string?
> Assuming CompanyID is character format, you can check:
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE COALESCE(CompanyID, '') = ''
> Or
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE CompanyID IS NULL
> OR CompanyID = ''
>
and if that doesn't get it, then
OR LTRIM(RTRIM(CompanyID)) = ''
HTH,
Brad.
|||On Apr 4, 11:07Xam, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Is CompanyID a string? X"Empty of data" and "blank" are two different
> things, in my opinion. XEmpty of data means NULL (and you have the correct
> syntax, of that were the case). XBlank means an empty string, e.g. ''. XIn
> which case,
> WHERE CompanyID = ''
> You may also be safer to trim the data first, since it could contain a
> space...
> WHERE RTRIM(CompanyID) = ''
> However, you should not insert a blank string when you really meant NULL.
> They are different in implementation, and they are different on a conceptual
> level, as well.
> <alvinstraigh...@.hotmail.com> wrote in message
> news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
>
>
>
>
>
> - Show quoted text -
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows, but I want to exclude rows
that do contain values in this field.
Thanks!
|||>>
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows,[vbcol=seagreen]
Right. So you need to have two concepts very clear:
NULL is the absence of any data whatsoever.
'' is an empty string. It is data, even if it is zero-length.
There is little point in setting this column to NOT NULL if you can stick an
empty string in there. In this specific situation, it is apparent that the
two concepts are interchangeable...

Having Problems with a Simple Query

Hey guys,
I am trying to run a query that will return all rows where a column is
completely empty of data.
I try this query:
select * from patients_visitInsurers
where CompanyID is NULL
But it does not return anything for me. If I try the reverse
select * from patients_visitInsurers
where CompanyID is not NULL
It returns rows that contain CompanyId values and rows that are blank.
What am I missing here? Thanks!Do you have those blank values for CompanyID being NULL or just blank
string? Assuming CompanyID is character format, you can check:
SELECT <columns>
FROM patients_visitInsurers
WHERE COALESCE(CompanyID, '') = ''
Or
SELECT <columns>
FROM patients_visitInsurers
WHERE CompanyID IS NULL
OR CompanyID = ''
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Is CompanyID a string? "Empty of data" and "blank" are two different
things, in my opinion. Empty of data means NULL (and you have the correct
syntax, of that were the case). Blank means an empty string, e.g. ''. In
which case,
WHERE CompanyID = ''
You may also be safer to trim the data first, since it could contain a
space...
WHERE RTRIM(CompanyID) = ''
However, you should not insert a blank string when you really meant NULL.
They are different in implementation, and they are different on a conceptual
level, as well.
<alvinstraight38@.hotmail.com> wrote in message
news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
> Hey guys,
> I am trying to run a query that will return all rows where a column is
> completely empty of data.
> I try this query:
> select * from patients_visitInsurers
> where CompanyID is NULL
> But it does not return anything for me. If I try the reverse
> select * from patients_visitInsurers
> where CompanyID is not NULL
> It returns rows that contain CompanyId values and rows that are blank.
> What am I missing here? Thanks!|||Plamen Ratchev was thinking very hard :
> Do you have those blank values for CompanyID being NULL or just blank string?
> Assuming CompanyID is character format, you can check:
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE COALESCE(CompanyID, '') = ''
> Or
> SELECT <columns>
> FROM patients_visitInsurers
> WHERE CompanyID IS NULL
> OR CompanyID = ''
>
and if that doesn't get it, then
OR LTRIM(RTRIM(CompanyID)) = ''
HTH,
Brad.|||On Apr 4, 11:07=A0am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Is CompanyID a string? =A0"Empty of data" and "blank" are two different
> things, in my opinion. =A0Empty of data means NULL (and you have the corre=ct
> syntax, of that were the case). =A0Blank means an empty string, e.g. ''. ==A0In
> which case,
> WHERE CompanyID =3D ''
> You may also be safer to trim the data first, since it could contain a
> space...
> WHERE RTRIM(CompanyID) =3D ''
> However, you should not insert a blank string when you really meant NULL.
> They are different in implementation, and they are different on a conceptu=al
> level, as well.
> <alvinstraigh...@.hotmail.com> wrote in message
> news:40bfbcdf-5531-4350-9889-dee8af80b160@.d1g2000hsg.googlegroups.com...
>
> > Hey guys,
> > I am trying to run a query that will return all rows where a column is
> > completely empty of data.
> > I try this query:
> > select * from patients_visitInsurers
> > where CompanyID is =A0NULL
> > But it does not return anything for me. =A0If I try the reverse
> > select * from patients_visitInsurers
> > where CompanyID is =A0not NULL
> > It returns rows that contain CompanyId values and rows that are blank.
> > What am I missing here? =A0 Thanks!- Hide quoted text -
> - Show quoted text -
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows, but I want to exclude rows
that do contain values in this field.
Thanks!|||>>
I think that is where I am getting confused. I looked at the data
type and it says varchar and not null. If I look at the database,
nothing shows in the column for some rows,
Right. So you need to have two concepts very clear:
NULL is the absence of any data whatsoever.
'' is an empty string. It is data, even if it is zero-length.
There is little point in setting this column to NOT NULL if you can stick an
empty string in there. In this specific situation, it is apparent that the
two concepts are interchangeable...sql

Having a MAJOR brain fart here...

Guys I'm sorry to be asking such a routine question...

I'm having trouble figuring out how to make this function dynamic enough to handle multiple insert statements.

1public int Add()23{45string SQL;67SQL ="INSERT INTO [BuildingInterior] (PropertyID, CeilingHeight, " +89"LoadingDocks, PassengerElevators, FreightElevators, PassengerEscalators, " +1011"FireSprinklersID, SecurityCameras, SmokeDetection, FireAlarms, " +1213"GasDetection, SecureAccess, HeatTypeID, AirConditioningID, " +1415"AirExchange, InternetAccessID, InteriorDescription) " +1617"VALUES ( @.PropertyID, @.CeilingHeight, " +1819"@.LoadingDocks, @.PassengerElevators, @.FreightElevators, @.PassengerEscalators, " +2021"@.FireSprinklersID, @.SecurityCameras, @.SmokeDetection, @.FireAlarms, " +2223"@.GasDetection, @.SecureAccess, @.HeatTypeID, @.AirConditioningID, " +2425"@.AirExchange, @.InternetAccessID, @.InteriorDescription)";2627PropertyDB myConnection =new PropertyDB();2829SqlConnection conn = myConnection.GetOpenConnection();3031SqlCommand cmd =new SqlCommand(SQL, conn);3233cmd.Parameters.Add("@.PropertyID", SqlDbType.Int).Value = PropertyID;3435cmd.Parameters.Add("@.CeilingHeight", SqlDbType.NVarChar, 50).Value = CeilingHeight;3637cmd.Parameters.Add("@.LoadingDocks", SqlDbType.NVarChar, 50).Value = LoadingDocks;3839cmd.Parameters.Add("@.PassengerElevators", SqlDbType.NVarChar, 50).Value = PassengerElevators;4041cmd.Parameters.Add("@.FreightElevators", SqlDbType.NVarChar, 50).Value = FreightElevators;4243cmd.Parameters.Add("@.PassengerEscalators", SqlDbType.NVarChar, 50).Value = PassengerEscalators;4445cmd.Parameters.Add("@.FireSprinklersID", SqlDbType.Int).Value = FireSprinklersID;4647cmd.Parameters.Add("@.SecurityCameras", SqlDbType.NVarChar, 50).Value = SecurityCameras;4849cmd.Parameters.Add("@.SecurityAlarms", SqlDbType.NVarChar, 50).Value = SecurityAlarms;5051cmd.Parameters.Add("@.SmokeDetection", SqlDbType.NVarChar, 50).Value = SmokeDetection;5253cmd.Parameters.Add("@.FireAlarms", SqlDbType.NVarChar, 50).Value = FireAlarms;5455cmd.Parameters.Add("@.GasDetection", SqlDbType.NVarChar, 50).Value = GasDetection;5657cmd.Parameters.Add("@.SecureAccess", SqlDbType.NVarChar, 50).Value = SecureAccess;5859cmd.Parameters.Add("@.HeatTypeID", SqlDbType.Int).Value = HeatTypeID;6061cmd.Parameters.Add("@.AirConditioningID", SqlDbType.Int).Value = AirConditioningID;6263cmd.Parameters.Add("@.AirExchange", SqlDbType.NVarChar, 50).Value = AirExchange;6465cmd.Parameters.Add("@.InternetAccessID", SqlDbType.Int).Value = InternetAccessID;6667cmd.Parameters.Add("@.InteriorDescription", SqlDbType.NVarChar, 50).Value = InteriorDescription;6869cmd.ExecuteNonQuery();7071cmd.CommandText ="SELECT @.@.IDENTITY";7273this.BuildingInteriorID = Int32.Parse(cmd.ExecuteScalar().ToString());7475conn.Close();7677return this.BuildingInteriorID;7879}80

Should I just pass an array of column names and use the AddWithValues SqlCommand method while looping through the array?

Any comments are greatly welcomed.

Hi eterry,

As far as I can see there is no better way to assign value by iterating through each column like this.

But I think there is one thing that you can improve in your code. You can put SELECT SCOPE_IDENTITY() (use SCOPE_IDENTITY() instead of @.@.IDENTITY in SQL Server)at the end of the INSERT statement. Seperate them with a ";", like

INSERT INTO ......; SELECT SCOPE_IDENTITY()

Then you can use ExecuteScalar to have the 2 statement executed at once. This will have 2 advantages.

1. Save a roundtrip to the server and gain better performance.
2. Prevent the concurrency issues. In your code, if 2 users do this together, there is possibility that they will get wrong identity if one's execution is interrupted by the other.

HTH. If this does not answer you question, please feel free to mark the post as Not Answered and post your reply. Thanks!

|||

Thanks for the tip Kevin.

I was hoping that there would be a better way of doing, but it is what it is.

having a contractor develop SSIS packages and security issues

Hi Guys,

I will have a constractor for 3 months developing ssis packages for me.

Obviously, his Windows account will be deleted when he leaves.

My question his. under which securoty context should he develop packages so they will still work and still be accessible after his departure. Assuming we do not want to use any password to either have his packages running or have his packages accessible by other developers from the development environment.

Most of all we do not want ANY job failure due to "encryption issues" after his departure

Thanks

Philippe

In a shared enviornment, I like to use package passwords myself. I use a password in development and when we had them off to a client or to production support, they assign a new password to the package. dtutil.exe can do this quickly for you if you generate a batch file.

This may help you to with that type of strategy if you're interested:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/19/34.aspx

-- Brian Knight

|||

If you store your packages to SQL Server, than you can use roles to grant and deny access to the packages. This is also a great approach if you wish to automatically execute packages because the integrations with Agent, the DTS Subsystem, and proxy accounts is all seamless.

I have an article here about SSIS security :

http://www.windowsitpro.com/Article/ArticleID/46723/46723.html

K

have elements even when column has NULL value

Hi guys,
Sorry if posted twice as I could not see the previous post...
I need to capture element name even when the column it is associate to has a NULL value. I can best describe this with an example...
Say for example we have 2 tables names tblProduct and tblCategory with following columns and relation...
tblProduct
ProductId int (PK)
ProductName varchar(30)
CategoryId int (FK to tblCategory)
tblCategory
CategoryId (PK)
CategoryName varchar(30)
table data...
tblProduct
ProductId ProductName CategoryId
123 testProduct1 777
345 testProduct2 NULL
678 testProdyct3 888
tblCategory
CategoruId CategoryName
777 testCategory1
888 testCategory9
999 testCategory44
Now I need to have XML output as...
<Products><Product><ProductId>123</ProductId><ProductName>testProduct1</ProductName><CategoryName>testCategory1</CategoryName></Product><Product><ProductId>345</ProductId><ProductName>testProduct2</ProductName><CategoryName></CategoryName></Product><Produ
ct>
...
...
</Product></Products>
In the above case, CategoryName element should be captured even when the product has null value for it.
So, you have an schema xsd as...
<?xml version="1.0" encoding="UTF-8"?><xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"><xsd:annotation><xsd:appinfo><sql:relation ship name="ProductCategory"
parent="tblProduct"
parent-key="CategoryId"
child="tblCategory"
child-key="CategoryId" /></xsd:appinfo></xsd:annotation><xsd:element name="Products" sql:is-constant="1"><xsd:complexType><xsd:sequence maxOccurs="unbounded"><xsd:element ref="Product"/></xsd:sequence></xsd:complexType></xsd:element><xsd:element name="P
roduct" type="ProductDetails"
sql:relation="tblProduct"
sql:key-field="ProductId"/><xsd:complexType name="ProductDetails"><xsd:sequence><xsd:element name="ProductId"
type="xsd:integer"
sql:field="ProductId"/><xsd:element name="ProductName"
type="xsd:string"
sql:field="ProductName"/><xsd:element name="CategoryName"
type="xsd:string"
sql:field="CategoryName"
sql:relation="tblCategory"
sql:relationship="ProductCategory"/></xsd:sequence></xsd:complexType></xsd:schema>
The problem is that you get below output when CategoryId is null for some of the products
<Products><Product><ProductId>123</ProductId><ProductName>testProduct1</ProductName><CategoryName>testCategory1</CategoryName></Product><Product><ProductId>345</ProductId><ProductName>testProduct2</ProductName>
***** error ***** does not get captured
</Product><Product>
...
...
</Product></Products>
I would apprecaite if anyone has a solution for this.
Thanks
-Sid
"sid" <anonymous@.discussions.microsoft.com> wrote in message
news:FBC3236C-9C0E-49E1-971D-4E7FD4F638DF@.microsoft.com...
> Hi guys,
> Sorry if posted twice as I could not see the previous post...
> I need to capture element name even when the column it is associate to has
> a NULL value. I can best describe this with an example...
See this FAQ:
http://sqlxml.org/faqs.aspx?faq=14
Bryant

Hardware issues for database mirroring

Hi guys, can I know for mirroring, is it both principal and mirror server's hardware spec needed to be same in order to best performance during mirroring? I am just curious about it. Thx.

Best Regards,

Hans

NO, they don't have to be

Although you might want them to be, as the mirror server should be capable enough to be your primary server when needed

Plus, you can set them up identically, or try different settings to see how performance changes

It's likd RAID, you don't necessarily need same brand, same model, same size HDDs, but people always recommends it

|||Also, depending on the version of SQL2005, only the enterprise edition supports "high-performance" in asynchronous mode for mirroring. The standard edition only supports synchronous - so a gigabit or fibre channel is recommended. The speed between servers should also be considered for best mirroring performance, depending on your situation.

Handling a SQL Exceptions and Custom Error Messages

Hello guys,

I need some ideas on how to handle an exception or a user defined error message.

I have a procedure that creates a new user. Lets say if the e-mail address entered is already in use. What are some of the best practices for notifying the user that the e-mail address is already in use?

This is what I was thinking...

Solution #1
-----
My proc will raise an error with a message id that is great than 50000, then my DAL will recognize this is a user defined error and spit back to the user instead of trapping it.

Solution #2
-----
The proc should have an output param ( @.CreationStatus CHAR(1) ).
If the @.CreationStatus has a value for example "E", I will have lookup the value for "E" in my app and spit back that custom error message. I don't really like this option because it is too concrete.

What are some of the ways you deal with this situation?

Your suggestions are greatly appreciated.

Thank you!

You could return a @.status value with (0=success, 1= failure and an appropriate status message @.Statusmsg ( = 'Success' if @.status = 0, custom error message if @.status = 1)

From your application you could check the value in @.status and if its not 0, then display the message from @.statusmsg. You can handle this in a number of ways, It comes down to setting up one standard way of doing it across all procs and communicating with your team and documenting it so the same logic is followed across all procs.

Handeling NULL variable

Good afternoon,

I'd like to like how you guys handle a situation like the one I'm going to describe in just a moment. I'm pretty sure I'm not handeling it in the best way, so I'll just try to explain the situation and learn something from the gurus.

Let's imagin I have the following tables:

[Companies]Id [Uniqueidentifier] [PK]Alias [NVarChar]Status [Bit][Campanies_JobTitles]Id [PK]CompanyId [Uniqueidentifier]Alias [NVarChar]Status [Bit]

If I want to know the job titles of company 1 I could easely set the following stored procedure:

SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1AND JT.CompanyId = @.CompanyIdORDER BY JT.AliasASC;

Now, if the user wants to list the job titles across all the companies I could pass a special value and identify that, as follows:

IF (@.CompanyIdISNOT NULL)BEGIN SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1AND JT.CompanyId = @.CompanyIdORDER BY JT.AliasASC;ENDELSE BEGIN SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1ORDER BY JT.AliasASC;END

This works fine. I just find it a bit counter productive to copy paste the query and wrap it into an if statement. So, the bottom line is: how can I do this in a more "professional" way?

Best regards.

Hi,

What I believe, the query is good enough because at certain point you have to check if the company Id exist or not so that if exist you can search it by the id, if not then show all.

Thanks and best regards,

|||

farazsk11:

Hi,

What I believe, the query is good enough because at certain point you have to check if the company Id exist or not so that if exist you can search it by the id, if not then show all.

Thanks and best regards,

I understand what you are saying, although that check was not on the posted code for the sake of simplicity. But, that wouldn't answer the underlying question that is: how can I "reuse" the query so I don't have to repeat it thoughout the procedure? Is there a function that can solve this like ISNULL allows you to handle the values that return as null.

|||

What about this:

SELECTJT.IdAS Id,JT.AliasAS AliasFROM Companies_JobTitles JT
WHERE JT.Status = 1
AND JT.CompanyId =ISNULL(@.CompanyId,JT.CompanyId)ORDER BY JT.Alias

|||

limno:

What about this:

SELECTJT.IdAS Id,JT.AliasAS AliasFROM Companies_JobTitles JT
WHERE JT.Status = 1
AND JT.CompanyId =ISNULL(@.CompanyId,JT.CompanyId)ORDER BY JT.Alias

That was exactly what I was looking for. I didn't know ISNULL could be used in that context.

Thanks a lot limno :)