CODEWITHSUNDEEP
sqlgoogle-bigquerybigquery-udf
d2kagw
d2kagw

Reputation: 39

Use column value through column name variable

is it possible to use a function property in a select statement?

I have a forex function in BigQuery like this, but it'd be SO much easier if we can use a rlfm[<column name>] style accessor

Current Approach

CREATE OR REPLACE FUNCTION
  reference.fxFromTo(d TIMESTAMP, fromRegion STRING, toRegion STRING, value FLOAT64)
  RETURNS FLOAT64 AS (
    (SELECT
      CASE fromRegion
        WHEN 'AUD' THEN value / rlfm.AUD
        WHEN 'USD' THEN value / rlfm.USD
        WHEN 'EUR' THEN value / rlfm.EUR
        WHEN 'SGD' THEN value / rlfm.SGD
        WHEN 'CAD' THEN value / rlfm.CAD
        WHEN 'GBP' THEN value / rlfm.GBP
        WHEN 'NZD' THEN value / rlfm.NZD
        ELSE -404
      END AS fx
    FROM reference.fx_monthly as rlfm
    WHERE Date = d)
  );

Preferred Approach

CREATE OR REPLACE FUNCTION
  reference.fxFromTo(d TIMESTAMP, fromRegion STRING, toRegion STRING, value FLOAT64)
  RETURNS FLOAT64 AS (
    (SELECT value / rlfm[fromRegion] AS fx
    FROM reference.fx_monthly AS rlfm
    WHERE Date = d)
  );

Upvotes: -1

Views: 69

Answers (1)

Jaytiger
Jaytiger

Reputation: 12264

Instead of rlfm[column_name] notation, you might consider below using regular expression.

CREATE TEMP TABLE fx_monthly AS
SELECT TIMESTAMP '2023-01-01' Date, 10.0 AUD, 20.0 USD, 30.0 EUR, 40.0 SGD, 50.0 CAD, 60.0 GBP, 70.0 NZD
 UNION ALL
SELECT TIMESTAMP '2023-01-02' Date, 11.1 AUD, 21.2 USD, 31.2 EUR, 41.34 SGD, 51.43 CAD, 61.42 GBP, 71.43 NZD;

CREATE TEMP FUNCTION fxFromTo (d TIMESTAMP, fromRegion STRING, value FLOAT64) 
RETURNS FLOAT64 AS ((
  SELECT value / SAFE_CAST(REGEXP_EXTRACT(TO_JSON_STRING(t), FORMAT('"%s":([0-9.]+)', fromRegion)) AS FLOAT64)
    FROM (
      SELECT * FROM fx_monthly WHERE Date = d 
    ) t
));

SELECT fxFromTo('2023-01-02', 'USD', 100.00);

+-------------------+
|        f0_        |
+-------------------+
| 4.716981132075472 |
+-------------------+

Upvotes: 1

Related Questions