How to use concatenated fields in criteria

Last Post 13 Mar 2009 02:21 AM by SwePeso. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
tinmainiac
New Member
New Member

--
04 Mar 2009 11:23 AM
I am trying to use concatenated fields in my WHERE clause as criteria for a sub query

select fld1,fld2 from tbl1
where fld1+fld2 in (select fld1+fld2 from tbl2)

is this possible with T-Sql? If so any advice would be greatly appreciated.

g8r
New Member
New Member

--
04 Mar 2009 11:52 AM
It will work, but you'd be better off using an inner join between the tables on the two fields.
tinmainiac
New Member
New Member

--
04 Mar 2009 12:58 PM
Thanks for the response, I had thought that the answer to my initail question would solve my issue, however...

This is the full sql statement I am trying to run

UPDATE PS_TL_TR_STATUS
SET TA_STATUS = 'Y', EARLIEST_CHGDT = '02/16/2009'
WHERE CONVERT(VARCHAR(7),EMPLID)+CONVERT(VARCHAR(1),EMPL_RCD) IN
( SELECT CONVERT(VARCHAR(7),B.EMPLID)+CONVERT(VARCHAR(1),B.EMPL_RCD) FROM PS_TL_GROUP_DTL B
WHERE B.GROUP_ID = 'TAALL')
AND CONVERT(VARCHAR(7),EMPLID)+CONVERT(VARCHAR(1),EMPL_RCD) IN
( SELECT CONVERT(VARCHAR(7),C.EMPLID)+CONVERT(VARCHAR(1),C.EMPL_RCD) FROM PS_TL_EMPL_DATA C
WHERE C.TIME_RPTG_STATUS = 'A'
AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_TL_EMPL_DATA C1
WHERE C1.EMPLID = C.EMPLID
AND C1.EMPL_RCD = C.EMPL_RCD
AND C1.EFFDT <= GETDATE()))

Initially sql server would not concatenate the EMPLID and EMPL_RCD fields as they are different datatypes. This particular statement sums the goal of what I am trying to accomplish however it takes too long to process and I am thinking that an easier method has to exist.

I have to concatenate the EMPLID and EMPL_RCD fields because the empl_rcd field determines an emplid with multiple jobs in my system and I only want to update where the emloyees job is specific to my criteria.
SwePeso
New Member
New Member

--
13 Mar 2009 02:21 AM
quote:

Originally posted by: g8r
It will work, but you'd be better off using an inner join between the tables on the two fields.


Beware of duplicate records when INNER JOIN!

Try EXISTS instead

select t1.fld1, t1.fld2 from tbl1 as t1
where exists ( select * from tbl2 as t2 where t2.fld1 = t1.fld1 and t2.fld2 = t1.fld2)
You are not authorized to post a reply.

Acceptable Use Policy