-2

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
BuJay
  • 115
  • 1
  • 12
  • 1
    Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Aug 28 '22 at 02:58
  • Possible duplicate of [Order Of Execution of the SQL query](https://stackoverflow.com/a/4596739/3404097) – philipxy Aug 28 '22 at 03:00
  • When you get a result you don't expect, find, present & justify the smallest wrong code--code that doesn't do what you expect--that is an extension of correct code--code that does what you expect. Say what you expect & why, justified by reference to authoritative documentation. (Debugging fundamental.) Otherwise you're just asking for yet another language definition & presentation for you to misunderstand, with no details of your misconceptions. [mre] [ask] Plus basic problems are faqs you can then characterize specifically enough to (re)search & find. – philipxy Aug 29 '22 at 01:29

2 Answers2

1

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
nbk
  • 45,398
  • 8
  • 30
  • 47
1

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
LiamJM
  • 172
  • 12