Row Exists

[nread]

People,

I am trying to return the first existance of a row within a table even if there are more than one entry.

For example abc exists in table X three times.

abc < once i have found one entry where abc exists then quit
abc
abc

Can anyone help please....?

Cheers
Nick

 

[lewisp]

[tt]SELECT * FROM table
WHERE column = 'abc'
AND rownum = 1;[/tt]

 

[nread]

Bearing in mind that the col is called 'a' and contains many different entries one of which is abc then would rownum = 1 work...?

a
xxx
ccc
ddd
ddd
abc < quit execution
abc
abc
sss
sss


Cheers

 

[lewisp]

In that case, you need PL/SQL

[tt]DECLARE
testchr table.a%TYPE := NULL;
BEGIN
FOR c IN (SELECT * from table ORDER BY a) LOOP
EXIT WHEN Nvl(testchr,'z') = c.a
AND c.a = 'abc';
--
Do_Something;
END LOOP;
END;

You would need the order by to ensure that all occurrences of 'abc' fall together.

 

[lewisp]

Oops, I forgot you would need

[tt]testchr := c.a;[/tt]

after the EXIT WHEN

 

[lecorr]

What you seems funny to me, since you can't predict the order oracle returns the rows when you do a select...

If you just export/import data, order may differ.

Maybe what lewisp said is good, but I wonder about the purpose of such a thing?

Maybe you should explain this purpose so we can give a 'real' good answer...

Cheers,

Christian

 

[nread]

Ok,

I have a list of ID's which do not appear in the log files for various daily tables in a database. I need to be able to confirm that the files were loaded into the daily tables despite the fact they are not in the logs. Now because these tables are are not summary tables and are at event level i.e thousands of occurences of the ID i need to be able to say if the file in the log exists at least one in the daily table i am happy that the file has been loaded and move to the next file in the log.

Does this make sense...?

Cheers

 

[nread]

May not have made my previos too clear, i have another data source which i have reconciled to the log files which tells me they are missing. It is this list which i need to query against the daily tables....

Cheers

 

[lecorr]

Of what I understand, you want to check if at least one record as been added from a file in your daily table?

For me, that mean:
- Check the number of records against the size of the file (if the file has constant line length, you should find Size_of_file divided by length_of_a_line = number_of_record_loaded). This is true also if you load several files in the same table, just do the sum.
- If the tables are emptied before the load, if you find one record, that means the files was loaded.
- Or just check at least some records (that you can check against another table) where loaded so do a select against this other table.

I hope this can help since your explaination is not really clear to me.

If you can, just post table format and a set of data so someone could have better ideas than me to solve your problem.

Cheers,

Christian