It *is* very long. It would have been easier for me to consume in two parts, I think. But I don't speak for everyone :) That being said, I think your explanations are clear and the whole thing flows well.
Thanks, Josh! Is it hard reading? Once I've written it, I realized that probably it is quite long (it was even longer with logical trees included, which were thrown out at some revision). I was thinking to split it in two parts, but now it's as it is.
for the transcript though: if you've arrived here from GlobalSign internal documentation, you are morally obligated to @-reply/ping me from this message
in a pleasing (possibly ironic) symmetry, the link in that screencap is actually to the chat message in this room where i started rubber-ducking :sweat_smile:
...but it's the explanation I put in the documentation I did for it ¯\\\_(ツ)_/¯
my working theory is this: because there's a single element in the `row()` returned by `jsonb_array_elements()`, then `->>` gets lazily assigned. `with ordinality` adds a second element to the `row()` returned and forces the error. sadly this isn't supported by my attempt at a repro...
I wonder, if there was a `->>`operator for `record`, how would it know which `jae` to pick to apply the operator? Because I'm guessing in the above query the `jae` in `jae->>'a'` resolves as `jae.jae`. In other words, it's the `jae` column alias, not the `jae` table alias. The operator applies to the column because there's no such operator for `record` (the table alias), but what if there was?
> "_just add the column alias, too_"
wondering if that warrants a bug report :thinking:
It looks like, in DB2, that could be selecting from the vendor-specific "dual" table:
SELECT 'potato', 'bananas'
or using the `VALUES` clause to define and build a table expression:
SELECT t.c1, t.c2
FROM (VALUES ('potato', 'bananas')) t (c1, c2);
this is valid query in sql server 2019 Linux https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=e504210afbcffe28e949a8efdd745a3a but not in db2 https://dbfiddle.uk/?rdbms=db2_11.1&fiddle=169ee4b22bab1d7bf3fc76b363a9192f
Good morning guys, how do I write `select 'apple', 'banana'` in db2?
John K N
No worries, happy to help
thank you. Seems like this is exactly what I was looking for
Same suggestions elsewhere: https://sqlskull.com/2020/12/18/get-row-counts-for-all-tables-in-sql-server/