0

I'm getting a cartesian product that I don't want in a complex Informix SQL query.

I know which of the tables is causing the problem, but I can't figure out a way around the cartesian product.

The query I refer to is very long, but I am going to exemplify the problem with only two tables:

SELECT ROUND( SUM( CASE WHEN ccp.IdConcepto IN ( 60, 62 ) THEN ccp.Importe END ), 2 ) TotalInvoiced
    FROM Cbts cc, Cbts_Cpts ccp
    WHERE cc.CbteId = ccp.CbteId
    AND ccp.IdConcepto IN ( 10, 30, 31, 32 )

The table called Cbtes has a primary key called CbteId, which is not the primary key of Cbtes_Cptos. Cbtes_Cptos also has other columns, including IdConcepto (which I use in the query), IdSubConcepto, Unidades, Importe, etc.).

The problem is that in Cbtes_Cptos for the value 10 there are from 1 to 4 different records, and this difference is marked by the "IdSubConcepto" column, which can range from 1 to 4. The relationship can be the following:

Cbtes          Cbtes_Cptos
IdCbte        IdCbte  IdConcepto   IdSubConcepto
1               1        10              1
                1        10              2
                1        10              3
                1        10              4
                1        60              0
                1        62              0

So, the query returns the sum of several times the value of "Importe".

And I need to use the IdConcepto in the WHERE to speed up the query since it can have more than 5000 different values.

I hope I have explained the problem well, and I appreciate your attention.

Gustavo Echenique
  • 115
  • 2
  • 11
  • Why do you think its cartesian join if you join on primary keys? Also why predicates for case when and where differs? Also why there is no columns used from one of the tables? – 4yz Aug 19 '22 at 16:28
  • You've not shown example `cbtes_cptos.importe` values for the `cbtes.idcbte` value of `1`. Is it the same value for all the `IdSubConcepto` values? Also, it helps if you state which version of Informix you're using. `SELECT DBINFO('version','full') FROM "informix".SysTables WHERE TabID = 1` will give an answer on most plausible versions of the software. – Jonathan Leffler Aug 19 '22 at 20:53
  • It would help if you showed a minimal schema for the two tables and (say) two rows from `cbtes` and maybe 10 rows from `cbtes_cptos` that would be used with the data from `cbtes`. And you should show the query you're running against the tables in your question, and the result you get, and the result you want instead. – Jonathan Leffler Aug 19 '22 at 20:56
  • The SQL does not do a full cartesian product. It joins multiple rows because your joining condition is not selective enough, and the SUM computes the sum over all the rows selected. I'm pretty sure it's doing the job it is supposed to do. Yu just haven't told it accurately what the job you want it to do is, and I'm not completely clear on that either. – Jonathan Leffler Aug 19 '22 at 21:00
  • @JonathanLeffler: You are right, I did not specify the engine version, and it is 7.31 TD6. What I need the query to do is the following: add the IdConcept 60 and 62 (only as an example), only if the IdCbte also contains the IdConcept 10, 30, 31 or 32. Now add the value of each IdConcept 60 or 62 as many times as a concept appears 10, 30, 31, or 32 for the same IdCbte. – Gustavo Echenique Aug 19 '22 at 21:26
  • Whoa! IDS 7.31.TD6 runs on Windows. It has been out of service for, I think, over a decade. Please work on creating an MCVE ([Minimal, Complete, Verifiable Example](https://stackoverflow.com/help/mcve) — or MRE or whatever name SO now uses) or an SSCCE ([Short, Self-Contained, Correct Example](http://sscce.org/)) — the same idea by a different name. That will have the (minimal) versions of the two table schemas, some sample data for the two tables, the SQL query you're running (presumably that's already in the question), the result you get, the result you want. – Jonathan Leffler Aug 19 '22 at 21:58
  • If need be, you can contact me via email: see my SO profile. Please include the SO question number and Informix in the subject line. See also the guidance in the information for the [SQL](https://stackoverflow.com/tags/sql/info) tag. – Jonathan Leffler Aug 19 '22 at 21:59

0 Answers0