Newbie Subquery Parameter Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dmeiser - 20 May 2006 14:37 GMT

Let me preface this by saying that I'm used to working with Access,
which is not real SQL but can be useful in a jiffy. Keeping this in
mind, be gentle.

Does DB2 support passing a subquery parameters from the global query?
For example:

Select myDate, ( Select Sum(column1) From table1 As t1 Where t1.myDate
= global.myDate ) ), ( Select Sum(column1) From table2 As t2 Where
t2.myDate = global.myDate ) From table1;

Thanks,
Dave

Reply to this Message

dmeiser - 20 May 2006 14:44 GMT

I realized I should have been a little more specific, here, so here's
what I need to do:

I need to sum a column in 4 or 5 different ways based on a number of
different parameters.

In Access, I would normally use a domain aggregate function and say
something like "DSum("Column_Name","tableName","Date = #" &
dateFromQuery & "#, rest of parameters here..."). However, a real SQL
doesn't support this, so I need to figure out how to do it using
subqueries or temp tables. And I don't think that my employer allows
temp tables.

Reply to this Message

Rhino - 20 May 2006 14:52 GMT

>I realized I should have been a little more specific, here, so here's
> what I need to do:
[quoted text clipped - 8 lines]
> subqueries or temp tables. And I don't think that my employer allows
> temp tables.

You still aren't being specific enough.

In what environment do you want to execute this SQL? From the command line?
From a script? In an application or stored procedure?

Also, what version of DB2 are you using and what operating system are you
running on? This can be relevant in some cases.

--
Rhino

Reply to this Message

dmeiser - 20 May 2006 15:24 GMT

And 10 minutes later I find my own answer:

Select myDate, ( Select Sum(col) From t1 As B Where A.myDate = B.myDate
And more parameters), (Select Sum(col) from t1 As C Where A.myDate =
C.myDate And more parameters) From t1 As A Where more parameters Group
By myDate

Reply to this Message

Serge Rielau - 20 May 2006 16:20 GMT

> And 10 minutes later I find my own answer:
>
> Select myDate, ( Select Sum(col) From t1 As B Where A.myDate = B.myDate
> And more parameters), (Select Sum(col) from t1 As C Where A.myDate =
> C.myDate And more parameters) From t1 As A Where more parameters Group
> By myDate

Or without the n-way join:
SELECT mydate, SUM(CASE WHEN <parameters> THEN col END),
SUM(CASE WhEN <moreparameters> THEN col END)
FROM t1 WHERE <global parameters>
GROUP BY mydate

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message