NAB datasource SQL statement rules

[gzambrano]

Rob,

The select does not work in the data source. It does work on Run SQL Scripts. See below:

Lateral example.png

[Johnny Major]

Please provide the SQL via text “pasting” the SQL that isn’t working and we will add it to the our Valance 6 feature request/bug list.

Thanks

[gzambrano]

select a.vouchr, a.vendor, x.account, x.amount
from GLORIA/horfile as a
cross join lateral(values (a.acT001, a.amT001),
(a.acT002, a.amt002),
(a.acT003, a.amT003))
as x (account, amount) where x.amount > 0

Thanks Johnny.

FYI, the definition of horfile is:

A R HORFILER
A VOUCHR 15A TEXT('VOUCHER')
A VENDOR 15A TEXT('VENDOR')
A ACT001 15A TEXT('ACCOUNT 01')
A AMT001 8S 0 TEXT('AMOUNT 01')
A ACT002 15A TEXT('ACCOUNT 02')
A AMT002 8S 0 TEXT('AMOUNT 02')
A ACT003 15A TEXT('ACCOUNT 03')
A AMT003 8S 0 TEXT('AMOUNT 03')

[gzambrano]

Johnny,

The following SQL also works on the Run SQL Scripts window but does not work in the data source:

select vouchr, vendor, account, amount
from GLORIA/horfile as s,
lateral(values (acT001, amT001),
(acT002, amt002),
(acT003, amT003))
as t(account, amount)

Thanks

[dlstrawn]

why the "as s" after from and "as t" before (account, amount)?

[robert.swanson]

The "as s" is optional, it's just providing an alias to the columns in horfile. It's not being used in this case, but if any of the column names were not unique to horfile you could use "s.field_name" as shorthand for saying "horfile.field_name".

The "as t" part is mandatory, as it's being used to provide a table identifier to the two-column lateral. Again, the column names are unique so "t" is not being used (i.e., via "t.field_name"), but you could specify if for improved clarity on which column is coming from where.

So Gloria's statement could be restated this way:

SELECT s.vouchr, s.vendor, t.account, t.amount
  FROM gloria/horfile AS s,
       LATERAL(VALUES (act001, amt001),
                      (act002, amt002),
                      (act003, amt003)) AS t (account, amount)

I should mention that many developers leave off the "AS" part, since it's implied by any string that follows a table reference...

SELECT s.vouchr, s.vendor, t.account, t.amount
  FROM gloria/horfile s,
       LATERAL(VALUES (act001, amt001),
                      (act002, amt002),
                      (act003, amt003)) t (account, amount)

:

[dlstrawn]

gotcha wasn't sure why the "s" was being used at all... the "t" I was confused about because it would not let me leave it off - which I thought I could do since it was not used as a qualifier with the dot...

[dlstrawn]

it looks like the gotcha with the cross join lateral is that you can't do an order by... at least this doesn't work with the order by clause in it (you take it out and it works fine)

select
a.podate,
c.dept concat '-' concat c.divn as "account",
b.name,
c.amount
from nfcclibf.npofile a
join nfcclibf.nvenmast b on b.vendno = a.povend
cross join lateral (values
(podp1, podv1, poam1),
(podp2, podv2, poam2),
(podp3, podv3, poam3))
c(dept, divn, amount)
where c.amount <> 0

[robert.swanson]

An ORDER BY should work fine in that statement. Are you placing it after the WHERE clause?

[dlstrawn]

This may be due to something I have wrong, but here is what happens when I add the order by clause:

ScreenShots.docx

[robert.swanson]

I'm not seeing anything wrong with that statement, nor do I see any error message in your screenshot... What happens if you paste it into Run SQL Scripts in ACS and try running it there?

[dlstrawn]

there is no error, it starts but never finishes, because it goes into a LCKW condition... (see the green screen screen shot). I will try it in ACS...

[dlstrawn]

ok, it does work in ACS... must be something strange in RDi... it does not pass syntax checking in NAB. See below...

ScreenShot2.docx

[robert.swanson]

Yes, we're aware of that. It will work in the next build, which should be out by next week.

In the meantime, try using it in a View. Keep in mind you cannot specify an ORDER BY clause in the View, but you can in the SELECT clause of your NAB data source that pulls from the View.

[dlstrawn]

gotcha! Thanks!