July 1, 2016 at 5:57 am
Dear Experts,
I was trying to get rid of null values when using full outer join between two tables. For example, I have tables like below:
Table A Table B
Eno Ename Eno Ename
1 A 1 A
2 B 2 D
3 C 3 E
my output should be like below
A.Eno A.ENAME B.ENAME STATUS
1 A A SAME
2 B D DIFF
3 C E DIFF
kindly provide your inputs in achieving this.
Thanks in advance
Warm Regards,
Vj
July 1, 2016 at 6:49 am
vijay.sap1255 (7/1/2016)
Dear Experts,I was trying to get rid of null values when using full outer join between two tables. For example, I have tables like below:
Table A Table B
Eno Ename Eno Ename
1 A 1 A
2 B 2 D
3 C 3 E
my output should be like below
A.Eno A.ENAME B.ENAME STATUS
1 A A SAME
2 B D DIFF
3 C E DIFF
kindly provide your inputs in achieving this.
Thanks in advance
Warm Regards,
Vj
Why are you getting NULLs? What code are you using?
Can you post your sample data this way?
CREATE TABLE TableA(
Eno int,
Ename varchar(10)
);
INSERT INTO TableA
VALUES
(1, 'A'),
(2, 'B'),
(3, 'C');
July 1, 2016 at 7:12 am
You're getting a NULL because the row doesn't exist in the other table. You'll probably want a different status for that. Try something like this
Status = CASE
WHEN TableA.Eno IS NULL THEN 'Doesn''t exist in TableA'
WHEN TableB.Eno IS NULL THEN 'Doesn''t exist in TableB'
WHEN TableA.Ename = TableB.Ename THEN 'Same'
WHEN TableA.Ename <> TableB.Ename THEN 'Different'
END
It would have been easier if you'd posted your query (and sample data as requested by Luis) so I didn't have to guess.
John
July 1, 2016 at 2:41 pm
What column(s) are you using for your Full Outer Join?
Given the sample data below, it seems obvious that the join should be on the [Eno] column.
A join on [Eno] only will not produce nulls, but a join that includes [Ename] will.
Remember, your join is on elements that are alike. You are then comparing other columns that could be different.
TableA
1 A
2 B
3 C
Table B
1 A
2 D
3 E
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 4, 2016 at 5:10 am
HI ,
U may use this coding Also .
select
a.emp_id , a.emp_name name_1 , b.emp_name name_2 ,
STAT = ( case REPLICATE(a.emp_name,2) when a.emp_name+b.emp_name then 'SAME' else 'DIFF' end)
from #tab1 a join #tab2 b on a.emp_id = b.emp_id
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy