Home » SQL & PL/SQL » SQL & PL/SQL » an SQL query
an SQL query [message #442275] Sat, 06 February 2010 07:13 Go to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
Hi

What is the use of below logic in where clause :

select max(emplid) into a from employees where 1=2;

I run it and can interpret result, since 1<>2, query will return no rows.

But still i want to know where such logic can be applied and useful also ?

Regards
-navin
Re: an SQL query [message #442281 is a reply to message #442275] Sat, 06 February 2010 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select empno from emp where 1=2;

no rows selected

SQL> select max(empno) from emp where 1=2;
MAX(EMPNO)
----------


1 row selected.

Quote:
I run it and can interpret result, since 1<>2, query will return no rows.

So you didn't run it.
Now it is REALLY run, do you so see?

Regards
Michel
Re: an SQL query [message #442299 is a reply to message #442275] Sat, 06 February 2010 11:49 Go to previous messageGo to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
Hi Michel

Thanks for your prompt reply. With the output, i have more doubts now.

Why the output shows 'No rows selected' in first case and '1 row seelected' when we used max(empno)?

Also where can such logic( where 1=2 or where 1=1) be applied in any application ?

Regards
-navin
Re: an SQL query [message #442300 is a reply to message #442299] Sat, 06 February 2010 12:10 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Also where can such logic( where 1=2 or
CREATE TABLE NO_ROWS AS SELECT * FROM TEMPLATE_TBL WHERE 1 = 2;
Used to create an empty table with same columns & datatypes.


>where 1=1) be applied in any application ?
Above used when building dynamic SELECT so that additional filters can be appended to SQL such as "AND NOW = SYSDATE-1"


WHERE 1 = 1 AND NOW = SYSDATE-1
Re: an SQL query [message #442301 is a reply to message #442299] Sat, 06 February 2010 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why the output shows 'No rows selected' in first case and '1 row seelected' when we used max(empno)?

This is how aggregate functions work:

Database SQL Reference
Chapter 5 Functions
Section SQL Functions
Paragraph Aggregate Functions

Quote:
All aggregate functions except COUNT(*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.


Quote:
Also where can such logic( where 1=2 or where 1=1) be applied in any application ?

Bad programming.

Regards
Michel
Re: an SQL query [message #442384 is a reply to message #442301] Sun, 07 February 2010 09:27 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel
Quote:
Also where can such logic( where 1=2 or where 1=1) be applied in any application ?

Bad programming.

I will not doubt Michel's statement, but there are situations where such things can be used.

For example, there is a lexical parameter in Reports Builder which comes in handy when you have to dynamically create a certain WHERE condition for the SELECT statement.

Sometimes you need to select records that satisfy a condition based on input parameter; then you create a lexical parameter which looks like
if input_parameter = 'Y' then
   lex_parameter := 'and cb_active = 1';
else
   lex_parameter := 'and 1 = 1';
end if;

In report's query, you'd then use it as
select id, ename, address
from some_table
where hiredate >= trunc(sysdate, 'yyyy')
  &lex_parameter
which, depending upon input parameter, becomes either
where hiredate >= trunc(sysdate, 'yyyy')
  and cb_active = 1
or
where hiredate >= trunc(sysdate, 'yyyy')
  and 1 = 1

The above was just a silly example; all I meant to say was that *somethimes* "1 = 1" (or 1 = 2) might help. I'm not saying that it can't be done differently, but - "1 = 2" obviously shows that thank you, I don't want this to return anything. ./fa/1597/0/
Re: an SQL query [message #442386 is a reply to message #442384] Sun, 07 February 2010 09:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree that "1=1" can be useful in products that generate dynamic condition because they automatically generate "where 1=1" and then all other conditions can be added with "and ..." without having to know if they are the first one (with "where") or subsequent ones (with "and");

But the only purpose of "1=2" I saw (out of CTAS), is to get the description of the expressions in the select clause and this is not the correct way to do as any language as a "describe" function.

Regards
Michel
Re: an SQL query [message #442434 is a reply to message #442386] Mon, 08 February 2010 00:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot tell from the info provided that this is bad programming. I can imagine situations where one has little to no control over which queries are executed (eg Reports), but in order to force a query not to return anything, the lexical Littlefoot was mentioning is set to 1 = 2.
Re: an SQL query [message #442442 is a reply to message #442434] Mon, 08 February 2010 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is Reports that is bad programmed.

Regards
Michel
Re: an SQL query [message #442659 is a reply to message #442275] Tue, 09 February 2010 15:22 Go to previous messageGo to next message
navin_deep
Messages: 18
Registered: November 2009
Junior Member
i could not understand completely the concepts which Littlefoot started. Can anyone explain me OR give links on this topic to read myself ?
Thanks in Advance!!

regards
-navin
Re: an SQL query [message #442663 is a reply to message #442275] Tue, 09 February 2010 16:15 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Expressions like where 1 = 2 are used to stop a query from returning data.

There are several techniques for this but this technique in particular is liked because it short circuits the oracle optimizer. Oracle optimizer knows that 1=2 will never be true and thus never actually executes the query. This is the most efficient way to cause a query to not return data.

One reason why this is useful is in testing situations where a developer wants to check code to see if the syntax works.

Other than that I can think of no other value but that does not mean others smater than I can't give you one.

Kevin
Previous Topic: Need help writing elements of an array to a single row (merged 3)
Next Topic: Loading data from flat file using ORGANIZATION EXTERNAL
Goto Forum:
  


Current Time: Fri Dec 02 16:31:48 CST 2016

Total time taken to generate the page: 0.24609 seconds