CODEWITHSUNDEEP
sqlinformix
Threadid
Threadid

Reputation: 750

How to use Sub Query with IBM Informix 10.0

RDBMS is INFORMIX 10.0

SELECT owner FROM systables WHERE TABNAME= ' VERSION';

Returns 9.50C1 (which equates to version 10.x)

The Sub Query works just fine:

SELECT acct.fund_acct_nbr, acct.bin, prod.issuer_id, COUNT(*)
FROM fund_acct AS acct
JOIN products AS prod ON acct.cusip = prod.cusip
WHERE prod.issuer_id = 'xxxx'
AND SUBSTR(acct.bin, 1, 1) = 'x'
GROUP BY acct.fund_acct_nbr, acct.bin, prod.issuer_id;

I want to use this Query as a Sub Query.

So my question is:

How do I get this done on IBM INFORMIX v10.0?
Should be simple and straight forward right?

Any attempt to use that query as a Sub Query generates this meaningful message:

"Failed queries => 2"

INFORMIX - No "WITH" clause so this will not work:

WITH issuer_accts AS
(
SELECT acct.fund_acct_nbr, acct.bin, prod.issuer_id, COUNT(*)
FROM fund_acct AS acct
JOIN products AS prod ON acct.cusip = prod.cusip
WHERE prod.issuer_id = 'xxxx'
AND SUBSTR(acct.bin, 1, 1) = 'x'
GROUP BY acct.fund_acct_nbr, acct.bin, prod.issuer_id
)
SELECT issuer_accts.issuer_id, COUNT(*)
FROM issuer_accts
GROUP BY issuer_accts.issuer_id;

Attempt to follow this example (IBM documentation):

SELECT issuer_id, COUNT(*)
FROM 
(
SELECT acct.fund_acct_nbr, acct.bin, prod.issuer_id, COUNT(*)
FROM fund_acct AS acct
JOIN products AS prod ON acct.cusip = prod.cusip
WHERE prod.issuer_id = 'xxxx'
AND SUBSTR(acct.bin, 1, 1) = 'x'
GROUP BY acct.fund_acct_nbr, acct.bin, prod.issuer_id
) issuer_accts
GROUP BY issuer_id;

Attempt to follow this example (specific to INFORMIX v10.0)

SELECT issuer_id, COUNT(*)
FROM 
TABLE(MULTISET(
SELECT acct.fund_acct_nbr, acct.bin, prod.issuer_id, COUNT(*)
FROM fund_acct AS acct
JOIN products AS prod ON acct.cusip = prod.cusip
WHERE prod.issuer_id = 'xxxx'
AND SUBSTR(acct.bin, 1, 1) = 'x'
GROUP BY acct.fund_acct_nbr, acct.bin, prod.issuer_id
)) 
GROUP BY issuer_id;

Upvotes: 0

Views: 1193

Answers (2)

Threadid
Threadid

Reputation: 750

Because this early version of Informix v10 does not support "Sub Query"/"Inline Query"/"Inner Query" then the conventional practice is to use TEMP tables to support complex sql statements. Therefore this is the working answer for this version v10 of IBM Informix:

SELECT acct.fund_acct_nbr, acct.bin, prod.issuer_id, COUNT(*)
FROM fund_acct AS acct
JOIN products AS prod ON acct.cusip = prod.cusip
WHERE prod.issuer_id = 'xxxx'
AND SUBSTR(acct.bin, 1, 1) = 'x'
GROUP BY acct.fund_acct_nbr, acct.bin, prod.issuer_id
INTO TEMP temp_issuer_accts WITH NO LOG;

SELECT issuer_id, COUNT(*) account_count
FROM temp_issuer_accts
GROUP BY issuer_id;

DROP TABLE temp_issuer_accts;

Temp tables survive only for the duration of the session - so make sure your connection remains open over the execution of multiple statements.

Relevant reference here:

INTO TEMP clause

Using the WITH NO LOG option

Explicit inserts with SELECT...INTO TEMP statements

Duration of temporary tables

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you just need an alias:

SELECT issuer_id, COUNT(*)
FROM (SELECT acct.fund_acct_nbr, acct.bin, prod.issuer_id, COUNT(*) as cnt
      FROM fund_acct AS acct JOIN
           products AS prod
           ON acct.cusip = prod.cusip
      WHERE prod.issuer_id = 'xxxx' AND SUBSTR(acct.bin, 1, 1) = 'x'
      GROUP BY acct.fund_acct_nbr, acct.bin, prod.issuer_id
     ) x
GROUP BY issuer_id;

But, under most reasonable assumptions about the data, you could also phrase this as:

SELECT issuer_id, COUNT(DISTINCT acct.bin || ' ' || prod.issuer_id)
FROM fund_acct AS acct JOIN
     products AS prod
     ON acct.cusip = prod.cusip
WHERE prod.issuer_id = 'xxxx' AND SUBSTR(acct.bin, 1, 1) = 'x'
GROUP BY issuer_id;

Upvotes: 1

Related Questions