Home » SQL & PL/SQL » SQL & PL/SQL » SQL Statement Error
SQL Statement Error [message #231919] Wed, 18 April 2007 19:16 Go to next message
prathimaprasunrao
Messages: 6
Registered: April 2007
Junior Member
Hello everyone,
I have 3 tables.They are DSS_USER, DSS_FIELD_ARCHAEOLOGY and CODE_FIELD_STATUS.

DSS_USER has fields(user_id, crt_by_name,crt_date,updt_by_name,updt_date and account_status_flag)

DSS_FIELD_ARCHAEOLOGY has fields( dhr_id, status_code, crt_by_name, crt_date,updt_by_name, updt_date)

CODE_FIELD_STATUS has fields( status_code, item,crt_by_name, crt_date, updt_by_name,updt_date)

If Status_code is 1 item = In Progress
If Status_Code is 2 item =Submitted
If Status_Code is 3, item =Reviewed
If Status_Code is 4, item = Obsolete
Account_status_flag has 2 values =(Inactive and Active)

The goal is to retrieve all the user_id’s whose account_status_flag is Inactive and item is either “In Progress or Submitted or Reviewed or Obsolete)

For this goal, I have written the following sql statements but I am not getting the correct result.
1. SELECT DISTINCT(a.user_id),
c.item
FROM
DSS_USER a, DSS_FIELD_ARCHAEOLOGY b,CODE_FIELD_STATUS c
WHERE a.account_status_flag ='I'AND b.status_code=c.status_code AND c.item = 'In Progress'
UNION
SELECT DISTINCT(a.user_id),
c.item
FROM
DSS_USER a, DSS_FIELD_ARCHAEOLOGY b,CODE_FIELD_STATUS c
WHERE a.account_status_flag ='I'AND b.status_code=c.status_code AND c.item = 'Reviewed'
UNION
SELECT DISTINCT(a.user_id),
c.item
FROM
DSS_USER a, DSS_FIELD_ARCHAEOLOGY b,CODE_FIELD_STATUS c
WHERE a.account_status_flag ='I'AND b.status_code=c.status_code AND c.item = 'Submitted' ;

2. SELECT a.user_id,a.ACCOUNT_STATUS_FLAG,
b.dhr_id,b.crt_by_name,b.crt_date,b.updt_by_name,b.updt_date,
c.item
FROM
DSS_USER a, DSS_FIELD_ARCHAEOLOGY b,CODE_FIELD_STATUS c
WHERE b.status_code=c.status_code AND ( c.item ='In Progress' or c.item ='Reviewed' or c.item ='Submitted') AND a.account_status_flag ='I';

Could any one tell me what is wrong in the sql statement.
Waiting for reply.
Prathima
Re: SQL Statement Error [message #231920 is a reply to message #231919] Wed, 18 April 2007 19:21 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>Could any one tell me what is wrong in the sql statement.
You should read & FOLLOW the #1 STICKY post regarding the use of "code tags".
>Could any one tell me what is wrong in the sql statement.
I don't see anything wrong.
What do you see wrong?
Re: SQL Statement Error [message #231923 is a reply to message #231920] Wed, 18 April 2007 20:08 Go to previous messageGo to next message
prathimaprasunrao
Messages: 6
Registered: April 2007
Junior Member
when i execute that query, each record is being displayed three times.
For example, I have a record "1234".The output from that sql statement appears to be as follows:
1234 In Progress
1234 Submitted
1234 Reviewed
1234 Obsolete
even though 1234 has status In Progress.
Re: SQL Statement Error [message #231933 is a reply to message #231919] Wed, 18 April 2007 21:01 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
SELECT COUNT(*) FROM DSS_FIELD_ARCHAEOLOGY ;
--produces what result?
Re: SQL Statement Error [message #231983 is a reply to message #231933] Thu, 19 April 2007 00:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Read about carthesian product.

[Edit:] Hm, apparently searching for that does not (easily) reveal your problem.

Hint: Did you join all your tables in your second query?

[Updated on: Thu, 19 April 2007 00:32]

Report message to a moderator

Re: SQL Statement Error [message #232051 is a reply to message #231919] Thu, 19 April 2007 03:57 Go to previous messageGo to next message
prathimaprasunrao
Messages: 6
Registered: April 2007
Junior Member
Select Count(*) from dss_field_archaeology gives the count of the total number of rows in that table.
Re: SQL Statement Error [message #232102 is a reply to message #232051] Thu, 19 April 2007 07:45 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
prathimaprasunrao wrote on Thu, 19 April 2007 04:57
Select Count(*) from dss_field_archaeology gives the count of the total number of rows in that table.


Dead Dead Dead
Previous Topic: can anyone help me in solving this query
Next Topic: How to generate Excel File through Pl/sql Procedure
Goto Forum:
  


Current Time: Mon Dec 05 15:11:21 CST 2016

Total time taken to generate the page: 0.25837 seconds