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
Solved

adding new column to sql and check status

Posted on 2013-02-04
9
425 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 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
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.

 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Help on model clause 5 37
Email query results in HTML 6 31
update statement in oracle 9 29
Toad 12.10 Enterprise visual interface 4 22
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

839 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