Home » SQL & PL/SQL » SQL & PL/SQL » AND AND AND (Oracle 9i)
AND AND AND [message #345928] Fri, 05 September 2008 05:55 Go to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
HI

Is there a shorter way to say..

Select * from Table
where value='value'
AND color='blue'
AND shape='round'
AND small='no'
AND valid='yes'

I want to caut out all the AND's. Is it posible?
Re: AND AND AND [message #345929 is a reply to message #345928] Fri, 05 September 2008 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use IN.

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) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: AND AND AND [message #345941 is a reply to message #345928] Fri, 05 September 2008 06:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Deon Smit wrote on Fri, 05 September 2008 12:55
HI

Is there a shorter way to say..

Select * from Table
where value='value'
AND color='blue'
AND shape='round'
AND small='no'
AND valid='yes'

I want to caut out all the AND's. Is it posible?


Actually, for a simple example like yours, this is the way to code it.
Why do you want to get rid of the ANDs?
Re: AND AND AND [message #345953 is a reply to message #345929] Fri, 05 September 2008 07:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 05 September 2008 12:57
Use IN.

Here? ./fa/1600/0/
Re: AND AND AND [message #345954 is a reply to message #345928] Fri, 05 September 2008 07:04 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi,

Try the following code:

    SELECT *
    FROM   TABLE
    WHERE  (Value, Color, Shape, Small, Valid) = (('value', 'blue', 'round', 'no', 'yes'))
   


I advice you to use the code with Simple AND conditional statements because it makes your query much more understandable then the one I have provided.

Regards,
Jo
Re: AND AND AND [message #345964 is a reply to message #345954] Fri, 05 September 2008 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@joicejohn

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: AND AND AND [message #345965 is a reply to message #345928] Fri, 05 September 2008 07:31 Go to previous messageGo to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
I just thought there can be shorter way.

Thanks everyone
Re: AND AND AND [message #345967 is a reply to message #345953] Fri, 05 September 2008 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Littlefoot wrote on Fri, 05 September 2008 14:04
Michel Cadot wrote on Fri, 05 September 2008 12:57
Use IN.

Here? ./fa/1600/0/

Same answer as joicejohn but with "IN" instead of "=" (just to give a way to think about list of values).

Regards
Michel

Re: AND AND AND [message #345970 is a reply to message #345928] Fri, 05 September 2008 07:35 Go to previous messageGo to next message
anuragsv
Messages: 5
Registered: September 2008
Junior Member

There is no specific way to cut short it If u will use IN ALSO
that will only replace AND Nothing else.So no specific Way.

Re: AND AND AND [message #345974 is a reply to message #345967] Fri, 05 September 2008 07:43 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 05 September 2008 14:31

Same answer as joicejohn but with "IN" instead of "=" (just to give a way to think about list of values).

./fa/3518/0/ I see; my first thought was that you didn't check OP's query carefully enough and suggested something like
Select * from Table 
where value = 'value'
AND color in ('blue', 'round', 'no', 'yes')    --> whoa!
Stupid me.

Sorry, sorry, sorry.
Re: AND AND AND [message #345988 is a reply to message #345964] Fri, 05 September 2008 08:23 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Michel Cadot wrote on Fri, 05 September 2008 18:00
@joicejohn

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel



http://smileys.on-my-web.com/repository/Confused/stupid.gif

Sorry Michel. I was expecting this from one of the moderators after I posted the reply. I wanted to change it into pure syntax but the idea came only after I posted my reply. Too bad one doesn't have the option to delete his own reply..

Regards,
Jo
Re: AND AND AND [message #346091 is a reply to message #345928] Fri, 05 September 2008 20:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I like this post, it has the potential to cause people to think a little. Hmmm...

where value='value'
AND color='blue' 
AND shape='round'
AND small='no'
AND valid='yes'

vs.

where value='value' AND color='blue' AND shape='round' AND small='no' AND valid='yes'

vs.

where  (Value, Color, Shape, Small, Valid) = (('value', 'blue', 'round', 'no', 'yes'))


Which is simpler? Maybe that is the wrong question. Though these are all semantically equivelant, it might be worth asking, how each alternative changes your perspective on the problem and how it is being answered. In some respects, the joicejohn version is the best of the three.

Possibly, if you think the joicejohn alternative is harder than the others to contemplate, then I would suggest that you are being lead astray by the simplicity of the example. If this were a snippet from a much larger, more complex query involving many table joins and possibly other more advanced SQL constructs, you would soon learn the value of this expressive form for the way it reduces a many column join into a single thought.

Indeed, I would be interested as what makes others think #1 is better that #2 or #3.

Just another opinion. Kevin

[Updated on: Fri, 05 September 2008 20:19]

Report message to a moderator

Re: AND AND AND [message #346100 is a reply to message #346091] Sat, 06 September 2008 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree with you that if you think SQL and all what is around as mathematical, more as "relational", the latter form, explaining the relation with tuples, is the best one.
But how many developers think rdbms application development in mathematical way? Very few, in this case explaining the relational in the usual common way with AND is easier to understand, even if it implies to successively add conditions to get the final point in mind (instead of getting it in one shot in the case of tuple expression).

I prefer the #3 but never use it when I know others will read it. How many times will you get the question: "why there are 2 levels of parenthesis?" (and this is a reason I also prefer using IN in this case, even if it is a IN with one (list) value).

Regards
Michel
Re: AND AND AND [message #346150 is a reply to message #345928] Sat, 06 September 2008 10:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hehehe I always get that question, along with the typical "gee I didn't know you could do that!" response.

But then again, I get variations of the same responses to every feature of the database I use these days. Let us examine a few:

select x.*
from table(cast(:<collection_variable> as c_emp)) x
/

Quote:
"what is that table(cast(?"
"gee I didn't know you could do that!"

select e.*,(select max(effective_date) from project_assignments b where b.emp_id = e.emp_id) last_effective_date
from emp e
/

Quote:
"what is that thing after the e.*?"
"gee I didn't know you could do that!"

select e.*,sum(salary) over (partition by e.dept_id) rolling_salary
from emp e
/

Quote:
"what is that rolling_summary?"
"gee I didn't know you could do that!"

with
   last_status as (
                    select emp_id,status_id,max(seq) seq
                    from hist_status
                    group by emp_id,status_id
                  )
select e.* ls.status_id,ls.seq
from emp e
    ,last_status ls
wher ls.emp_id = e.emp_id
/

"what is that WITH?"
"gee I didn't know you could do that!"

And we could go on for some time. The point being of course that I have had it to my eyeballs with people saying

Quote:
you shouldn't do that because not everybody knows what it is

That is what a manual is for, to learn from. That is what a mentor is for, to learn from. That is what OraFAQ is for, to learn from. I have become more cynical in my old age I guess for though I have great tolerance for those who need me to teach them new tricks, I have less and less tolerance for those among them who express no desire and thus put out litte effort on their side to learn these tricks.

This is of course the age-old discussion about the benefits of simple coding style. But many are confused about what exatly is simple. My observation on the matter is that most lazy bums think like this:

Quote:
If I don't know it, you shouldn't use it because its complicated.

I say bull...

Quote:
If you don't know how to use Oracle Objects effectively
If you don't know how to use Singleton Selects effectively
If you don't know now to use Oracle Analytics effectively
If you don't know how to use the WITH clause effectively
If you don't know how to use (put any of 100 great Oracle/SQL features here) effectively

Then you need to either ask for help or better yet do what a real professional would do (what everybody here does on a regular basis) and get you nose into some web articles and start reading about it. I am dizzyed by the percentage of developers who ask me what some Oracle error message means. They can figure out to call me, but they can't figure out to log on to the internet and look it up along with several of its possible solutions. Geesh...

I subscribe to this very simple belief:

Quote:
Baby SQL is not better SQL, its just baby SQL.

Indeed, it is easy to determine if a feature is a simplifying feature or not:

Quote:
does it reduce code bloat?
does it allow for the expression of a thought in a concise manner?
does it allow you to organize your SQL into a ordered progression of logic?
does it promote thinking about a solution in terms of transformation of datasets or does it promote the slow by slow mentality?

A simplifying piece of code will always do one or usually more than one of these things. I am sure other people can add to the list.

Anyways, I blab on. For those who made it to the end, thanks for doing the reading. Kevin
Re: AND AND AND [message #346161 is a reply to message #346150] Sat, 06 September 2008 14:12 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Entertaining reading. However, when I first read this:

Quote:

I like this post, it has the potential to cause people to think a little. Hmmm...

where value='value'
AND color='blue'
AND shape='round'
AND small='no'
AND valid='yes'



my first thoughts were "big blue round" ... "balloon"? Just going off on a tangent unrelated to computers and thinking in a less esoteric, more simplistic mode at the time. Still wondering what we're describing here, although I know it's just an example.




Previous Topic: DDL statement in Procedure
Next Topic: All table_names, row_count and size
Goto Forum:
  


Current Time: Fri Dec 09 10:01:13 CST 2016

Total time taken to generate the page: 0.13957 seconds