How to concat two columns and comparing the value with other table-CodePudding

I'm trying to concatenate two columns and compare this value to a "not in" condition, from another select, but I'm not succeeding. My last attempt:

SELECT  valorA || SubStr(valorB,3,2) AS al,hours FROM table_A
where valorA LIKE '%RR%'
AND hours > To_Date('24/09/2021 12:00','dd/mm/yyyy HH24:mi')
  AND al NOT IN ( 
SELECT vr
FROM   table_B )

Its returnin error: ORA-00904: "AL": invalid identifier

CodePudding user response：

As Del said (like that name!), you cannot reference a column alias in the same query block. Choices are rephrase the concatenation in the predicate (as Del pointed out):

SQL> SELECT  valorA || SubStr(valorB,3,2) AS al,hours FROM table_A
  2  where valorA LIKE '%RR%'
  3  AND hours > To_Date('24/09/2021 12:00','dd/mm/yyyy HH24:mi')
  4    AND valorA || SubStr(valorB,3,2) NOT IN (
  5  SELECT vr
  6* FROM   table_B );

no rows selected

Use a subquery, either subquery factoring or embedded subquery. How many of the predicates you push down should depend on whether you're reusing the same query block potentially multiple times in the same stmt ... . Should not matter to the Optimizer.

-- subquery factoring
SQL> with q as
  2  (SELECT  valorA || SubStr(valorB,3,2) AS al,hours FROM table_A
  3  where valorA LIKE '%RR%'
  4  AND hours > To_Date('24/09/2021 12:00','dd/mm/yyyy HH24:mi'))
  5  select al, hours from q
  6  where al NOT IN (
  7  SELECT vr
  8* FROM   table_B );

no rows selected
-- subquery factoring, less predicates pushed down
SQL>  with q as
  2   (SELECT  valorA || SubStr(valorB,3,2) AS al,hours FROM table_A
  3    where valorA LIKE '%RR%')
  4  select al, hours from q
  5  where hours > To_Date('24/09/2021 12:00','dd/mm/yyyy HH24:mi')
  6  and al NOT IN (
  7   SELECT vr
  8*  FROM   table_B );

no rows selected

-- normal, embedded subquery
SQL> select al, hours from
  2  (SELECT  valorA || SubStr(valorB,3,2) AS al,hours FROM table_A
  3  where valorA LIKE '%RR%')
  4  where  hours > To_Date('24/09/2021 12:00','dd/mm/yyyy HH24:mi')
  5  and al NOT IN (
  6    SELECT vr
  7*   FROM   table_B );

no rows selected