Help With SQL Statement

[jtrapat1]

I need help writing a sql statement against some db2 tables.
I think I can write this with a single sql statement but if i can't please let me know.
There are four columns but the role column will be analyst and deputy.
I'd like to get a single row made up of one unique agency.
Here are the results I'm getting:
-----------------------------------------
AGENCY DESC DEPY ROLE ANAL ROLE
01000 exec DEPY DEPY
01000 exec ANAL ANAL
-----------------------------------------
I'd like to get a single row like this:
------------------------------------------
AGENCY DESC DEPY ROLE ANAL ROLE
01000 exec DEPY ANAL
------------------------------------------
Here's the sql statement I used-
it's a join of two tables linked on a common field:
My role column will have many to one application.
--------------------------------------------
SELECT t1.agency_code, t1.agency_desc, t2.role, t2.role
FROM nysa.bp02t_agency t1,
nysa.bp91t_end_update t2
WHERE t2.parent_code = t1.agency_code
AND t2.budget_yr = t1.budget_yr
AND t2.budget_yr = :bp91.budget_yr
AND t2.application = :bp91.application
--------------------------------------------
Can I use a subselect to get what I want?
Here are my table layouts if it will help.
----------------------------------------------
// bp02t_agency
//
struct
{
short budget_yr;
char agency_code[5+1];
char parent_code[5+1];
short chapter;
char agency_desc[80+1];
char agency_abbr[6+1];
char apprbill_code[3+1];
char subcom_code[2+1];
short fin_cat_code;
sqlint32 seq;
char status[1+1];
char edit_userid[8+1];
char edit_tstamp[27];
char ybh[1+1];
char approp[1+1];
} bp02;
------------------------------------------
// bp91t_end_update
//
struct
{
short budget_yr;
char role[4+1];
char parent_code[5+1];
char application[10+1];
char start_tstamp[27];
char end_tstamp[27];
char edit_userid[8+1];
char edit_tstamp[27];
} bp91;
------------------------------------------------
Thanks in Advance
John

 

[MarcLodge]

Hi John,
What platform are you running this on, and do you have any reporting software that might be able to massage the result? ie. what application is receiving the result of your SQL?

Marc

 

[jtrapat1]

MarcLodge,
Thanks for the response -

I figured out how to link the table to itself (by alias) and it's working.

Thanks
John