Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# SQL Problem

Posted on 2004-09-07
Medium Priority
168 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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

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.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month10 days, 11 hours left to enroll