Find Fields that are True

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Find Fields that are True

Find Fields that are True

Rate Topic

Display Mode

Topic Options

Author

Message

terrygefael

Posted Saturday, January 05, 2013 10:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 10:14 AM
Points: 3, Visits: 15

Hi, Folks! Need help.
I have a simple table with 29 fields(unique id, 14 True/False, 14 text)
I want to find all fields relating to one unique ID that are true.

Example:
SELECT *
FROM table
WHERE ID = 1
(But then I want the fields that are True)
Out of 14 fields, 4 might be true and the rest are false.
I want to create a list of True fields.

Any ideas?
Thanks in advance.
Terry

Post #1403246

ChrisM@home

Posted Sunday, January 06, 2013 12:44 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 921, Visits: 3,815

terrygefael (1/5/2013)

Lets say Column2, Column3, Column4 and Column5 all have values of "True" for ID IN (1,27,42). Your SELECT list might be unique id, Column2, Column3, Column4 and Column5, 14 text. What happens for ID IN (2,3,4,5) which might have values of "True" in Column3 and Column7? The SELECT list will be different: unique id, Column3, Column7, 14 text. The SELECT lists are not compatible.
There is a way of getting around this; concatenate all of the "True" values into a new column. You'd have to include the column name too.

If you're retrieving the result for a single ID then it's fairly straightforward, using "CROSS APPLY VALUES". Post the ddl for the table so someone can construct the query for you.

^{Low-hanging fruit picker and defender of the moggies}

For better assistance in answering your questions, please read this.

Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Post #1403309

terrygefael

Posted Monday, January 07, 2013 8:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 10:14 AM
Points: 3, Visits: 15

The text fields are irrelevant at this stage as they are only going to be used if a field is designated as True. So, I thought about using a Case statement in my SQL, but am getting a "Missing Operator" error message. I should point out that the SQL is being used in a web page, if that makes a difference.
Here is what I came up with:
SELECT ID,
case when FB_chk = -1 Then ' FB_chk=True' else '' end
+ case when TW_chk = -1 Then ' TW_chk=True' else '' end
+ case when PI_chk = -1 Then ' PI_chk=True' else '' end
+ case when FR_chk = -1 Then ' FR_chk=True' else '' end
+ case when RS_chk = -1 Then ' RS_chk=True' else '' end
+ case when FS_chk = -1 Then ' FS_chk=True' else '' end
+ case when YT_chk = -1 Then ' YT_chk=True' else '' end
+ case when DS_chk = -1 Then ' DS_chk=True' else '' end
+ case when MU_chk = -1 Then ' MU_chk=True' else '' end
+ case when LN_chk = -1 Then ' LN_chk=True' else '' end
+ case when TB_chk = -1 Then ' TB_chk=True' else '' end
+ case when FF_chk = -1 Then ' FF_chk=True' else '' end
+ case when SU_chk = -1 Then ' SU_chk-True' else '' end
+ case when PC_chk = -1 Then ' PC_chk=True' else '' end
+ case when IG_chk = -1 Then ' IG_chk=True' else '' end
+ case when RD_chk = -1 Then ' RD_chk=True' else '' end
+ case when EM_chk = -1 Then ' EM_chk=True' else '' end as Result
FROM tbl_links
WHERE ID = 1
AND (FB_chk = -1 OR TW_chk = -1 OR PI_chk = -1 OR FR_chk = -1
OR RS_chk = -1 OR FS_chk = - 1 OR YT_chk = -1 OR DS_chk = -1
OR MU_chk = -1 OR LN_chk = - 1 OR TB_chk = -1 OR FF_chk = -1
OR SU_chk = -1 OR PC_chk = -1 OR IG_chk = -1 OR RD_chk = -1
OR EM_chk = -1)
But when I run it, I get a "Missing Operator" error.
So, again, I am stuck. Someone out there knows a fix to my dilemma.
Thanks in advance for thoughts, help.
Terry

Post #1403662

ChrisM@Work

Posted Monday, January 07, 2013 9:22 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

It looks ok to me, Terry. Try this:

SELECT 
	ID,
	Result = 
		case when FB_chk = -1 Then ' FB_chk=True' else '' end
		+ case when TW_chk = -1 Then ' TW_chk=True' else '' end
		+ case when PI_chk = -1 Then ' PI_chk=True' else '' end
		+ case when FR_chk = -1 Then ' FR_chk=True' else '' end
		+ case when RS_chk = -1 Then ' RS_chk=True' else '' end
		+ case when FS_chk = -1 Then ' FS_chk=True' else '' end
		+ case when YT_chk = -1 Then ' YT_chk=True' else '' end
		+ case when DS_chk = -1 Then ' DS_chk=True' else '' end
		+ case when MU_chk = -1 Then ' MU_chk=True' else '' end
		+ case when LN_chk = -1 Then ' LN_chk=True' else '' end
		+ case when TB_chk = -1 Then ' TB_chk=True' else '' end
		+ case when FF_chk = -1 Then ' FF_chk=True' else '' end
		+ case when SU_chk = -1 Then ' SU_chk-True' else '' end
		+ case when PC_chk = -1 Then ' PC_chk=True' else '' end
		+ case when IG_chk = -1 Then ' IG_chk=True' else '' end
		+ case when RD_chk = -1 Then ' RD_chk=True' else '' end
		+ case when EM_chk = -1 Then ' EM_chk=True' else '' end  
FROM tbl_links
WHERE ID = 1
	AND -1 IN (FB_chk, TW_chk, PI_chk, FR_chk, RS_chk, FS_chk, YT_chk, DS_chk, 
		MU_chk, LN_chk, TB_chk, FF_chk, SU_chk, PC_chk, IG_chk, RD_chk, EM_chk)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1403699

CELKO

Posted Monday, January 07, 2013 11:04 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

I have a simple table with 29 fields [sic: columns are not fields] (unique id, 14 True/False, 14 text). I want to find all fields [sic] relating to one unique ID that are true.

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. All you did was give us a narrative and a generic query!

You do not yet understand that we do not use assembly language bit flags in SQL; this is a declarative predicate driven language. We discover via predicates if some fact is TRUE, FALSE or UNKNOWN. We do not store it

The fact that you did not know how a column is totally different from a field was very telling.

I want to create a list of TRUE fields [sic].

What does that mean? Column names are meta data, so we do not query them; we query their values. The same column and a predicate can test TRUE, FALSE or UNKNOWN for some predicate in the various rows of the table.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1403761

terrygefael

Posted Monday, January 07, 2013 6:22 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 10:14 AM
Points: 3, Visits: 15

Celko,
I definitely appreciate your taking the time to respond to my post.
The forum name is "SQL Server Newbies". I had assumed maybe that would be taken into account.
While I will do my best to follow forum etiquette,
I have no clue what you are talking about. What is DDL? And the various ISO spec's are greek to me.
Properly formed code: The code I offered up is directly from my web page. How much more formed does it need to be?
Maybe we need a forum here for SQL xxx, I don't know, what's less informed than a newbie?
-- Frustrated in a "SQL newbie forum"...

Post #1403940

Lynn Pettis

Posted Monday, January 07, 2013 8:37 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 21,832, Visits: 27,858

terrygefael (1/7/2013)

First, I know it's hard (especially for me) but you need to ignore Mr. Celko. It isn't the message but the messenger and how he presents the message that is the problem.

To help you, DDL means Data Definition Language. This is your CREATE TABLE, CREATE PROCEDURE, etc type statements.

You should take the time to read the first article I reference below in my signature block. It will walk you through what you need to post and how to do it.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1403950

« Prev Topic | Next Topic »

Permissions