By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,415 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,415 IT Pros & Developers. It's quick & easy.

problem with the query

P: 49
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
Sep 29 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
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
Sep 29 '08 #2

P: 49
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
Sep 30 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.