CODEWITHSUNDEEP
sqloracle-database
OmarLittle
OmarLittle

Reputation: 503

PL SQL select count(*) giving wrong answer

I have I table consisting of 3 columns: system, module and block. Table is filled in a procedure which accepts system, module and block and then it checks if the trio is in the table:

select count(*) into any_rows_found from logs_table llt
where system=llt.system and module=llt.module and block=llt.block;

If the table already has a row containing those three values, then don't write them into the table and if it doesn't have them, write them in. The problem is, if the table has values 'system=a module=b block=c' and I query for values 'does the table have system=a module=d block=e' it returns yes, or, to be precise, any_rows_found=1. Value 1 is only not presented when I send a trio that doesn't have one of it's values in the table, for example: 'system=g module=h and block=i'. What is the problem in my query?

Upvotes: 0

Views: 327

Answers (3)

Data_Geek
Data_Geek

Reputation: 21

Root cause is alias used for table name.

where system=llt.system and module=llt.module and block=llt.block;

Table name alias in select query and input to procedure having the same name(i.e. llt ). You should consider either renaming one of them.

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191275

From the documentation:

If a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence.

So when you do

where system=llt.system

that is interpreted as

where llt.system=llt.system

which is always true (unless it's null). It is common to prefix parameters and local variables (e.g. with p_ or l_) to avoid confusion.

So as @Littlefoot said, either change the procedure definition to make the parameter names different to the column names, or qualify the parameter names with the procedure name - which some people prefer but I find more cumbersome, and it's easier to forget and accidentally use the wrong reference.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142753

Problem is in this:

where system = llt.system

Both systems are the same, it is as if you put where 1 = 1, so Oracle is kind of confused (thanks to you).

What to do? Rename procedure's parameters to e.g. par_system so that query becomes

where llt.system = par_system

Another option (worse, in my opinion) is to precede parameter's name with the procedure name. If procedure's name was e.g. p_test, then you'd have

where llt.system = p_test.system

Upvotes: 5

Related Questions