problem with the query

[radhikams]

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

SELECT
DEAL_DETAIL.DEA L_ID, DEAL_DETAIL.CUR RENCY_CODE,DEAL _DETAIL.RATE,
DEAL_DETAIL.CUR RENCY_AMOUT,DEA L_DETAIL.SETTLE MENT_AMOUNT,
convert(varchar , DEAL_DETAIL.CRE ATED_DATE , 120) CREATED_DATE ,
CUSTOMER.COMPAN Y_NAME,PAYMENT_ FUNDS_IN.AMOUNT ,
(select sum(amount)as total from PAYMENT_FUNDS_I N ,DEAL_DETAIL where DEAL_DETAIL.DEA L_ID=PAYMENT_FU NDS_IN.DEAL_ID group by DEAL_DETAIL.CUR RENCY_CODE )
FROM DEAL_DETAIL,CUS TOMER ,PAYMENT_FUNDS_ IN
where DEAL_DETAIL.CUS TOMER_ID=CUSTOM ER.CUSTOMER_ID AND DEAL_DETAIL.DEA L_ID=PAYMENT_FU NDS_IN.DEAL_ID


In this DEAL_DETAIL , PAYMENT_FUNDS_I N 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_I N 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_CO DE,D_D.RATE,
D_D.CURRENCY_AM OUT,D_D.SETTLEM ENT_ AMOUNT,
convert(varchar , D_D.CREATED_DAT E , 120) CREATED_DATE ,
CUSTOMER.COMPAN Y_NAME,PAYMENT_ FUNDS_IN.AMOUNT ,
(select sum(amount)as total
from PAYMENT_FUNDS_I N ,
DEAL_DETAIL
where DEAL_DETAIL.DEA L_ID=PAYMENT_FU NDS_IN.DEAL_ID AND
DEAL_DETAIL.CUR RENCY_CODE = D_D.CURRENCY_CO DE )
FROM DEAL_DETAIL as D_D,CUSTOMER ,PAYMENT_FUNDS_ IN
where D_D.CUSTOMER_ID =CUSTOMER.CUSTO MER_ID AND D_D.DEAL_ID=PAY MENT_FUNDS_IN.D EAL_ID
[/sql]
Thanks

[radhikams]

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

[code=sql]
SELECT
D_D.DEAL_ID, D_D.CURRENCY_CO DE,D_D.RATE,
D_D.CURRENCY_AM OUT,D_D.SETTLEM ENT_ AMOUNT,
convert(varchar , D_D.CREATED_DAT E , 120) CREATED_DATE ,
CUSTOMER.COMPAN Y_NAME,PAYMENT_ FUNDS_IN.AMOUNT ,
(select sum(amount)as total
from PAYMENT_FUNDS_I N ,
DEAL_DETAIL
where DEAL_DETAIL.DEA L_ID=PAYMENT_FU NDS_IN.DEAL_ID AND
DEAL_DETAIL.CUR RENCY_CODE = D_D.CURRENCY_CO DE )
FROM DEAL_DETAIL as D_D,CUSTOMER ,PAYMENT_FUNDS_ IN
where D_D.CUSTOMER_ID =CUSTOMER.CUSTO MER_ID AND D_D.DEAL_ID=PAY MENT_FUNDS_IN.D EAL_ID
[/sql]
Thanks

Thank You very much that helped me a lot