Avoid cartesian queries - find a proper "where clause"

Question

The following code generates alter password statements to change all standard passwords in an Oracle Database. In version 12.1.0.2.0 it’s not possible anymore to change it to in invalid Password values. That’s why I had to build this switch case construct. Toad gives a warning (rule 5807) on the join construct at the end. It says “Avoid cartesian queries – use a where clause...”. Any Ideas on a “where clause” which works on all oracle database versions?

SET TERMOUT  OFF
SET ECHO     OFF
SET LINESIZE 140
SET FEEDBACK OFF
SET PAGESIZE 0

SPOOL user.sql

SELECT    'alter user '
       || username
       || ' identified by values '
       || CHR (39)
       || CASE
             WHEN b.version = '12.1.0.2.0' THEN '462368EA9F7AD215'
             ELSE 'Invalid Password'
          END
       || CHR (39)
       || ';'
  FROM DBA_USERS_WITH_DEFPWD a,
       (SELECT DISTINCT version
          FROM PRODUCT_COMPONENT_VERSION) b;

SPOOL OFF
@user.sql

Answer 1

On my machine (a laptop with the free XE version of Oracle - simplest arrangement possible) the table PRODUCT_COMPONENT_VERSION has FOUR rows, not one. There are versions for different products, including the Oracle Database and PL/SQL. On my machine the version is the same in all four rows, but I don't see why that should be expected in general.

If they may be different, FIRST you can ignore all the answers that tell you the cross join is not a problem because you return only one row. You don't; you may return more than one row. SECOND, in your query itself, why are you returning the version from ALL the rows of PRODUCT_COMPONENT_VERSION, and not just the version for the Oracle Database? I imagine something like

WHERE PRODUCT LIKE 'Oracle%'

should work - and you won't need DISTINCT in the SELECT clause. Good luck!