NAB datasource SQL statement rules

[dlstrawn]

Is it possible to include more than 1 SQL statement in a data source definition?

[Johnny Major]

Hi David,

No a SQL data source can only be on single Select statement

[dlstrawn]

gotcha, thanks!

[robert.swanson]

Just curious, what are you trying to achieve? Sounds like perhaps a WITH clause would get you what you're looking for? That's where you create a temporary table (file) in a WITH-based SELECT, then pull from that temporary file and other files in your main SELECT to get your results.

[dlstrawn]

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

[robert.swanson]

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:

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)
)

[dlstrawn]

a union all will work with select statements, but I can't use but 1 select statement, right?

[robert.swanson]

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

[dlstrawn]

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!

[robert.swanson]

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.

[dlstrawn]

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?

[robert.swanson]

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.

[dlstrawn]

true, probably would cause less wait time...

[gzambrano]

You can create the view like this:

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)
)

[robert.swanson]

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:

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)

What's happening here is the CROSS JOIN LATERAL is essentially adding a series of extension columns (x) for each row selected from your_table (a).

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.