April 14, 2015 at 9:25 am
Hi all,
i have a query needs to look for 5 records data in a table. Basically i need to hardcode. Below is my query which didnt work out
select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD,BF_BDOB_CD,BF_TM_PERD_CD) in ***** i guess this is the wrong query****
('A1', 'B1', 'C1')
('A2', 'B2', 'C2')
('A3', 'B3', 'C3')
('A4', 'B4', 'C4')
('A5', 'B5', 'C5')
but if i use the query below it will generate more records than these 5 records
select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA
WHERE (BF_ORGN_CD) in ('A1', 'A2', 'A3', 'A4', 'A5')
and (BF_BDOB_CD) in ('B1', 'B2', 'B3', 'B4', 'B5')
and (BF_TM_PERD_CD) in ('C1', 'C2', 'C3', 'C4', 'C5')
hope you can understand and share with me the right solution.
thanks.
April 14, 2015 at 9:38 am
You need to specify the 5 records:
select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD, data
from BF_DATA
WHERE (BF_ORGN_CD='A1' AND BF_BDOB_CD='B1' AND BF_TM_PERD_CD='C1')
OR (BF_ORGN_CD='A2' AND BF_BDOB_CD='B2' AND BF_TM_PERD_CD='C2')
OR (BF_ORGN_CD='A3' AND BF_BDOB_CD='B3' AND BF_TM_PERD_CD='C3')
OR (BF_ORGN_CD='A4' AND BF_BDOB_CD='B4' AND BF_TM_PERD_CD='C4')
OR (BF_ORGN_CD='A5' AND BF_BDOB_CD='B5' AND BF_TM_PERD_CD='C5')
April 14, 2015 at 9:56 am
Maybe a join to reduce repetition of comparisons:
SELECT bf.BF_ORGN_CD, bf.BF_BDOB_CD, bf.BF_TM_PERD_CD, bf.data
FROM BF_DATA bf
INNER JOIN (
VALUES('A1', 'B1', 'C1'),
('A2', 'B2', 'C2'),
('A3', 'B3', 'C3'),
('A4', 'B4', 'C3'),
('A5', 'B5', 'C5')
) AS values_to_match(BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD) ON
values_to_match.BF_ORGN_CD = bf.BF_ORGN_CD AND
values_to_match.BF_BDOB_CD = bf.BF_BDOB_CD AND
values_to_match.BF_TM_PERD_CD = bf.BF_TM_PERD_CD
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 14, 2015 at 10:09 am
Thank you very much.
It really worked out. I really appreciate of your help.
sincerely,
Kenny
April 14, 2015 at 10:13 am
This is another option.
WITH Conditions AS(
SELECT ORGN, BDOB, TM_PERD
FROM (VALUES('A1', 'B1', 'C1'),
('A2', 'B2', 'C2'),
('A3', 'B3', 'C3'),
('A4', 'B4', 'C4'),
('A5', 'B5', 'C5'))x(ORGN, BDOB, TM_PERD)
)
select BF_ORGN_CD, BF_BDOB_CD, BF_TM_PERD_CD,data
from BF_DATA d
WHERE EXISTS(SELECT 1
FROM Conditions c
WHERE d.BF_ORGN_CD = c.ORGN
AND d.BF_BDOB_CD = c.BDOB
AND d.BF_TM_PERD_CD = c.TM_PERD)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply