July 14, 2006 at 10:43 am
I have a new SQL2k box that I am trying to set a database up on.
I am having problems with an sql statement that is behaving differently on this new box to our other two where its ok.
I have two tables, default_text and custom_text, the default tbl is datafilled whilst the custom is not. I am running a statement to return email messages for a client. If they have a record for that email in the custom table it overrides the default email message.
The statement is this
SELECT
HTML = CASE
WHEN c.HTML is not null
THEN c.HTML ELSE d.HTML
End,
Textual = CASE
WHEN c.Textual is not null
THEN c.Textual ELSE d.Textual
End
FROM
tbl_DEFAULT_EMAILS as d
LEFT JOIN
tbl_CUSTOM_EMAILS as c
ON
d.EmailPK = c.EmailFK AND
c.SiteFK = 10000
WHERE
d.EmailPK = 1
which is returning null for both HTML and Textual columns though it should be returning data from the default table as none exists in the custom tbl. The two columns are both ntext.
However if I run this statement.
SELECT
ISNULL(c.HTML, d.HTML) as HTML,
ISNULL(c.Textual, d.Textual) as Textual
FROM
tbl_DEFAULT_EMAILS as d
LEFT JOIN
tbl_CUSTOM_EMAILS as c
ON
d.EmailPK = c.EmailFK AND
c.SiteFK = 10000
WHERE
d.EmailPK = 1
It returns data in both HTML and Textual columns which is coming from the default table.
If I run the first statment on our other box it works fine.
I can only imagine its something to do with something not being set up correctly so far on the new box so that the ntext fields are screwed in regards to is null although I have no idea what.
I can rewrite the code so that it uses the second statment but I would have to change it on our dev and production servers as well. Plus there are lots of other cases where the similar case statements are used but with different tables.
Could someone please explain what is happening and how I can resolve the problem.
Thanks in advance for any help. Rob
July 14, 2006 at 4:01 pm
How did you migrate the data to your new box? It sounds to me like the nText columns for HTML and Textual in your Custom table are not truely NULL. There is nothing wrong with your query, it is unstructing SQL Server to pull Custom.HTML and Custom.Textual if they are not null. Can it be that, instead of being null as you are expecting, the HTML and Textual columns for these rows are blank and not null?
July 17, 2006 at 2:32 am
It can't be that as there is actually no data at all in the custom_emails tables. I have only migrated the default data across to the new system.
If I run select * from tbl_CUSTOM_EMAILS it returns no rows at all, therefore my left join with the default table should return nulls in both the custom.HTML and custom.Textual columns (Which it does - just that the CASE statement isn't recognising it)
There is only data in the default table for that row, so the case statement should return that data as it should only return data for the custom row if it exists else default data.
I don't understand how my isnull version is working yet the CASE statement version isn't as they both do the same thing.
July 17, 2006 at 2:33 am
By the way I used the DTS wizard to import only the tables/data that I required from our dev server to the new server.
July 17, 2006 at 11:37 am
Just to let you know I fixed it.
We installed Service Pack 4 and it fixed it completley.
Thanks for help
July 17, 2006 at 11:46 am
That's great as I was stumped. Anyway, I just wanted to point out an additional method for what you are doing. Check out COALESCE in BOL. It is easier to read than the CASE statement and I think it is the ANSI-preferred way over ISNULL.
SELECT
COALESCE(c.HTML, d.HTML) as HTML,
COALESCE(c.Textual, d.Textual) as Textual
FROM tbl_DEFAULT_EMAILS as d
LEFT JOIN tbl_CUSTOM_EMAILS as c
ON d.EmailPK = c.EmailFK AND c.SiteFK = 10000
WHERE d.EmailPK = 1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply