Using a query as a function? (1 Viewer)

avisccs · Jul 18, 2018

Hi,

Can any one help of this please?

I have a fairly complex query (Q1) which outputs a calculated value from a table. To make it easier to understand, the calculation estimates the middle point between two dots in a scatter plot. To run this query, it asks for a 2 manual input values, say a and b, for the position of the two dots. These input values are within the formula in this query.

I have another query (Q2) which produces a and b for about 10000 different people. The row is people. a and b are two columns in this query.

My problem is: How do I produce a query/table which had the Q1 outputs for the 10000 people in Q2?

Minty · Jul 18, 2018

Make a copy of Q1 , then remove the two manual parameters.
Add Q2 and put the outputs (A & B) in place of the criteria in the new version of Q1.

Be warned this is effectively a cartesian join and may well take a considerable time to run.

avisccs · Jul 18, 2018

Minty said:
Make a copy of Q1 , then remove the two manual parameters.
Add Q2 and put the outputs (A & B) in place of the criteria in the new version of Q1.

Be warned this is effectively a cartesian join and may well take a considerable time to run.

Hi, Thank you for your reply, but I don't understand (1) why do I make a copy of Q1 and (2) how to remove the two manual parameters in Q1 function.

e.g. inside Q1's function, I have formula like: Result = a+b. And, some a and b are in the criteria, such as pick up c if a<1.

Even if I can move the formula from Q1, I still can't remove the inputs in criteria.

Do you want to see the full SQL of Q1?

Minty · Jul 18, 2018

Yes that would help - and/or maybe some sample data or even a striiped down db with a dozen records and some anonymised names / data ?

avisccs · Jul 18, 2018

Tb1 is like

Code:

          Grade    TimeDiff     P_neg

Code:

[FONT=Courier New]     1         200          0.1[/FONT]
[FONT=Courier New]     1         300          0.2[/FONT]
[FONT=Courier New]     1         500          0.3[/FONT]
[FONT=Courier New]     2         200          0.2[/FONT]
[FONT=Courier New]     2         300          0.4[/FONT]
[FONT=Courier New]     2         500          0.7[/FONT]
[FONT=Courier New]     3         200          0.3[/FONT]
[FONT=Courier New]     3         300          0.5[/FONT]
[FONT=Courier New]     3         500          0.9 [/FONT]

Q1 SQL is like:

Code:

 SELECT Max(Tb1.TimeDiff) AS TimeDiff1, Min(tb1_1.TimeDiff) AS TimeDiff2, Max(Tb1.P_neg) AS P_neg1, Min(tb1_1.P_neg) AS P_neg2,[

Code:

[FONT=Courier New][Enter TimeDiff num]-[TimeDiff1] AS P_negCalc,[/FONT]
[FONT=Courier New]IIf([Enter grade]=1, [Enter TimeDiff num]-[TimeDiff1]+[Enter size])),0)+IIf([Enter grade]=2, [Enter TimeDiff num]-[TimeDiff1]+[Enter size]+1)),0)+IIf([Enter grade]=3, [Enter TimeDiff num]-[TimeDiff1]+[Enter size]+2)),0) AS P4Given4U,[/FONT]
[FONT=Courier New]FROM tb1_1 INNER JOIN tb1_1 AS Tb1 ON tb1_1.Grade = Tb1.Grade[/FONT]
[FONT=Courier New]WHERE (((Tb1.TimeDiff)<=[Enter TimeDiff num]) AND ((tb1_1.TimeDiff)>[Enter TimeDiff num]) AND ((tb1_1.Grade)=[Enter grade])); [/FONT]

Manual inputs are: [Enter TimeDiff num], [Enter grade] and [Enter size]

Q2’s output is very much a table looks like:

Code:

 ID     Grade  size   TimeDiff

Code:

[FONT=Courier New]1      1      10     100[/FONT]
[FONT=Courier New]2      2      4      150[/FONT]
[FONT=Courier New]3      1      15     250[/FONT]
[FONT=Courier New]4      3      25     470[/FONT]
[FONT=Courier New]5      1      10     130 [/FONT]

Sorry that the post seems to automatically add '

Code:

 to my sentence...

Using a query as a function? (1 Viewer)

avisccs

New member

Minty

AWF VIP

avisccs

New member

Minty

AWF VIP

avisccs

New member

Similar threads

Users who are viewing this thread