Is it possible to include more than 1 SQL statement in a data source definition?
Announcement
Collapse
No announcement yet.
NAB datasource SQL statement rules
Collapse
X
-
not sure a with clause will work... but here is what i have:
We have a lot of tables designed horizontally, meaning:
Voucher# Vendor# Account# 01 Amount 01 Account# 02 Amount 02 Account# 03 Amountt 03… and on and on…
Is there a way, with 1 SQL statement to turn that “horizontal” row into vertical rows in the result set, meaning:
Voucher# Vendor# Account# 01 Amount 01
Voucher# Vendor# Account# 02 Amount 02
Voucher# Vendor# Account# 03 Amount 03
Comment
-
Oh boy, that's ugly!
I would probably suggest creating an SQL View that normalizes the table through a bunch of UNIONs, then use that View for your data source. You'd probably want to omit records that don't have an account, though I assume there's always at least a value in account#01...
So perhaps something like this:
Code:CREATE VIEW your_library/new_view_name as ( SELECT voucher#, vendor#, account#01, amount01 FROM your_table UNION SELECT voucher#, vendor#, account#02, amount02 FROM your_table WHERE account#02>0 UNION SELECT voucher#, vendor#, account#03, amount03 FROM your_table WHERE account#03>0 UNION (etc) )
Last edited by robert.swanson; 05-01-2021, 08:56 AM.
Comment
-
As I understand it, the only difference between a UNION and UNION ALL is that the former would eliminate duplicate records in the result set. There shouldn't be any limitation on the number of select statements. Going with UNION ALL may yield better performance if you don't care about duplicates.
https://www.ibm.com/support/pages/di...-union-all-db2
Comment
-
I tried the select with union... this is what I got
statement I put in datasource:
select
a.pv concat a.pvnum as "Voucher#",
a.invno as "Invoice#",
a.invdt as "Invoice Date",
a.duedat as "Due Date",
a.apam1 as "Amount",
a.apfd1 concat '-' concat a.apfc1 concat '-' concat a.apdp1 concat '-' concat a.apdv1 concat '-' concat a.apli1 concat '-' concat a.appj1 as "Account#",
a.vendor as "Vendor#",
b.name as "Vendor Name"
from nfcclibf.ncheckshst a
join nfcclibf.nvenmast b on b.vendno = a.vendor
where a.duedat = '42921' and apam1 <> 0
union all
select
a.pv concat a.pvnum as "Voucher#",
a.invno as "Invoice#",
a.invdt as "Invoice Date",
a.duedat as "Due Date",
a.apam2 as "Amount",
a.apfd2 concat '-' concat a.apfc2 concat '-' concat a.apdp2 concat '-' concat a.apdv2 concat '-' concat a.apli2 concat '-' concat a.appj2 as "Account#",
a.vendor as "Vendor#",
b.name as "Vendor Name"
from nfcclibf.ncheckshst a
join nfcclibf.nvenmast b on b.vendno = a.vendor
where a.duedat = '42921' and apam2 <> 0
what editor did with statement:
WITH tempt AS (
(
SELECT
a.pv concat a.pvnum AS voucher#,
a.invno AS "invoice#",
a.invdt AS "invoicedate",
a.duedat AS "duedate",
a.apam1 AS "amount",
a.apfd1 concat '-' concat a.apfc1 concat '-' concat a.apdp1 concat '-' concat a.apdv1 concat '-' concat a.apli1 concat '-' concat a.appj1 AS account#,
a.vendor AS "vendor#",
b.name AS "vendorname"
FROM
nfcclibf.ncheckshst AS a
JOIN nfcclibf.nvenmast AS b ON b.vendno = a.vendor
WHERE
(
a.duedat = '42921'
and apam1 <> 0
)
)
UNION ALL
SELECT
a.pv concat a.pvnum AS voucher#,
a.invno AS "invoice#",
a.invdt AS "invoicedate",
a.duedat AS "duedate",
a.apam2 AS "amount",
a.apfd2 concat '-' concat a.apfc2 concat '-' concat a.apdp2 concat '-' concat a.apdv2 concat '-' concat a.apli2 concat '-' concat a.appj2 AS account#,
a.vendor AS "vendor#",
b.name AS "vendorname"
FROM
nfcclibf.ncheckshst AS a
JOIN nfcclibf.nvenmast AS b ON b.vendno = a.vendor
WHERE
(
a.duedat = '42921'
and apam2 <> 0
)
)
SELECT
voucher#,
invoice#,
invoicedate,
duedate,
amount,
account#,
vendor#,
vendorname
FROM
tempt
it looks like it is trying to do something!
Comment
-
You didn't want to try the VIEW approach?
For various technical reasons, if you put a bunch of UNION clauses inside a NAB data source it must convert it to a temporary table (tempt) inside a WITH clause. It should still work, but I think you'd find doing a SELECT from a view to be easier and cleaner to work with.
Comment
-
I would have to create the view as embedded in an rpg or something... total code I would write to accomplish the task in either scenario, would be essentially the same thing... probably more with the view... I was just trying it out in nab, but could not get it to work as it modified it... you are saying it is supposed to work?
Comment
-
The View would not be something you create and recreate in RPG; Rather you'd create it once (i.e., in a STRSQL session), typically placing it in the same library as the underlying physical file(s), and then use it in your data sources whenever you need it. Think of a View as a set of instructions that mimic the big UNION statement you're trying to create in the data source.
That said, yes, the tempt should work. I just think it's a less efficient approach to what you're trying to achieve.
Comment
-
You can create the view like this:
Code:create view your_library/new_view_name as ( select voucher#, vendor#, account#, amount from your_library/your_table as s, lateral(values (account#01, amount01), (account#02, amount02), (account#03, amount03)) as t(account#, amount) )
Comment
-
Very clever approach, Gloria! In fact I think using LATERAL might actually be a more efficient way to normalize the data over a bunch of UNIONs.
Here's another way to express that statement:
Code:CREATE VIEW your_library/new_view_name AS (SELECT a.voucher#, a.vendor#, x.account#, x.amount FROM your_table AS a CROSS JOIN LATERAL(VALUES (a.account#01, a.amount01), (a.account#02, a.amount02), (a.account#03, a.amount03)) AS x (account#, amount) WHERE x.account#>0)
I suppose you could simply plop the main SELECT statement right into your data source, but if you're going to use this normalized result elsewhere then having the View would come in handy.
Comment
Comment