[SOLVED] [sql] matching records from 2 tables with multiple fields

hi, i am wanting to pull records from a query like so:

table-1:
claim-prefix claim-id claim-segment-number member-id
s            al3r     0                    chun-li
a            h3l0     1                    ryu
...

table-2:
claim   date
sal3r00 2020-02-18-12:00:00
msux005 2020-02-18-13:00:00
...

and i wanna':

select claim, member-id, date
from table-1, table-2
where claim = 'sal3r00'
and claim-prefix+claim-id+char(claim-segment-number) = claim
order by date desc
with ur;
--------------------
sal3r00 chun-li 2020-02-18-12:00:00

but i cant punch up the correct syntax ?

in db2-sql, seems like concat() only allows for two arguments.

this worx:

AND claim-prefix CONCAT claim-id CONCAT LPAD(claim-segment-number,2,'0') = claim

I don't know anything about DB2, but with other database systems doing that type of comparison prevents indexes being used, so if this system is expected to scale you might want to just create a claim column in table-1 instead, and perform the concatenation on insert.

Originally Posted by boughtonp I don't know anything about DB2, but with other database systems doing that type of comparison prevents indexes being used, so if this system is expected to scale you might want to just create a claim column in table-1 instead, and perform the concatenation on insert.

bon idée:

Originally Posted by schneidz i agree, the mainframe dataset is pulled from db2, vsam, i.d.m.s., websphere mqueues and other datasets. to get an additional column added, business solutions delivery would have to create a service request; then, app-dev would need to design it; and, enterprise release management would need to approve the budget.

Build a temporary table which includes the claim column, then JOIN on that table to get the result.

Not certain of db2 syntax but I know it supports temporary tables, something like this in pseudo-sql:

CREATE TEMPORARY TABLE tmpclaim(
    SELECT CONCAT(claim-prefix, claim-id, claim-segment-number) AS claim, member_id FROM table-1
);

CREATE INDEX claimindx(claim) on tmpclaim;

SELECT claim, member_id, date 
FROM tmpclaim JOIN table-2 USING(claim) 
WHERE claim='sal3r00';

But if the database uses those columns as a compound key in one table, then uses their concatenation as a key into another table, the data model is in need of a little normalization (which the temporary table provides, more or less, temporarily).

Originally Posted by schneidz to get an additional column added, business solutions delivery would have to create a service request; then, app-dev would need to design it; and, enterprise release management would need to approve the budget.

Or you just get friendly with a DBA.

Originally Posted by boughtonp Or you just get friendly with a DBA.

i once had to request extra space from storage management and their quote was cost per gb is $2.32 per month.
i factored this into $2,320/month -> $27,840/year -> $278,400/decade. i pointed out that a 1 tb sd-card on newegg costs like $45.

DB2 does have some string functions. If you know the length of each component of the claim string you could separate it into its constituent parts using SUBSTR() and select on the fields.