problem with the query

[radhikams]

Hi
I have query here through which Im actually trying to prepare a report .

SELECT
DEAL_DETAIL.DEAL_ID, DEAL_DETAIL.CURRENCY_CODE,DEAL_DETAIL.RATE,
DEAL_DETAIL.CURRENCY_AMOUT,DEAL_DETAIL.SETTLEMENT_ AMOUNT,
convert(varchar , DEAL_DETAIL.CREATED_DATE , 120) CREATED_DATE ,
CUSTOMER.COMPANY_NAME,PAYMENT_FUNDS_IN.AMOUNT,
(select sum(amount)as total from PAYMENT_FUNDS_IN ,DEAL_DETAIL where DEAL_DETAIL.DEAL_ID=PAYMENT_FUNDS_IN.DEAL_ID group by DEAL_DETAIL.CURRENCY_CODE )
FROM DEAL_DETAIL,CUSTOMER ,PAYMENT_FUNDS_IN
where DEAL_DETAIL.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID AND DEAL_DETAIL.DEAL_ID=PAYMENT_FUNDS_IN.DEAL_ID


In this DEAL_DETAIL , PAYMENT_FUNDS_IN and CUSTOMER are three different tables
I have no problem with the main query.But in my report I have show the total of amount for each currency
so I wrote the sub query.when it is executed separatley it works fine but when i put as a part of main query im getting the following error
deal_id is the common column in DEAL_DETAIL , PAYMENT_FUNDS_IN and CUSTOMER

please suggest

Thanks In Advance

[deepuv04]

Hi,
Try the following query probably will work for you,

[code=sql]
SELECT
D_D.DEAL_ID, D_D.CURRENCY_CODE,D_D.RATE,
D_D.CURRENCY_AMOUT,D_D.SETTLEMENT_ AMOUNT,
convert(varchar , D_D.CREATED_DATE , 120) CREATED_DATE ,
CUSTOMER.COMPANY_NAME,PAYMENT_FUNDS_IN.AMOUNT,
(select sum(amount)as total
from PAYMENT_FUNDS_IN ,
DEAL_DETAIL
where DEAL_DETAIL.DEAL_ID=PAYMENT_FUNDS_IN.DEAL_ID AND
DEAL_DETAIL.CURRENCY_CODE = D_D.CURRENCY_CODE )
FROM DEAL_DETAIL as D_D,CUSTOMER ,PAYMENT_FUNDS_IN
where D_D.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID AND D_D.DEAL_ID=PAYMENT_FUNDS_IN.DEAL_ID
[/sql]
Thanks

[radhikams]

Hi,
Try the following query probably will work for you,

[code=sql]
SELECT
D_D.DEAL_ID, D_D.CURRENCY_CODE,D_D.RATE,
D_D.CURRENCY_AMOUT,D_D.SETTLEMENT_ AMOUNT,
convert(varchar , D_D.CREATED_DATE , 120) CREATED_DATE ,
CUSTOMER.COMPANY_NAME,PAYMENT_FUNDS_IN.AMOUNT,
(select sum(amount)as total
from PAYMENT_FUNDS_IN ,
DEAL_DETAIL
where DEAL_DETAIL.DEAL_ID=PAYMENT_FUNDS_IN.DEAL_ID AND
DEAL_DETAIL.CURRENCY_CODE = D_D.CURRENCY_CODE )
FROM DEAL_DETAIL as D_D,CUSTOMER ,PAYMENT_FUNDS_IN
where D_D.CUSTOMER_ID=CUSTOMER.CUSTOMER_ID AND D_D.DEAL_ID=PAYMENT_FUNDS_IN.DEAL_ID
[/sql]
Thanks

Thank You very much that helped me a lot