February 1, 2014 at 12:10 am
i have TABLEA and TABLEB
Table A:
Create table TableA (ID INT
, A1 DECIMAL(6,5)
, A2 DECIMAL(6,5)
, A3 DECIMAL(6,5)
, A4 DECIMAL(10,9)
, FLAGB1 CHAR(1)
, FLAGB2 CHAR(1))
INSERT INTO TABLEA (ID, A1, A2, A3, A4)
VALUES(1, 1.1111, 2.2222, 3.3333, 4.4444 )
, (2, 1.2345, 2.2345, 3.2345, 4.2345)
,(3, 1.2222, 2.2222, 3.2222, 4.2222)
[/CODE]
TABLE B:
CREATE TABLE TABLEB (ID INT
, B1 DECIMAL(6,5)
, B2 DECIMAL(6,5))
INSERT INTO TABLEB (ID, B1, B2)
VALUES (1, 1.1111, 1.2345)
, (2, 2.2222, 3.3333)
, (3, 5.2222,3.2222)
[/CODE]
LOGIC:
I WOULD LIKE TO COMPARE B1 WITH A1, A2, A3, A4 AND IF THERE IS ANY MATCHING NUMBER THEN PUT 'Y' ON FLAGB1 ELSE PUT 'N'. LIKEWISE, COMPARE B2 WITH A1, A2, A3, A4 AND IF THERE IS ANY MATCHING NUMBER THEN PUT 'Y' ON FLAGB2 ELSE PUT 'N' ON TABLEA.
NOTE: ACTUAL TABLES HAVE THOUSANDS OF ROWS AND ABOUT 60 COLUMNS
OUTPUT:
TABLEA:
ID----A1-----------A2-----------A3----------A4-----FLAGB1----FLAGB2
1---1.11110-----2.22220------3.33330------4.44440-----Y---------N
2---1.23450-----2.23450------3.23450------4.23450-----N---------N
3---1.22220-----2.22220------3.22220------4.22220-----N---------Y
SO FAR I HAVE TRIED
DECLARE @NUM1 decimal(6,5), @NUM2 decimal(6,5)
DECLARE @END INT, @COUNT INT
SET @COUNT= 1
SELECT @END =MAX(ID) FROM TABLEB
WHILE @COUNT <= @END
BEGIN
SELECT @NUM1= B1 FROM TABLEB WHERE ID = @COUNT IF EXISTS (SELECT 1 FROM TABLEA WHERE
@NUM1 IN (A1,A2,A3,A4) AND ID =@count)
UPDATE TABLEA
SET FLAGB1 = 'Y'
where ID = @count
ELSE
UPDATE TABLEA
SET FLAGB1='N'
WHERE ID=@COUNT
SELECT @NUM2= B2 FROM TABLEB WHERE ID = @COUNT
IF EXISTS (SELECT 1 FROM TABLEA WHERE @NUM2 IN (A1,A2,A3,A4) AND ID =@count)
UPDATE TABLEA
SET FLAGB2 = 'Y'
where ID = @count
ELSE
UPDATE TABLEA
SET FLAGB2='N'
WHERE ID=@COUNT
SET @COUNT-@COUNT+1
END
SELECT * FROM TABLEA
I GET RANDOM 'Y'AND 'N' ON FLAG COLUMNS WHICH DOES NOT SATISFY THE LOGIC.
ANY HELP IS APPRICIATED!!
February 1, 2014 at 3:53 am
-- always check the equivalent SELECT is returning an appropriate result set
SELECT a.ID, b1.ID, b2.ID,
FLAGB1 = CASE WHEN b1.ID IS NULL THEN 'N' ELSE 'Y' END,
FLAGB2 = CASE WHEN b2.ID IS NULL THEN 'N' ELSE 'Y' END
FROM TableA a
-- you don't have to join TABLEB twice, you could use OR.
LEFT JOIN TABLEB b1 ON b1.B1 IN (a.A1, a.A2, a.A3, a.A4)
LEFT JOIN TABLEB b2 ON b2.B2 IN (a.A1, a.A2, a.A3, a.A4)
-- before performing the update
UPDATE a SET
FLAGB1 = CASE WHEN b1.ID IS NULL THEN 'N' ELSE 'Y' END,
FLAGB2 = CASE WHEN b2.ID IS NULL THEN 'N' ELSE 'Y' END
FROM TableA a
LEFT JOIN TABLEB b1 ON b1.B1 IN (a.A1, a.A2, a.A3, a.A4)
LEFT JOIN TABLEB b2 ON b2.B2 IN (a.A1, a.A2, a.A3, a.A4)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 1, 2014 at 11:14 am
thank you for the reply. may be i am a newbie which is why i couldnt get your query to work\. i tried to run it but it kept on running and when i stoped the execution it has already generated 200k+ rows with only value 'Y' on flag columns.
but anyways i fixed my query and is working now:-). i missed to refernce table after IN.
correction: select 1 from TABLEA A where ID =@count in (A.A1, A.A2, A.A3, A.A4)
February 1, 2014 at 11:49 am
I took and modified the solution from Chris and came up with this:
update a set
Flagb1 = case when b1 in (A1, A2, A3, A4) then 'Y' else 'N' end,
Flagb2 = case when b2 in (A1, A2, A3, A4) then 'Y' else 'N' end
from TableA a
inner join TABLEB b on b.id = a.ID
select a1, a2, a3, a4, flagb1, flagb2
from TableA
This gave the expected output that you specified.
I did a quick and dirty compare of the execution plans between the cursor you posted and the code above and your cursor takes a whopping 87% of the cpu compared to 13%.
I know you will realize significant improvement in speed by using a set based solution rather than cursors.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 4, 2014 at 8:32 pm
Thank you this is what i was looking for:-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply