CODEWITHSUNDEEP
sqloracle-database
Jack Deeth
Jack Deeth

Reputation: 3357

Oracle SQL: inline table within a SELECT statement?

I'm configuring an integration tool which moves data from System A to System B. I provide an SQL SELECT statement which it runs against System A, and the statement's output determines what in System B gets updated. For instance, to update Team 1's international sales figure:

SELECT
  'Team1_Int_Sales_Count' Code,
  count(*) Count,
  to_char(<integration tool date syntax>) Period
FROM
  Sales
JOIN
  ADozenMoreTables ON stuff
WHERE
  ADozenOpaqueFields IN ADozenOpaqueReferences
  AND Sales.Date BETWEEN <integration tool date syntax>
  AND Team = 'Team1'
  AND Sales.Type = 'International'

It uses the returned fields Code, Value, and Period to update a that code for that period with that value. (Fields identified by what order they're in, not by name.)

Then, for Team 1's domestic sales, I've copied and pasted the entire query, and changed the code and one WHERE clause:

SELECT
  'Team1_Dom_Sales_Count' Code,
  <otherwise as above>
  AND Sales.Type = 'Domestic'

And then I copy and paste it four more times for Teams 2 and 3

SELECT
  'Team2_Int_Sales_Count' Code,
  <snip>
  AND Team = 'Team2'
  AND Sales.Type = 'International'

SELECT
  'Team2_Dom_Sales_Count' Code,
  <snip>
  AND Team = 'Team2'
  AND Sales.Type = 'Domestic'

SELECT
  'Team3_Int_Sales_Count' Code,
  <snip>
  AND Team = 'Team3'
  AND Sales.Type = 'International'

SELECT
  'Team3_Dom_Sales_Count' Code,
  <snip>
  AND Team = 'Team3'
  AND Sales.Type = 'Domestic'

The full problem has a ~60-line SELECT statement with 3x3x3 permutations and the amount of copy/pasting involved is giving me the fear.

Is there some way I can write a single SQL SELECT statement which will step through all the permutations without the copy-pasted repetition? In my mind's eye I'd have the permutations in a temporary table created inline, or as a 2-dimensional array, and the query could return the code and the value where the other two fields match:

      { {'Team1_Int_Sales_Count', 'Team1', 'International'},
        {'Team1_Dom_Sales_Count', 'Team1', 'Domestic'},
        {'Team2_Int_Sales_Count', 'Team2', 'International'},
        {'Team2_Dom_Sales_Count', 'Team2', 'Domestic'},
        {'Team3_Int_Sales_Count', 'Team3', 'International'},
        {'Team3_Dom_Sales_Count', 'Team3', 'Domestic'} } Permutations

Constraints here are the integration tool requires I provide each task with a single SELECT statement. I cannot preface it with a WITH statement, or declare functions, or store the complex query as a view within the source database, or do anything that's fun or nice. And it's an Oracle ODBC connection, so it uses Oracle SQL.

Upvotes: 0

Views: 989

Answers (3)

sticky bit
sticky bit

Reputation: 37472

You seem to want a cross join.

SELECT t.team || ct.code code,
       t.team,
       ct.type
       FROM (SELECT '_Int_Sales_Count' code,
                    'International' type
                    FROM dual
             UNION ALL
             SELECT '_Dom_Sales_Count' code,
                    'Domestic' type
                    FROM dual) ct
            CROSS JOIN (SELECT 'Team1' team
                               FROM dual
                        UNION ALL
                        SELECT 'Team2' team
                               FROM dual
                        UNION ALL
                        SELECT 'Team3' team
                               FROM dual) t;

db<>fiddle

Upvotes: 1

Thomas Strub
Thomas Strub

Reputation: 1285

I would do it like: 

with 
  team as (select  level lv from dual connect by level <= 3)
, bas  as (select '_Int_Sales_Count' n_count, 'International' type from dual
       union all select '_Dom_Sales_Count' n_count, 'Domestic' type from dual)
, permutations as 
     ( select 'Team' || lv ||n_count   as code,  'Team' || lv as team, type from team join bas on (1=1))
select * from permutations

but with the constraints: 

select 'Team' || lv ||n_count   as code,  'Team' || lv as team, type from
      (select  level lv from dual connect by level <= 3) team,
      (select '_Int_Sales_Count' n_count, 'International' type from dual
           union all select '_Dom_Sales_Count' n_count, 'Domestic' type from dual) bas

Upvotes: 0

Jack Deeth
Jack Deeth

Reputation: 3357

A table of permutations can be constructed like this:

SELECT
  Permutations.Code AS Code,
  COUNT(*) AS Count,
...
JOIN (SELECT * FROM (
        (SELECT 'Team1_Int_Sales_Count' AS Code, 'Team1' AS Team, 'International' AS Type FROM Dual),
  UNION (SELECT 'Team1_Dom_Sales_Count' AS Code, 'Team1' AS Team, 'Domestic'      AS Type FROM Dual),
  UNION (SELECT 'Team2_Int_Sales_Count' AS Code, 'Team2' AS Team, 'International' AS Type FROM Dual),
  UNION (SELECT 'Team2_Dom_Sales_Count' AS Code, 'Team2' AS Team, 'Domestic'      AS Type FROM Dual),
  UNION (SELECT 'Team3_Int_Sales_Count' AS Code, 'Team3' AS Team, 'International' AS Type FROM Dual),
  UNION (SELECT 'Team3_Dom_Sales_Count' AS Code, 'Team3' AS Team, 'Domestic'      AS Type FROM Dual)
)) AS Permutations
ON Permutations.Team = Sales.Team AND Permutations.Type = Sales.Type
WHERE...

Upvotes: 0

Related Questions