| Join Problem in This Query [message #13243] |
Thu, 01 July 2004 10:52  |
Steve
Messages: 190
Registered: September 1999
|
Senior Member |
|
|
Hello All !
This query is working the way I want it to, except for one facet. It is returning more than one instance of the same installment number (decode(gmsi.installment_num...)) when I uncomment the pfv.total columns. Can somebody see a way to fix it? If no, then I suppose its off to find a correct view or make a new one.... sigh... I appreciate the time!
Steve
SELECT DISTINCT
gmsi.installment_num,
saa.award_number,
SUBSTR(GMSI.attribute5, 1, 4),
substr(GMSI.start_date_active, 4, 3) || ' ' || substr(GMSI.start_date_active, 8, 2),
decode(gmsi.installment_num, 'AC', GMSI.direct_cost, TO_NUMBER('0.00'))
--pfv.total_funding_amount,
--pfv.total_billed_amount,
--pfv.total_revenue_amount, --This is a temp value...need to get total expenditure costs
--(pfv.total_funding_amount - pfv.TOTAL_BILLED_AMOUNT) unpaid_oblig
FROM
GMS_SUM_PRJ_FUNDINGS_V PFV,
GMS_INSTALLMENTS GMSI,
GMS_status_award_accum_base_v saa,
(select di.award_id,sum(nvl(di.invoice_amount, 0)) invoiced
from GMS_draft_invoices_v di group by di.award_id) v
WHERE saa.award_id = v.award_id
and v.award_id = gmsi.award_id
and gmsi.award_id = pfv.award_id
and (gmsi.INSTALLMENT_ID = pfv.INSTALLMENT_ID OR gmsi.installment_num like '%AC%')
and saa.status = 'ACTIVE'
AND GMSI.AWARD_ID = 5377539
and saa.funding_source_id = (select customer_id from ra_customers where customer_name = 'FHWA')
ORDER BY saa.award_number
|
|
|
|
| Re: Join Problem in This Query [message #13253 is a reply to message #13243] |
Fri, 02 July 2004 00:02  |
 |
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
|
Senior Member |
|
|
you have used distinct. that's why it is giving extra rows.
for suppose, the data is like the following
1 1 1 1 1
1 1 1 2 2
1 1 1 1 1
1 1 1 2 2
if you select all the columns then 2 rows will be displayed.
1 1 1 1 1
1 1 1 2 2
if you select first 3 columns then only 1 row will be displayed.
1 1 1
bye
vamsi
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
