Idem.

Thanks in advance,
Jose Luis.

jose_luis_fdez_diaz wrote:
I am not 100% sure what you are trying to accomplish.

Your can select from dual:
SELECT
SYSDATE CURRENT_DATE
FROM
DUAL;

SELECT
'Hello World'
FROM
DUAL;

You can select from a table, bringing along with it a text constant,
and a date constant:
SELECT
SID,
SERIAL#,
'User' TEXT_MESSAGE,
SYSDATE CURRENT_DATE
FROM
V$SESSION;

If you are wanting to return a row, even when a matching value does not
appear in the table, perform an outer join (left outer join) between a
table containing the full list of values and the table of interest:
SELECT
SID,
SERIAL#,
'User' TEXT_MESSAGE,
SYSDATE CURRENT_DATE
FROM
V$SESSION S,
MY_SESSION_HISTORY SH
WHERE
SH.SID=S.SID(+);

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

jose_luis_fdez_diaz <gfiuni2 (AT) gmail (DOT) com> wrote:
'Idem' is Latin for 'the same'.

What do you want to tell us?
Or, more to the point, what is your question?

If it is the question in the subject, please clarify what you want.

Are you looking for 'SELECT 42 FROM dual' ?

Yours,
Laurenz Albe

In the table below:

table1
---------
1
2
4
5


select 3 from table1 where not exists (select * from table1 where
row1!=3)

returns:

3
3
3
3

I only want "one" 3.

How can I get it?

PD: Sorry for my little explanation in the last post.


Regards,
Jose Luis.

Laurenz Albe wrote:

jose_luis_fdez_diaz schrieb:
Your query would return no rows (not 4 rows with '3').
Still it is unclear, what you try to achieve.
Do you have a sequential values in your table and like find the "gaps"
in this sequence ? If yes, are the start and end values defined ( i.e. ,
if the gap occur at the highest end of your sequence, do you consider
this to be a gap or not) ?

Best regards

Maxim

jose_luis_fdez_diaz wrote:
I suggest a different approach:
Setting up the test case:
CREATE TABLE TABLE1 (ROW1 NUMBER(22));
INSERT INTO TABLE1 VALUES (1);
INSERT INTO TABLE1 VALUES (2);
INSERT INTO TABLE1 VALUES (4);
INSERT INTO TABLE1 VALUES (5);

SELECT
COUNT(*) MATCH_ROWS
FROM
TABLE1
WHERE
ROW1=3;

MATCH_ROWS
==========
0

The above will give you a count of the rows where ROW1 is 3. A count
of 0 implies that 3 is not included in the ROW1 column.

The above does not return the result that you are looking for, which
appears to be 3, if 3 does not appear in column ROW1. To obtain that
result, the above query is modified:
SELECT
DECODE(COUNT(*),0,3,0) NOT_IN
FROM
TABLE1
WHERE
ROW1=3;

NOT_IN
==========
3

If the COUNT is equal to 0, the value 3 is returned, otherwise the
value 0 is returned.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

This is what I was looking for.

Thanks for your help.

Regards,
Jose Luis.

Charles Hooper wrote: