variable query in MSSQL (title likely misleading)

[Homerboy]

I have and IN statement that is used in about 25+ (and growing) different queries.

The IN statement contains about 125 different codes (and growing)

When I need to add or delete codes from this IN statement, I'd like to just update the list in ONE spot, not in 25+ different queries.

Is there a way to do that?

I've tried to "cheat" and create a table with e single column row that has all my codes in there:

'ABC', 'DEF', 'GHI'....'XYZ', etc (set to varchar(MAX)

and then in my queries do something like:

WHERE ACTIVE.CODE IN (SELECT * FROM CODE_TABLE)

But that doesn't seem to work.
Anyone have any suggestions?
I probably explained it horribly.

 

[KLin]

lol that isn't going to work. Add each value as a separate row and do (SELECT code from code_table) in the subquery. Or join on the table like brandonb suggests.

 

[brandonb]

Is there any reason why you wouldn't want to inner join to this new code table and use the relational database as a relational database? This is about as noob as you can get, and you are asking questions about it. If you don't get this, you need to sit down with a book or read a webpage that describes simple relational database concepts. If you just try to wing it, you are going to end up doing alot of stupid things. And if you are doing this for a job, you really need to understand, otherwise you will have huge problems down the road. I don't mean to come off sounding like a prick. I'm trying to say this as friendly and "fatherly" as I can. I've seen alot of your posts over the months, and I have a feeling you just haven't done enough studying.

select *
from active
inner join code_table on active.code = code_table.code

(have 1 column and as many rows as you have codes)

As far as answering your question. Just think about it and wonder why its not working. It will come to you eventually.

hint: What if this code table contained this single row/column as: "hello my name is bob"

Do you think its going to find all codes "hello", "my", "name", "is", "bob" or, will it find the code "hello my name is bob"

Now put your value in there, and tell me what its going to do.

 

[Markbnj]

What Brandon said, but just to round things off you could also put the test in a function.

 

[Homerboy]

quite honestly I don't think I explained the problem very well. I assure you I know what I am doing (but I will admit also that I do learn things as I go... but don't we all?)

Brandon, I fully understand relational databases and the like.

 

[GeekDrew]

No, I think we understood exactly what you're trying to do.

Right now it sounds like you're doing something like this:

SELECT active.code
FROM active
WHERE active.code IN ('abc', 'def', 'ghi', 'jkl')

And you tried this, but it didn't work:

SELECT active.code
FROM active
WHERE active.code IN ( SELECT * FROM CODE_TABLE )

What you should actually do is one of these, per the above suggestions.

Both suggestions require the following output from code_table:

SELECT code FROM code_table
----------------------------
'abc'
'def'
'ghi'
'jkl'
'mno'
(5 rows returned - you'd insert one row for each code)

Solution #1 (uses a subquery):

SELECT active.code
FROM active
WHERE active.code IN (SELECT code FROM code_table)

Solution #2 (uses joining):

SELECT active.code
FROM active
INNER JOIN code_table ON active.code = code_table.code

 

[Homerboy]

I worked it out

SELECT
CAST(CAST(MONTH(MASTER.DATE_RECD) AS varchar(2)) +'/01/' +  CAST(YEAR(MASTER.DATE_RECD) AS varchar(4)) AS DATE) AS 'Date',
COUNT(CASE WHEN ACTIVE.CODE IN ('SKIP1', 'SKIP2','SKIP3','SKIP4','SKIP5','SKIP7', 'SKIPP') THEN ACTIVE.FILENO END) AS 'SKIPS',
COUNT(CASE WHEN LEFT(ACTIVE.CODE,1) = 'L' OR ACTIVE.CODE IN ('PLT', 'PPOB-ADD', 'PLP100') THEN ACTIVE.FILENO END) AS 'Letters',
COUNT(DISTINCT CASE WHEN ACTIVE.CODE IN('SVPOE','XVPOE') THEN ACTIVE.FILENO END) AS 'SVPOE',
COUNT(DISTINCT CASE WHEN ACTIVE.CODE ='SCONTACT' THEN ACTIVE.FILENO END) AS 'SCONTACT',
COUNT(DISTINCT CASE WHEN ACTIVE.CODE IN ('PGARN', 'PGARNNE') THEN ACTIVE.FILENO END) AS 'GARNs',
COUNT(DISTINCT CASE WHEN ACTIVE.CODE ='SFDRECD' THEN ACTIVE.FILENO END) AS 'SFDRECD',
COUNT(DISTINCT CASE WHEN ACTIVE.CODE ='SOI' THEN ACTIVE.FILENO END) AS 'SOI',
COUNT(DISTINCT CASE WHEN ACTIVE.CODE ='XOTAAPR' THEN ACTIVE.FILENO END) AS 'XOTAAPR',
COUNT(DISTINCT CASE WHEN ACTIVE.CODE ='PCPT' THEN ACTIVE.FILENO END) AS 'PCPT',
COUNT(DISTINCT CASE WHEN ACTIVE.CODE ='XCPTAPR' THEN ACTIVE.FILENO END) AS 'XCPTAPR',
COUNT(DISTINCT CASE WHEN ACTIVE.CODE ='PBA' THEN ACTIVE.FILENO END) AS 'PBA'

FROM
MASTER INNER JOIN ACTIVE ON 
MASTER.FILENO = ACTIVE.FILENO
WHERE MASTER.FORW_NO IN(29) AND ACTIVE.CODE IN (SELECT CODES FROM PERFORMANCE_REPORT_CODES)
AND(DATEDIFF(MONTH, GETDATE(), MASTER.DATE_RECD) BETWEEN -24 AND -1)
GROUP BY CAST(CAST(MONTH(MASTER.DATE_RECD) AS varchar(2)) +'/01/' +  CAST(YEAR(MASTER.DATE_RECD) AS varchar(4)) AS DATE)

As I said, I don't think I explained the FULL problem at hand and how it was being used (in fact this is 1 part of a larger CTE)

Besides some logic issues, I also had two different data types between the ACTIVE.CODE (varchar(8)) and the PERFORMANCE_REPORT_CODES.CODES (nvarchar(8)) which was an oversight.