September 17, 2014 at 1:22 pm
Customer_id = 192618941 Doesn't exist in customer table
I m using this query
Select distinct DECODE (d.customer_id, NULL, 'NO', 'YES') EXIST ,e.customer_id from customer e , customer d
where e.customer_id = d.customer_id(+) and e.customer_id in ('192609905','192618941');
But I m getting output
Exist Customer_id
YES192609905
I want output like this
Exist Customer_id
YES192609905
NO 192618941
September 17, 2014 at 1:39 pm
selpoivre (9/17/2014)
Customer_id = 192618941 Doesn't exist in customer tableI m using this query
Select distinct DECODE (d.customer_id, NULL, 'NO', 'YES') EXIST ,e.customer_id from customer e , customer d
where e.customer_id = d.customer_id(+) and e.customer_id in ('192609905','192618941');
But I m getting output
Exist Customer_id
YES192609905
I want output like this
Exist Customer_id
YES192609905
NO 192618941
Are you sure you are using sql server? This is a sql server site. There is no DECODE function is sql server. There is one in Oracle though. In sql server we have the case expression.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2014 at 1:53 pm
Yea its in oracle but you can give solution accordingly
September 17, 2014 at 2:41 pm
I see the issue. The problem is your are using ANSI-89 style joins instead of ANSI-92 style joins. Your join condition causes this to be an inner join and you need a left join.
Not sure about the Oracle syntax but in SQL Server it would be like this.
Select case when d.customer_id IS NULL then 'NO' else 'YES' end as EXIST
, e.customer_id
from customer e
left join customer d on e.customer_id = d.customer_id
where e.customer_id in ('192609905','192618941');
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply