Solved

adding new column to sql and check status

Posted on 2013-02-04
9
427 Views
Last Modified: 2013-02-04
hi guys

I have two tables : employee and customer

employee table has
Id salary
1  100
2  300
5  600

customer table has
Id salary
1   100
2   500
5   800

My requirment is :
I have to check if salary of employee table matchees with salary of customer table for a given id, If they dont match create a new column 'Status' with value false. If it matches then its True.

Right now i have

select id, salary from employee
where employee.id =  ?  

union all

select id, salary from customer
where customer.id =  ?


How can i come up with the extra column to compare  salary of employee with salary of customer for a given id.

Any help will be greatly appreciated
thanks
0
Comment
Question by:royjayd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38851715
What table holds the status column?
0
 

Author Comment

by:royjayd
ID: 38851729
There is no table which holds the status column, I need to come up with the
new  'status' column in my sql.

In my above sql i need to check if
employee.salary = customer.salary then populate status to 'true'
else populate status to 'false'.


thanks.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 38851730
Also, what happens if you have an emp or cust row that isn't in the other table?

Given what you posted, see if this get's you started.  I just created a results table until you provide more details.

drop table my_emp purge;
drop table my_cust purge;
drop table my_results purge;

create table my_emp(id number, salary number);
create table my_cust(id number, salary number);
create table my_results(id number, status varchar2(10));


insert into my_emp values(1,100);
insert into my_emp values(2,300);
insert into my_emp values(3,600);

insert into my_cust values(1,100);
insert into my_cust values(2,500);
insert into my_cust values(3,800);
commit;


insert into my_results
select e.id, case when e.salary != c.salary then 'false' else 'true' end status
from my_emp e, my_cust c
where e.id=c.id
/


select * from my_results;

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38851732
>>I need to come up with the  new  'status' column in my sql.

Then just remove the 'insert into' from my example above.

select e.id, case when e.salary != c.salary then 'false' else 'true' end status
from my_emp e, my_cust c
where e.id=c.id
/
0
 

Author Comment

by:royjayd
ID: 38852067
when i run

select e.id, case when e.salary != c.salary then 'false' else 'true' end status
from my_emp e, my_cust c
where e.id=c.id
/

I am getting error:
"column ambiguously defined"

am i missing anything

thx
0
 
LVL 32

Expert Comment

by:awking00
ID: 38852132
Are you using the my_emp and my_cust tables that slightwv created or your own tables? If the latter, can you provide a description of them?
0
 

Author Comment

by:royjayd
ID: 38852176
hi,
these are my tables

employee table has
Id salary
1  100
2  300
5  600

customer table has
Id salary
1   100
2   500
5   800

I dont have a status column, i need my sql to return 'status' with true or false

i am trying to do

select e.id, case when e.salary != c.salary then 'false' else 'true' end status
from my_emp e, my_cust c
where e.id=c.id
/

or

select e.id, case when e.salary != c.salary then 'false' else 'true' AS status
from my_emp e, my_cust c
where e.id=c.id
/

but no luck

getting
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38852183
I tested what I posted against 10.2.0.3.

I assume you changed my table names to match your table names?

What is different between my test tables and your actual tables?
0
 

Author Comment

by:royjayd
ID: 38852268
sorry, it was a silly typo, got it now thanks

any help with next question greatly appreciated

http://www.experts-exchange.com/Database/Oracle/Q_28019544.html
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question