467,915 Members | 1,227 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,915 developers. It's quick & easy.

problem with the query

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
  • viewed: 873
Share:
2 Replies
deepuv04
Expert 100+
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
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.

Similar topics

20 posts views Thread by Development - multi.art.studio | last post: by
3 posts views Thread by Juan Antonio Villa | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.