Purposeful Cartesian product

Asked

Viewed 295 times

1

I need to create a purposeful Cartesian product, for a report on Fast Ports that is to multiply my data by a given number EX:

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

outworking:

000001 Jõao das Couves

I’d like you to return to me

outworking:

000001 Jõao das Couves
000001 Jõao das Couves
000001 Jõao das Couves
sql firebird

  • because it is expensive the data are only of the cadclifor, I use a JOIN when I have more than one table, until I tried to make one but n it worked very well

  • 1

    Try to make the question more complete, click on [Edit] and add the table structure and more details than you need, which increases the chance of a good answer. For example, what determines that 3 results come out instead of one?

  • would bring Jõao das Couves, Jõao das Couves Jõao das Couves,..... I will say the number of results but anyway and for a label in fast Reports

  • The following as it is for a Report in Fast Reports, and the same supports Script I will pass the number of times I want to repeat the value for the report, and create a script in Fast that makes one by adding UNION ALL select ... thus repeating the number of times I want, as soon as ready put here as it was.

1 answer

4


What I’m going to write may sound absurd, but it’s 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 that 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. There may be better solutions (at least I hope so!) but unfortunately I don’t know any...

Browser other questions tagged sql firebird

You are not signed in. Login or sign up in order to post.