Complex select in loop (based on a previous solution)

Question

I asked a question here: Identify phone numbers in a PL/SQL iteration

and I got a perfect answer, but now I need to use this in a complex select statement.

The goal is the same result in a column joined to a query. For example:

  SELECT t1.phone_number
       , t2.origin_country
       , sum(t1.volume) Total_vol
       , sum(t1.charge) Total_chg
       from t2
     LEFT JOIN t1 ON t1.item_no = t2.item_no
     LEFT JOIN t3 ON t3.vcode = t2.vcode
     LEFT JOIN /*<<Here should be a subquery which attach the column with
     the countries to my query>>*/
       +many WHERE and GROUP BY clauses

The problem is the number of the columns and source tables can vary, therefore I am looking for a flexible solution I can use with any complex query where a phone_number column exists. What I have already tried:

• put the entire select into a loop and join to

  SELECT ic.country 
  FROM int_codes ic 
  WHERE ic.int_code = substr(t1.phone_number, 1, i)
  

  as a subquery, but it can't work obviously because it's not stored, no fields to fill

• create a view in a procedure and join the countries to it, but it's not flexible

• not tried but thought about a script with many UNION and NOT EXISTS, but it would be incredibly complicated and slow to run
• with CURSOR but the error message said I had to define a TYPE on schema level, but it's still not flexible due to a determined table structure.

So how should I do this?

(If anybody has a totally different approach to identify and display phone numbers in a table flexibly they are welcome)

--UPDATE--

The solution:

  select ... PHONE_NUMBER, XY, ZZ, ... ,
         case
         when i.INT_CODE = substr(PHONE_NUMBER,1,4) then i.COUNTRY

         when i.INT_CODE = substr(PHONE_NUMBER,1,3) 
         and i.INT_CODE = substr(PHONE_NUMBER,1,4) then i.COUNTRY

         when i.INT_CODE = substr(PHONE_NUMBER,1,2) 
         and i.INT_CODE = substr(PHONE_NUMBER,1,3) then i.COUNTRY

         when i.INT_CODE = substr(PHONE_NUMBER,1,1) 
         and i.INT_CODE = substr(PHONE_NUMBER,1,2) then i.COUNTRY

         else 'Unidentified location'
         end TARGET_COUNTRY

   from  (
          select ... t1.phone_number ,sum(xy) Total XY, sum(zz) ZZ, ... 
             /*same fields like in the main query above*/
          left join t2 on ...
          left join t3 on ...

          where date = 'some date' and country in ('country1','country2') ...
             /*many conditions*/
          group by t2.phone_number,
                   t3.account ... 
          ) MainQuery

  left join int_codes i
  on (    i.INT_CODE = substr(MainQuery.PHONE_NUMBER, 1, 1)
       or i.INT_CODE = substr(MainQuery.PHONE_NUMBER, 1, 2)
       or i.INT_CODE = substr(MainQuery.PHONE_NUMBER, 1, 3)
       or i.INT_CODE = substr(MainQuery.PHONE_NUMBER, 1, 4)
     );

Answer 1

If you are using Oracle 12c, then you can use a LATERAL VIEW
with a subquery which determines a right country code:

SELECT .........
FROM T_NUMBERS t
LEFT JOIN ..............
LEFT JOIN ................
, /* this comma is required by the syntax */
LATERAL (
  SELECT * FROM int_codes i
  WHERE i.INT_CODE = substr(t.PHONE_NUMBER, 1, 1 )
     OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 2 )
     OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 3 )
     OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 4 )
  ORDER BY length( i.INT_CODE ) DESC
  FETCH FIRST 1 ROWS ONLY
)
GROUP BY .....;

---

Furthermore, assuming that T_NUMBERS has some primary key column that uniquely identifies each row, then you can use this subquery together with MERGE statement to update T_NUMBERS table
with correct country codes and names, in this way (assume that PK is a primary key column):

MERGE INTO T_NUMBERS  t
USING (
  SELECT t.pk, i.country, i.int_code
  FROM T_NUMBERS t,
  LATERAL (
    SELECT * FROM int_codes i
    WHERE i.INT_CODE = substr(t.PHONE_NUMBER, 1, 1 )
       OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 2 )
       OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 3 )
       OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 4 )
    ORDER BY length( i.INT_CODE ) DESC
    FETCH FIRST 1 ROWS ONLY ) i
) i
ON ( t.pk = i.pk )
WHEN MATCHED THEN UPDATE SET t.COUNTRY = i.COUNTRY, t.COUNTRY_CODE = i.int_CODE;
;

----- EDIT -----

On Oracle 11g you can try something like the below
- but still the assumption is that T_NUMBERS table has some primary key column (in the query below this column is named pk):

SELECT ........
 FROM (
      SELECT t.pk, t.phone_number,
             i.int_code, i.country,
             aaa.*, bbb.*
             row_number() over( partition by pk order by length(i.int_code) desc ) xxxx
      FROM T_NUMBERS t
      LEFT JOIN aaa   ......
      LEFT JOIN bbb   ......
      LEFT JOIN int_codes i
      ON (    i.INT_CODE = substr(t.PHONE_NUMBER, 1, 1 )
           OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 2 )
           OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 3 )
           OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 4 )
        ) 
)
WHERE xxxx = 1
GROUP BY ........ ;

I am not sure how it will perform, probably it could be slow.

The below version may perform slightly better:

SELECT ........
 FROM (
      SELECT t.pk, t.phone_number,
             i.int_code, i.country,
             row_number() over( partition by pk order by length(i.int_code) desc ) xxxx
      FROM T_NUMBERS t

      LEFT JOIN int_codes i
      ON (    i.INT_CODE = substr(t.PHONE_NUMBER, 1, 1 )
           OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 2 )
           OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 3 )
           OR i.INT_CODE = substr(t.PHONE_NUMBER, 1, 4 )
        ) 
)
LEFT JOIN aaa   ......
LEFT JOIN bbb   ......
WHERE xxxx = 1
GROUP BY ........ ;