return a bit when uniqueidentifier is null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

return a bit when uniqueidentifier is null

Author

Topic

mroll
Starting Member

4 Posts

Posted - 2010-02-10 : 14:24:30

How do I get a boolean variable to be returned when a uniqueidentifier is NULL?

I am selecting values from 2 tables and I want the ID from another if the IDs match up. Here is my select statement. I always get "Yes" in the HasPLS field, even when the PLSId is NULL.
SELECT
A.*,
dbo.User_GetUserFullName(A.CreatedBy) CreatedByFullName,
dbo.User_GetUserFullName(A.ModifiedBy) ModifiedByFullName,
PLS.Id AS PLSId,
case PLS.Id
when null then 'No'
else 'Yes'
end as HasPLS
FROM
dbo.ADSystematicReview A
join dbo.ADCategoryClinicalTopic C
on A.[Id] = C.ClinicalTopicId and
C.CategoryId = @CategoryId
left join dbo.ADPlainLanguageSummary PLS
on PLS.SystematicReviewId=A.Id
Order by
A.SRTitle

Thanks,

Mike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 14:38:14

quote:
Originally posted by mroll

How do I get a boolean variable to be returned when a uniqueidentifier is NULL?

I am selecting values from 2 tables and I want the ID from another if the IDs match up. Here is my select statement. I always get "Yes" in the HasPLS field, even when the PLSId is NULL.
SELECT
A.*,
dbo.User_GetUserFullName(A.CreatedBy) CreatedByFullName,
dbo.User_GetUserFullName(A.ModifiedBy) ModifiedByFullName,
PLS.Id AS PLSId,
cast(case
when PLS.Id is null then 0
else 1
end as bit) as HasPLS
FROM
dbo.ADSystematicReview A
join dbo.ADCategoryClinicalTopic C
on A.[Id] = C.ClinicalTopicId and
C.CategoryId = @CategoryId
left join dbo.ADPlainLanguageSummary PLS
on PLS.SystematicReviewId=A.Id
Order by
A.SRTitle

Thanks,

Mike

modify as above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mroll
Starting Member

4 Posts

Posted - 2010-02-10 : 14:53:32

thanks.
I saw that from a previous post. I knew I was close.
I am still trying to bind that HasPLS and the ID, but I am getting an error.
Any help with this?
<ItemTemplate>
<asp:HyperLink ID="hlCategory2" CssClass="hide" runat="server" Text="Plain Language Summary" Visible='<%#Eval("HasPLS") %>' NavigateUrl='<%# Eval("PLSId", "~/PLS/PlainLanguageSummaryDetail.aspx?plsId={31}&IndexId="+Request.QueryString["IndexId"]) %>' EnableViewState="True" style="float: left" >Plain Language Summary</asp:HyperLink>
</ItemTemplate>

Mike

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-10 : 15:22:19

[code]DECLARE @Sample TABLE
(
i UNIQUEIDENTIFIER
)

INSERT @Sample
SELECT NULL UNION ALL
SELECT NEWID()

SELECT i,
CAST(COALESCE(LEN(i), 0) AS BIT)
FROM @Sample[/code]

N 56°04'39.26"
E 12°55'05.63"

mroll
Starting Member

4 Posts

Posted - 2010-02-10 : 15:43:23

THANKS!
Again!

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources