CODEWITHSUNDEEP
t-sql
John Waclawski
John Waclawski

Reputation: 996

Using count without aggregate

I have a checkbox in our application that can have 1 of 3 options:

Check/Uncheck/Nothing Checked

If the "Use Customer..." box is checked, the box with the arrow pointing to it greys out & the value is deleted from the database. If it's checked, "True" If it's not checked but "Use Customer..." is not checked, "False" If "Use Customer..." is checked, there is no record.

My issues is pulling the SQL for it. I can pull the TRUE/FALSE value with no issues. I'm stuck trying to get the t-sql to return SOMETHING if the record is blank.

Currently I have this that works fine:

CASE when cb.tvalue.value('declare namespace y="http://schemas.xyzpdg.com/framework/resources";y:Item[1]', 'varchar(25)') = 'True' THEN 'Checked'
        when cb.tvalue.value('declare namespace y="http://schemas.xyzpdg.com/framework/resources";y:Item[1]', 'varchar(25)') = 'False' THEN 'Unchecked'
    END AS [CheckboxValue]--

I've tried using:

When COUNT(cb.tvalue.value('declare namespace y="http://schemas.xyzpdg.com/framework/resources";y:Item[1]', 'varchar(25)')) = 0 then 'Use Customer...'

But when I use that I of course get a the infamous ..."invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." error. And I can't use XML as a GROUP BY.

Any ideas? Thank you.

Upvotes: 0

Views: 53

Answers (1)

i-one
i-one

Reputation: 5120

If I understand correctly, it seems that either you may use exist xml type method or simply else branch of the case statement, see sample:

declare @xml xml;
--set @xml = '<Test><Item>False</Item></Test>';
--set @xml = '<Test><Item>True</Item></Test>';
set @xml = '<Test />';

select
    case
        when @xml.value('(/Test/Item)[1]', 'varchar(25)') = 'True' then 'Checked'
        when @xml.value('(/Test/Item)[1]', 'varchar(25)') = 'False' then 'Unchecked'
        when @xml.exist('(/Test/Item)[1]') = 0 then 'Use Customer...'
    end [CheckboxValue];

select
    case
        when @xml.value('(/Test/Item)[1]', 'varchar(25)') = 'True' then 'Checked'
        when @xml.value('(/Test/Item)[1]', 'varchar(25)') = 'False' then 'Unchecked'
        else 'Use Customer...'
    end [CheckboxValue];

Upvotes: 1

Related Questions