Home » SQL & PL/SQL » SQL & PL/SQL » Where Clause in sql
Where Clause in sql [message #338333] Mon, 04 August 2008 11:02 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Hi,

Need a little help on this basic sql statement. I am a little rusty on sql statements these days. I need in the where clause for the sql statement to retriev data where the project number is starting at or greater than '560000'.

Would this be the correct way below?:

select 
  project_number award_number ,
  SUBSTR(project_number, 1, 6) AS project_number, 
  event_date,
  description,
  organization_name,
  bill_amount 
from pa_events_v
where project_number > '560000';


Thanks
Anne

[EDITED by LF: cut long line short]

[Updated on: Mon, 04 August 2008 11:27] by Moderator

Report message to a moderator

Re: Where Clause in sql [message #338335 is a reply to message #338333] Mon, 04 August 2008 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 25035
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

What is datatype of "project_number"?
Re: Where Clause in sql [message #338343 is a reply to message #338333] Mon, 04 August 2008 11:33 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Anne
starting at or greater than '560000'

As far as I understood that, "starting at or greater than" means ">=", not ">", apart from us not knowing the column datatype, as Anacedent had already asked. Why? Because, we normally don't search for substrings in numbers (or we'd use TO_CHAR), as well as - when dealing with characters (just as an example) - 93 is greater than 56000
SQL> select '93' col from dual
  2  union
  3  select '56000' from dual
  4  order by 1;

COL
-----
56000
93

SQL>
while - at the same time - your WHERE clause suggests it IS a number. So ... read and follow ./fa/1597/0/
Re: Where Clause in sql [message #338356 is a reply to message #338343] Mon, 04 August 2008 13:33 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
The project number field/column is varchar2.


Anne
Re: Where Clause in sql [message #338361 is a reply to message #338356] Mon, 04 August 2008 13:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Does it only contain numbers?
Or are there records that have non-numeric data in it?
If it always is a number, the best thing to do is to make it a number.
Re: Where Clause in sql [message #338362 is a reply to message #338356] Mon, 04 August 2008 13:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
to retriev data where the project number is starting at or greater than '560000'.

Greater as a string or as a number? This is the question.

Regards
Michel
Re: Where Clause in sql [message #338369 is a reply to message #338362] Mon, 04 August 2008 14:16 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
The column also has characters and numbers.


Anne
Re: Where Clause in sql [message #338381 is a reply to message #338369] Mon, 04 August 2008 15:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Then let me repeat what other people already asked in different wordings: what do you mean by "greater than 560000"?
Is A1 greater than 560000?
Is 9 greater than 560000?
Is 56000a greater than 560000?

See the problem?
Re: Where Clause in sql [message #338622 is a reply to message #338381] Tue, 05 August 2008 08:39 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
OK,

Let me make it simeple, where project_number is starting at 560000 and above.

The query needs to incorporate this in the where clause, so how would I write that.

Anne
Re: Where Clause in sql [message #338625 is a reply to message #338622] Tue, 05 August 2008 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To make it simple, the problem is that we don't know what you mean with "above".
Just answer Frank's questions.

Regards
Michel
Re: Where Clause in sql [message #338628 is a reply to message #338622] Tue, 05 August 2008 08:59 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
The need for clarification remains the same.
Because project_number is a text item, which can contain both numerical and non numerical characters, you cannot simply convert that column into a number. Now, the value '9' (as a text value) is higher than 560000 and therefore would come out as 'above' 560000. Is that what you want? In addition, please re-read and actually answer Frank's (and all the others') questions. Don't try to make it simple, just answer the questions Wink
Re: Where Clause in sql [message #338631 is a reply to message #338628] Tue, 05 August 2008 09:01 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Is 56000a greater than 560000? Yes.


Anne
Re: Where Clause in sql [message #338634 is a reply to message #338631] Tue, 05 August 2008 09:04 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Also greater as a number.

Anne
Re: Where Clause in sql [message #338635 is a reply to message #338631] Tue, 05 August 2008 09:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, now the other ones.
And this one "Is '56000' greater than '5600a', and what about '5600A'?"

Regards
Michel
Previous Topic: How to find ALL jobs that have run till date ?
Next Topic: Need help in sequence.
Goto Forum:
  


Current Time: Sat Dec 03 20:36:11 CST 2016

Total time taken to generate the page: 0.19218 seconds