CODEWITHSUNDEEP
sqlsql-servercoldfusion
mac3201
mac3201

Reputation: 85

can I use a variable for the integer expression in a left sql function

I have the following query:

    SELECT top 2500 *
    FROM  table a
    LEFT JOIN table b
    ON a.employee_id = b.employee_id
    WHERE left(a.employee_rc,6) IN 
           (
             SELECT employeeID, access 
             FROM   accesslist 
             WHERE  employeeID = '#client.id#' 
           )

The sub select in the where clause can return one or several access values, ex:

In the primary where clause I need to be able to change the integer expression from 6 to 5 or 4 or 7 depending on what the length of the values returned in the sub select. I am at a loss if this is the right way to go about it. I have tried using OR statements but it really slows down the query.

Upvotes: 0

Views: 194

Answers (3)

Dan Bracuk
Dan Bracuk

Reputation: 20804

This construct will enable you to do what you said you want to do, have an integer value depend on somethign from a subquery. It's the general idea only, the details are up to you.

select field1, field2
, case when subqueryField1 = 'fred' then 1
when subqueryField1 = 'barney' then 2
else 3 end integerValue

from table1 t1 join (
select idField subqueryField1, etc
from whereever ) t2 on t1.idFeld = t2.idField
where whatever

Also, a couple of things in your query are questionable. First, a top n query without an order by clause doesn't tell the database what records to return. Second, 2500 rows is a lot of data to return to ColdFusion. Are you sure you need it all? Third, selecting * instead of just the fields you need slows down performance. If you think you need every field, think again. Since the employee ids will always match, you don't need both of them.

Upvotes: 0

jcaron
jcaron

Reputation: 17710

Move the subquery to a JOIN:

SELECT TOP 2500 *
    FROM table a
        LEFT JOIN table b ON a.employee_id = b.employee_id
        LEFT JOIN accesslist al ON al.access LIKE concat('%', a.employee_id)
    WHERE al.employeeID = '#client.id#'

Like Gordon, I don't quite see how your query worked, so I'm not quite sure if it should be access or employeeID which is matched.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Try using exists instead:

   SELECT top 2500 *
   FROM table a LEFT JOIN
        table b
        ON a.employee_id = b.employee_id
   WHERE EXISTS (Select 1
                 FROM accesslist
                 WHERE employeeID = '#client.id#' and
                       a.employee_rc like concat(employeeID, '%') 
                ) ;

I don't see how your original query worked. The subquery is returning two columns and that normally isn't allowed in SQL for an in.

Upvotes: 2

Related Questions