PL SQL select count(*) giving wrong answer

Question

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?

How is the question related to PL/SQL, packages or PL/SQL Developer? — William Robertson
– William Robertson, Commented Jun 7, 2021 at 19:38

Littlefoot · Accepted Answer · 2021-06-07 09:00:30Z

5

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

answered Jun 7, 2021 at 9:00

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2021-06-07 09:04:02Z

1

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.

answered Jun 7, 2021 at 9:04

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Comments

Data_Geek · Accepted Answer · 2021-06-08 08:33:49Z

0

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.

answered Jun 8, 2021 at 8:33

Data_Geek

214 bronze badges

Collectives™ on Stack Overflow

PL SQL select count(*) giving wrong answer

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related