Home » SQL & PL/SQL » SQL & PL/SQL » help with this query & unwanted records (merged)
help with this query & unwanted records (merged) [message #327744] Tue, 17 June 2008 07:29 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi all

I have the following query and am trying to get distinct contract_numbers.

select 'scoping' calc_type,TRUNC(sysdate-selection_date) days,DISTINCT(contract_number),worktype_code

but for some reason its giving me the following error message.

ORA-00936 missing expression

Cause: A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE.

any advice is greatly appreciated.

Thanks
Re: help with the sql query [message #327746 is a reply to message #327744] Tue, 17 June 2008 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is clear your query is incomplete. You should post the actual one along with table description and full explaination of what you're trying to do.

DISTINCT is on ALL selected expressions and can't be used in only one.

Regards
Michel

Re: help with the sql query [message #327748 is a reply to message #327746] Tue, 17 June 2008 07:48 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
the actual query is

select DISTINCT 'scoping' calc_type,TRUNC(sysdate-selection_date) days,contract_number,worktype_code
from DW_CONTRACT_VW
where AMENDMENT_NUMBER=0
and AMENDMENT_STATUS_CODE IN (1,5,6,7)
and CONTRACT_TYPE !='on call'

and each contract number has multiple worktype_codes, so the contract number is displayed multiple times. All i want is to display the contractnumber only once irrespective of the worktype codes.
Thanks
Re: help with the sql query [message #327749 is a reply to message #327748] Tue, 17 June 2008 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you or not display all worktype?
If not, which one do you want to display?
Is this just a display issue: you don't want to contract_number to be repeated each line or do you want just one line per contract_number?

What is your Oracle version?

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: help with the sql query [message #327753 is a reply to message #327749] Tue, 17 June 2008 08:15 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
I'm using oracle 10 g

Actually this query is to generate the report for this i dont need the worktype code, but since when i run the query i'm getting more records than the original report.......................so when i checked in TOAD i found out that the contract number has been repeated multiple times.All i need is a unique cintractnumber from the table............................worktype code doesnt come into the picture.


thnks
Re: help with the sql query [message #327770 is a reply to message #327753] Tue, 17 June 2008 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm using oracle 10 g

10g what?

Quote:
i dont need the worktype code,

Remove it.
Remove all what you don't need then distinct will work.

Regards
Michel
Re: help with the sql query [message #327777 is a reply to message #327770] Tue, 17 June 2008 09:33 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Michel Cadot wrote on Tue, 17 June 2008 09:03
Quote:
I'm using oracle 10 g

10g what?

Quote:
i dont need the worktype code,

Remove it.
Remove all what you don't need then distinct will work.

Regards
Michel



As Michel said, DISTINCT is on ALL selected expressions and can't be used in only one.

SQL> SELECT * FROM Test;

1          300
2          400
3          300
1          300
1          500

SQL> SELECT SAL, DISTINCT(EMPID) FROM test;
SELECT SAL, DISTINCT(EMPID) FROM test
            *
ERROR at line 1:
ORA-00936: missing expression




SQL> SELECT  DISTINCT(EMPID),SAL FROM test
 

1          300
1          500
2          400
3          300

SQL>  SELECT  DISTINCT EMPID,SAL FROM test;

1          300
1          500
2          400
3          300



Hope you have understood!

[Updated on: Tue, 17 June 2008 09:34]

Report message to a moderator

Re: help with the sql query [message #327781 is a reply to message #327777] Tue, 17 June 2008 09:55 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
thnks
unwanted records [message #327800 is a reply to message #327744] Tue, 17 June 2008 13:06 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi all

Please see the query below

SELECT DISTINCT 'scoping' calc_type, TRUNC ( SYSDATE - selection_date ) days
, contract_number
FROM dw_contract_vw
WHERE amendment_number = 0
AND amendment_status_code IN ( 1, 5, 6, 7 )
AND contract_type != 'on call'


when i run it i get the records containing "contract_type='oncall'" as well
where did i go wrong ? any suggestions please.

thnks
Re: unwanted records [message #327801 is a reply to message #327800] Tue, 17 June 2008 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'oncall' != 'on call'

And once again post your version with 4 decimals and read and FOLLOW OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Tue, 17 June 2008 13:11]

Report message to a moderator

Re: unwanted records [message #327802 is a reply to message #327800] Tue, 17 June 2008 13:12 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

We don't have your table.
We don't have your data.
We don't have your results.

You're On Your Own (YOYO) until we have something to answer with.
Previous Topic: Analytical query to get sum of parts
Next Topic: Queries That Never Complete
Goto Forum:
  


Current Time: Sat Dec 10 01:02:32 CST 2016

Total time taken to generate the page: 0.07711 seconds