Oracle SQL Merge/Join not working (side by side)-CodePudding

Can anyone explain why this is not producing the desired results?

select a.*, b.Feb22 
from (
    select 
        'Wtd Avg FICO' as Metric
        ,750.5 as Jan22
    from dual
    union all
    select 
        'Wtd Avg DTI' as Metric
        ,0.35 as Jan22
    from dual
    union all
    select 
        'Wtd Avg LTV' as Metric
        ,0.75 as Jan22
    from dual
    ) a
inner join
select * 
from (
    select 
        'Wtd Avg FICO' as Metric
        ,700.5 as Feb22
    from dual
    union all
    select 
        'Wtd Avg DTI' as Metric
        ,0.50 as Feb22
    from dual
    union all
    select 
        'Wtd Avg LTV' as Metric
        ,0.25 as Feb22
    from dual
    ) b on a.Metric = b.Metric

Desired Results:

Metric	Jan22	Feb22
Wtd Avg FICO	750.5	700.5
Wtd Avg DTI	0.35	0.75
Wtd Avg LTV	0.75	0.50

CodePudding user response：

The query was failing to execute as there was no table specified after the INNER JOIN.

The inner join was looking for a table in the format INNER JOIN <table_name> but instead you had a SELECT statement.

You can fix this by using an inline view by nesting the SELECT statement in another subquery as shown in the example.

SELECT a.*, b.Feb22 
  FROM (
        SELECT 'Wtd Avg FICO' as Metric
              ,750.5 as Jan22
          FROM dual
         UNION ALL
        SELECT 'Wtd Avg DTI' as Metric
              ,0.35 as Jan22
          FROM dual
         UNION ALL
        SELECT 'Wtd Avg LTV' as Metric
              ,0.75 as Jan22
          FROM dual
       ) a
 INNER JOIN (
SELECT * 
  FROM (
        SELECT 'Wtd Avg FICO' as Metric
              ,700.5 as Feb22
          FROM dual
         UNION ALL
        SELECT 'Wtd Avg DTI' as Metric
              ,0.50 as Feb22
          FROM dual
         UNION ALL
        SELECT 'Wtd Avg LTV' as Metric
              ,0.25 as Feb22
          FROM dual
        ) b_subquery
        ) b 
    ON a.Metric = b.Metric

CodePudding user response：

JOIN works only between two tables an you b isn't one i sql yo you need to rewrite it

see example db<>fiddle here

select a.*, b.Feb22 
from (
    select 
        'Wtd Avg FICO' as Metric
        ,750.5 as Jan22
    from dual
    union all
    select 
        'Wtd Avg DTI' as Metric
        ,0.35 as Jan22
    from dual
    union all
    select 
        'Wtd Avg LTV' as Metric
        ,0.75 as Jan22
    from dual
    ) a
inner join
(select * 
from (
    select 
        'Wtd Avg FICO' as Metric
        ,700.5 as Feb22
    from dual
    union all
    select 
        'Wtd Avg DTI' as Metric
        ,0.50 as Feb22
    from dual
    union all
    select 
        'Wtd Avg LTV' as Metric
        ,0.25 as Feb22
    from dual
    ) b1 ) b on a.Metric = b.Metric