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
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