Solved

adding new column to sql and check status

Posted on 2013-02-04
9
430 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
Technology Partners: 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

707 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