Loading...

Using select query for IN Clause, along with hardcoded values


I have some code where I am selecting rows that have any of the following hardcoded SETID values. This is part of additional code being rolled into a SQL View.

SELECT *
FROM TEST_TABLE1 B
WHERE B.SETID IN ('11000', '18000', '41000', '41001')

In addition to the above hardcoded values, I would like to add in another much longer list of SETID's between the range '90000' and '90999' that are contained in a table PS_LOC_GL. I would prefer to use the BETWEEN operator, so that I don't have to list out each value in this range. I haven't been able to find a method to select both the hardcoded values, and use a BETWEEN operator together.

This is what I have come up with so far, however I don't believe I can use variables inside SQL views, so while this works as native SQL code, it will not work inside my view:

DECLARE @SET_ID_LIST table (SETID varchar(5))
INSERT @SET_ID_LIST(SETID) VALUES ('11000'), ('18000'), ('41000'), ('41001');

SELECT *
FROM TEST_TABLE1 B
WHERE B.SETID IN (
    SELECT SETID FROM @SET_ID_LIST 
    UNION
    SELECT SETID FROM PS_LOC_GL
)

Are there any other options I have where I can use both the hardcoded list of values, along with more of a dynamic list based on the PS_LOC_GL table that only has SETID values in the '90000' to '90999' range.

- - Source

Answers

answered 1 week ago Salman A #1

You can simply use a OR clause:

SELECT *
FROM TEST_TABLE1 B
WHERE B.SETID IN (SELECT SETID FROM PS_LOC_GL /* WHERE SETID BETWEEN ... */)
OR    B.SETID IN ('11000', '18000', '41000', '41001')

If you want to use UNION then use the (VALUES ...) clause to append the hardcoded list of values:

SELECT *
FROM TEST_TABLE1 B
WHERE B.SETID IN (
    SELECT SETID FROM PS_LOC_GL /* WHERE SETID BETWEEN ... */
    UNION
    SELECT SETID FROM (VALUES
        ('11000'),
        ('18000'),
        ('41000'),
        ('41001')
    ) AS v(SETID)
)

answered 1 week ago sticky bit #2

Why don't you just OR the other condition? IN actually is a shortcut for multiple ORs, so just append to it.

SELECT *
       FROM test_table1 b
       WHERE b.setid IN ('11000', '18000', '41000', '41001')
              OR b.setid IN (SELECT setid
                                    FROM ps_loc_gl);

Or with a BETWEEN operation something like:

SELECT *
       FROM test_table1 b
       WHERE b.setid IN ('11000', '18000', '41000', '41001')
              OR b.setid BETWEEN (SELECT min(setid)
                                  FROM ps_loc_gl)
                                 AND (SELECT max(setid)
                                             FROM ps_loc_gl);

comments powered by Disqus