CODEWITHSUNDEEP
sqlpostgresql
nimgwfc
nimgwfc

Reputation: 1509

Add row to a select query

I have a sub query that I am using to get FX Values which is itself used as part of a materialized view.

Currently, my code looks like this for the subquery:

fx_rate AS (
        SELECT year::INT AS date,
            CASE WHEN r.counter_currency_code = 'EUR' THEN (SELECT g.country_iso)
                 WHEN r.counter_currency_code = 'XOF' THEN (SELECT g.country_iso)
            ELSE (SELECT reporter_iso FROM countries_view c where c.reporter =  r.counter_currency LIMIT 1)
            END AS country_iso,
            yearlyaverage AS fx_rate
        FROM currency_rates_view r left join currency_groups g on r.counter_currency_code = g.currency_group_code
    )

This is then used in an outer join on all countries for the materialized view.

I have no currency data for USA (one of the countries in my view) so I just want a value of 1 returned for every year.

Is there a way I can add this so that when I do my Select it will look like this:

date  | country_iso |       fx_rate       
------+---------+---------------------
 2018 | CMR         |    556.971455938697
 2016 | USA         |    1
 2017 | USA         |    1
 2018 | USA         |    1

etc.

Upvotes: 1

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Can you use union all and generate_series()?

    SELECT year::INT AS date,
           (CASE WHEN r.counter_currency_code = 'EUR' THEN (SELECT g.country_iso)
                 WHEN r.counter_currency_code = 'XOF' THEN (SELECT g.country_iso)
                 ELSE (SELECT reporter_iso FROM countries_view c where c.reporter =  r.counter_currency LIMIT 1)
            END) AS country_iso,
           yearlyaverage AS fx_rate
    FROM currency_rates_view r LEFT JOIN
         currency_groups g 
         ON r.counter_currency_code = g.currency_group_code
    UNION ALL
    SELECT gs.year, 'USA' as country_iso, 1
    FROM generate_series(2000, 2018, 1) gs(year)

Upvotes: 1

Related Questions