Proposed Cartesian Product

Question

Proposed Cartesian Product

Navigation

#1 by (4 votes)

1

I need to create a purposive Cartesian product, for a report in Fast reports or multiply my data by a certain number EX:

select
  cli.cdclifor,
  CLI.nmCliFor
from
  cadclifor cli
where  cli.cdclifor = '000001'

result:

000001 Jõao das Couves

I'd like you to come back to me

result:

000001 Jõao das Couves
000001 Jõao das Couves
000001 Jõao das Couves

sql firebird

asked by anonymous 12.09.2014 / 22:48

1 answer

Phonegap / Connection with MySql Remote without PHP Select specific items within a while loop

score 4 · Accepted Answer

What I am going to write may seem absurd, but it is a common practice in techniques of Data Mining (in contrast to "normal" transactional systems where this would be considered a WTF):

Simply create a table to represent numbers, and make a Cartesian product ( cross join ) with this table:

create table numeros(
  numero integer
);

insert into numeros(numero) values(1);
insert into numeros(numero) values(2);
insert into numeros(numero) values(3);
...
(até onde você espera razoavelmente que você precisará consultar)

select
  cli.cdclifor,
  cli.nmCliFor
from
  cadclifor cli
  cross join numeros n
where  cli.cdclifor = '000001' and n.numero <= 3;

Example in SQLFiddle . It may be that there are better solutions (at least I hope so!) But unfortunately I do not know any of them ...