Re: Rather Urgent SQL QUESTION

[Pete Hall <pbhall@xxxxxxxxxx>]

At 10:01 10/19/2000, Bart Verweijen wrote:
>Hi everybody,
>
>I am using a reportgenerator which uses odbc to connect to our as400.
>The generator uses SQL to get the data.
>
>I want to use the following statement (with interblock reference) to build 
>a kind of crosstab; but it doesn't work:
>
>select chl1uc,itnouc,(select sum(ofqsuc0101) from cognosanc.flinvbcum 
>where chl1uc=f.chl1uc and itnouc=f.itnouc and year='2000') as 2000
>from cognosanc.flinvbcsum f
>where chl1uc='K00069'
>group by chl1uc,itnouc;

I'd expect a sum function to look like this:

  SELECT CHL1UC, ITNOUC, SUM(OFQSUC0101) AS S2000
  FROM COGNOSANC.FLINVBCUM
  WHERE YEAR='2000'
    AND CHL1UC='K00069'
  GROUP BY CHL1UC, ITNOUC

If you need this to return a row for each row in the base table, you could 
create a view like the above and join it to flinvbcum on chl1uc and itnouc. 
SQL identifiers are required to begin with an uppercase alphabetic 
character, so "2000" is not a valid column name.

Look at the section on "subselect" in 
http://publib.boulder.ibm.com/pubs/html/as400/v4r4/ic2924/info/INFOCENT.HTM 
for some good examples of select syntax.
Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall/

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---