CODEWITHSUNDEEP
sqldb2db2-400
KD_Raj
KD_Raj

Reputation: 337

SQL and IBM I systems

I have an SQL solution that retrieves similar quotenumber# from contractnumber# across 2 tables (QUOTES and CLIENTS) respectively. This works fine in any SQL Fiddle on the internet. See all the SQL below to create the solution ( I have added the create and insert tables statements just for easy of making the SELECT statement work):

CREATE TABLE QUOTES
(`quotenumber` varchar(10));

INSERT INTO QUOTES
(`quotenumber`)
VALUES
('10006'),
('10007'),
('10008'),
('10009');

CREATE TABLE CLIENTS
(`contractnumber` varchar(21));

INSERT INTO CLIENTS
(`contractnumber`)
 VALUES
('PRXQTE-10006'),
('PRXQTE-10007'),
('FRR-13'),
('JK1222222'),
('X'),
('');

This is the SELECT statement:

SELECT H.quotenumber
FROM QUOTES as H
INNER JOIN CLIENTS as P 
ON P.contractnumber LIKE CONCAT('%', H.quotenumber, '%');

However this SELECT statement does not work when I run this SQL in an IBM I environment. For example when I try to run the SELECT statement below in IBM I Access Client Solution's sql editor I get this error:

 SQL State: 42605 Vendor Code: -170 Message: [SQL0170] Number of arguments for function CONCAT not valid. Cause . . . . . :   A function has been specified with an incorrect number of arguments. If this function is meant to be a user-defined function reference, a function with this signature was not found using the current path. Recovery  . . . :   Correct the number of arguments specified for the function.  Refer to the DB2 for IBM i SQL Reference topic collection in the Database category in the IBM i Information Center for information on functions. If this is a user-defined function, correct the path or the function signature. Try the request again.

Does anyone know the DB2 adaptation of this SELECT Statement or what alternative will work with IBM i ?

Upvotes: 1

Views: 552

Answers (3)

iiSiggi
iiSiggi

Reputation: 175

You can use

SELECT H.quotenumber
  FROM QUOTES as H
  JOIN CLIENTS as P 
    ON P.contractnumber LIKE '%' || H.quotenumber, || '%';

but you shouldnt because it literally fucks up every index in your db. See this

Upvotes: 0

KD_Raj
KD_Raj

Reputation: 337

I think I found a solution that is a workaround and runs in DB2 (I added a second column to prove it retrieves the correct the correct corresponding quote#s):

SELECT H.quotenumber, P.contractnumber FROM QUOTES as H  
             INNER JOIN CLIENTS as P ON H.quotenumber = SUBSTRING( P.contractnumber, 8, 5 );

Upvotes: 0

sticky bit
sticky bit

Reputation: 37487

Hmm, according to the documentation sadly concat() in DB2 can only take (exactly) two arguments:

The CONCAT function combines two arguments to form a string expression.

You can work around that by nesting concat() calls like:

... concat(concat('%', h.quotenumber), '%') ...

That isn't beautiful but should work in DB2 and also in DBMS where concat() accepts an arbitrary number of arguments.

Upvotes: 4

Related Questions