![]() However, we really appreciate if you test your script before you post it, so that it compiles, run, and actually demonstrates the issue you are having. It's great that you provide CREATE TABLE + INSERT. Select * from CTE where city '' or post '' or address '' or date1 '' or comments '' įirst permit me some comments on the post as such. I am not sure if this is what you want, and if not, keep leaving a message. When A.PerID is null then B.PerID end as ,ĮLSE isnull(CAST(A.Address AS VARCHAR(20)),CAST(B.Address AS VARCHAR(20))) END AS Address,ĮLSE isnull(convert(VARCHAR(20),A.Date1,23),convert(VARCHAR(20),B.Date1,23)) END AS Date1, ![]() If I understand correctly, you can try this query. Or date1 is not null or comments is not null Or post is not null or address is not null When A.Name = B.Name then '' end as Commentsįrom TableA as A FULL outer join TableB as B on A.Name = B.Name) When B.Name is null then 'Missing in Table-B' Select case when A.Name is not null then A.Name else B.Name end as ,Ĭase when A.Code is not null then A.Code else B.Code end as ,Ĭase when A.PerID is not null then A.PerID else B.PerID end as ,ĮLSE isnull(CAST(A.City AS VARCHAR(20)),CAST(B.City AS VARCHAR(20))) END AS City,ĮLSE isnull(CAST(A.Post AS VARCHAR(20)),CAST(B.Post AS VARCHAR(20))) END AS Post,ĮLSE isnull(CAST(A.Address AS VARCHAR(20)),CAST(B.Address AS VARCHAR(20)))ĮLSE isnull(convert(VARCHAR(20),A.Date1,23),convert(VARCHAR(20),B.Date1,23)) I made some modifications, how about this query? with CTE as( When B.Name is null then 'Missing in Table-B' end as Comments / Remaining 3 lines of query in next answer's block*/ Insert into TableB values (‘BBB’, ‘BB’, ‘003’, ‘HH’, ‘1X’, ‘67 Dr’, ‘’)ĬASE WHEN A.City = B.City THEN '' ELSE isnull(CAST(A.City AS VARCHAR(20)), '') END AS City,ĬASE WHEN A.Post = B.Post THEN '' ELSE isnull(CAST(A.Post AS VARCHAR(20)), '') END AS Post,ĬASE WHEN A.Address = B.Address THEN '' ELSE isnull(CAST(A.Address AS VARCHAR(20)), '') END AS Address,ĬASE WHEN A.Date1 = B.Date1 THEN '' ELSE isnull(convert(VARCHAR(20),A.Date1,23), '') END AS Date1,Ĭase when A.Name is null then 'Missing in Table-A' Insert into TableA values (‘AAA’, ‘AA’, ‘003’, ‘HH’, ‘1X’, ‘67 Dr’, ‘’)Ĭreate TableB (Name varchar(20), Code varchar(20),PerID varchar(20), City varchar(20), Post varchar(20), Address varchar(20), Date1 datetime) Currently I am using VARCHAR(20) to convert which want to keep.Ĭreate TableA (Name varchar(20), Code varchar(20),PerID varchar(20), City varchar(20), Post varchar(20), Address varchar(20), Date1 datetime) ![]() Note:I want to keep VARCHAR(20) to convert in varchar with every column in select query. Should show key columns with data and with comments that 'Missing in Table-A' and same if TableA's key column is missing in TableB then it should all the data and with comments that 'Missing in Table-B'Ģ- if Comments has null then it should replace with ‘ ‘ (blank). Note: The key columns are Name, code and PerID which can not be same.ġ- Example: TableB’s key column BBBB-BB-003 is missing in TableA but result is not showing the keys columns, showing NULL instead of key columns. I need help to fix the following two issues in my query to get the desired result. Hi, Please review the SQL query and help.
0 Comments
Leave a Reply. |