Solved

adding new column to sql and check status

Posted on 2013-02-04
9
422 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)
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 76

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
 
LVL 76

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
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
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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parametric query in oracle 6 52
query in Oracle forms Builder 2 42
Concat multi row values of a field in oracle 6 56
Consolidating oracle query results to a single line 8 53
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

18 Experts available now in Live!

Get 1:1 Help Now