Solved

adding new column to sql and check status

Posted on 2013-02-04
9
419 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
  • 4
  • 4
9 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
What table holds the status column?
0
 

Author Comment

by:royjayd
Comment Utility
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:royjayd
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now