How to convert the Oracle code below to BigQuery?
max(to_date(BIC_GM_AGCPOAODS00_BO_VW.BOUND_DATE,'yyyymmdd'))
When I try this code:
SELECT A._BIC_GCISBN,
max(cast(A.BOUND_DATE as date),'yyyymmdd')
FROM `BIC_GM_AGCPOAODS00_BO_VW` A
WHERE A._BIC_ZC2GRIRIN = 'G' AND A._BIC_ZCLOEKZ = ' '
GROUP BY A._BIC_GCISBN
I am get the error:
No matching signature for aggregate function MAX for argument types: DATE, STRING. Supported signature: MAX(ANY) at [15:2]
In BigQuery, you want parse_date():
SELECT A._BIC_GCISBN,
MAX(PARSE_DATE('%Y%m%d', A.BOUND_DATE))
FROM `BIC_GM_AGCPOAODS00_BO_VW`A
WHERE A._BIC_ZC2GRIRIN = 'G' AND A._BIC_ZCLOEKZ = ' '
GROUP BY A._BIC_GCISBN;
You should be storing the value using date, but sometimes we don't have control over how data is stored.
EDIT:
Given your data format, you can also write this as:
SELECT A._BIC_GCISBN,
PARSE_DATE('%Y%m%d', MAX(A.BOUND_DATE))
FROM `BIC_GM_AGCPOAODS00_BO_VW`A
WHERE A._BIC_ZC2GRIRIN = 'G' AND A._BIC_ZCLOEKZ = ' '
GROUP BY A._BIC_GCISBN;
I prefer the first version because it generalizes readily to any data format. This might be more efficient.
PARSE_DATE('%Y%m%d', MAX(A.BOUND_DATE)) instead will make a difference in terms of performance / resources?
MAX(DATE()) consumes twice more slots vs. DATE(MAX()) - around 1 slot during that 1 sec vs 0.5 slots - which is also consistent with execution plan. Obviously absolutely negligible difference for single query. Trying to understand how this type of "differences" can affect effectiveness of flat rate usage on massive scale. most likely not much - but want to have better insight on this ...
varchar? That's a really bad idea