Solved

# SQL Problem

Posted on 2004-09-07
Medium Priority
170 Views
hi! everybody...well, my problem is the following:

I've got this table:

p      monto p            q      monto q
a      0,995      0            0,005       100.000,00
b      0,998      0            0,002       50.000,00
c      0,991      0            0,009       200.000,00

And need to create another table with the following results

pa pb pc      0,98407291      0
pa pb qc      0,00893709      200.000,00
pa qb pc      0,00197209      50.000,00
pa qb qc      0,00001791      250.000,00
qa pb pc      0,00494509      100.000,00
qa pb qc      4,491E-05                      300.000,00
qa qb pc      0,000010                       150.000,00
qa qb qc      9E-08             350.000,00

probability is for example> pa x pb x pc, and MontoTotal monto qa + monto pb + monto pc

I made theses tables in excel...but I would like to do all this job in paradox and quick sql, cause as you may have noticed with only 3 cases the resulting table had 8 rows (2 power 3). Imagine if there are 10 cases (2 power 10) or 1000 (2 power 1000).

so, is there any way to do this in sql??

paul.

pd: as I'm using delphi I thought of solving this problem using recursion...what do you think?? would it be faster using sql, if there is any chance to do so??
0
Question by:pin_plunder

LVL 13

Expert Comment

ID: 11998995
I think you could do this in SQL, but not in a Paradox table, since it wouldn't be a simple SQL query, but you could (probably) do it in a Stored Procedure
anyway... seems your option is to do it in Delphi... are you trying to find the permutations of

p and q with all the rows (a,b,c)???
0

Author Comment

ID: 12002918
Yes, that's right, I want to find all the permutation of p and q with all rows and get two fields for each one: Probabilidad, which is the result of the multiplication of each p or q, and MontoTotal which is the sum of the amount corresponding to each p or q (ie. if it's pa the amount is 0, if it's qa the amount is 5000, so I want to sum all these for each p or q that in the previous field I multiplied)

I already solved the problem using recursion in delphi...but this code is really slow....

how would you solve it using a stored procedure??? any implementation would be usefull....

thanks!
paul.
0

LVL 27

Expert Comment

ID: 12003481
usual it is not solveable with a sql-statement,
because its a crosstable-issue, which is not
supported by most databases, even not by paradox

>how would you solve it using a stored procedure???

try to use a reporting-system, which can build up a crosstab, like
TDecissionCube, QuickReport, Reportbuilder, etc.

meikl ;-)
0

LVL 4

Accepted Solution

mottor earned 1000 total points
ID: 12004200
Lets look at the problem binary.
If the first row is number 0, look at p as binary 0, look at q as binary 1,
on the row number 5 we have

qa pb qc  or binary  1 0 1 or decimal 5

We can use this to find if we hawe q or p on certain position:

uses math;
{\$R *.dfm}
var
p: array[0..2,0..1] of double = ((0.995,0.005),(0.998,0.002),(0.991,0.009));
s: array[0..2,0..1] of double = ((0.0,100000.00),(0.0,50000.00),(0.0,200000.00));
r: array[0..1,0..7] of double;
procedure TForm1.Button1Click(Sender: TObject);
var i,j :integer;
pp,ss: double;
n,m: word;
begin
for i := 0 to 7 do begin
pp := 1.0; ss := 0.0;
n := i;
for j := 2 downto 0 do begin
divmod(n,2,n,m);                //-- Here we are finding  q or p
pp := pp*p[j,m];
ss := ss+s[j,m];
end;
r[0,i] := pp;
r[1,i] := ss;
end;
for i := 0 to 7 do
end;

Regards,
mottor
0

LVL 10

Expert Comment

ID: 12010362
With a good SQL database you could use the cross join option.

First make the table like this:

name=tab(fields t,i,p,m)
t=a i=p p=0,995 m=0
t=a i=q p=0,005 m=100
t=b i=p p=0,998 m=0
t=b i=q p=0,002 m=50
t=c i=p p=0,995 m=0
t=c i=q p=0,001 m=200

select
a.i + a.t + ' ' + b.i + b.t + ' ' + c.i + c.t as Caso,
a.p * b.p * c.p as Probabilidad,
a.m + b.m + c.m as MontoTotal
from
tab a cross join tab b cross join tab c
where
a.t = 'a' and b.t = 'b' and c.t = 'c'

This should work with SQL Server for example

Regards Jacco
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.