Solved

I need to add a field to my query that lists the results of a subquery (query returning more than 1 row)?

Posted on 2009-05-11
14
221 Views
Last Modified: 2012-05-06
Hello,
The query I have (see code window) works fine BUT I want to add one additional field that will list the outcome of a sub-query returning several rows.  The sub-query will grab the distributor names that have invoiced me from my Reconcile table (which is already in the main query) and will then list the names as a string to be used in one field.

See my query below and help me rewrite it to accomplish the above.

Thanks in advance.
select Order_no, orderdate, total as Cust_Total, Orders.cost as Our_Calculated_Cost, sum(p.tot_cost) as Disty_Invoiced, count(p.tot_cost) as Invoice_Count from Orders, Reconcile p where cast(orderid as nvarchar) = order_no group by order_no, orderdate, total, Orders.cost HAVING SUM(p.tot_cost) > cost order by order_no

Open in new window

0
Comment
Question by:BlueKnight66
  • 5
  • 5
  • 4
14 Comments
 

Author Comment

by:BlueKnight66
ID: 24360358
Hi Again,

I added the query as plain text for easier reading...thanks again

select Order_no, orderdate, total as Cust_Total, Orders.cost as Our_Calculated_Cost, sum(p.tot_cost) as Disty_Invoiced, count(p.tot_cost) as Invoice_Count from Orders, Reconcile p where cast(orderid as nvarchar) = order_no group by order_no, orderdate, total, Orders.cost HAVING SUM(p.tot_cost) > cost order by order_no
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24360450
Let me know if this is the approach you would like to follow:

select      o.Order_no,
            o.orderdate,
            o.total Cust_Total,
            o.cost Our_Calculated_Cost,
            p.Disty_Invoiced,
            p.Invoice_Count,
            p.Distributors
from      Orders o
            Inner Join (
                        Select      orderid,
                                    SUM(p.tot_cost) Disty_Invoiced,
                                    COUNT(*) Invoice_Count,
                                    dbo.udf_GetDistributors(orderid) Distributors
                        From      Reconcile
                        Group By
                                    orderid) p On o.order_no = cast(p.orderid as nvarchar(10))
Where      p.Disty_Invoiced > o.cost
order by
            o.order_no

If it is I can post the function dbo.udf_GetDistributors()
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24360454
Let's try that again as there is a small typo:
select      o.Order_no,
            o.orderdate,
            o.total Cust_Total,
            o.cost Our_Calculated_Cost,
            p.Disty_Invoiced,
            p.Invoice_Count,
            p.Distributors
from      Orders o
            Inner Join (
                        Select      orderid,
                                    SUM(tot_cost) Disty_Invoiced,
                                    COUNT(*) Invoice_Count,
                                    dbo.udf_GetDistributors(orderid) Distributors
                        From      Reconcile
                        Group By
                                    orderid) p On o.order_no = cast(p.orderid as nvarchar(10))
Where      p.Disty_Invoiced > o.cost
order by
            o.order_no
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24360463
Here is the function so that you can better judge the results:

ALTER Function [dbo].[udf_GetDistributors] (@orderid as nvarchar(10))

Returns varchar(2000)

As

BEGIN

Declare @Distributors varchar(1000)

Select      @Distributors = IsNull(@Distributors + ', ', '') + DistributorName
From      Reconcile
Where      orderid = @orderid

Return @Distributors

END
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24360535

Can you explain what do you want with an example?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24360548
i think you need a query like this. check this.

select Order_no, 
       orderdate, 
       total as Cust_Total, 
       Orders.cost as Our_Calculated_Cost, 
       sum(p.tot_cost) as Disty_Invoiced, 
       count(p.tot_cost) as Invoice_Count,
       (select rtrim(substring(isnull((select ','+Distributors from Reconcile r1 where r1.Orderid = r.Orderid for xml path('')),''),2,2000))) as Distributors
  from Orders, Reconcile p 
 where cast(orderid as nvarchar) = order_no 
 group by order_no, orderdate, total, Orders.cost 
HAVING SUM(p.tot_cost) > cost 
 order by order_no

Open in new window

0
 

Author Comment

by:BlueKnight66
ID: 24360571
Thank you both....I am trying both suggestions but running into issues on both...see below:

acperkins:
I'm getting this error:
The conversion of the nvarchar value '5033519001' overflowed an int column. Maximum integer value exceeded

Sharath 123:

I'm getting this error:

Line 7: Incorrect syntax near 'xml'
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24360584
what is your sql server version?
0
 

Author Comment

by:BlueKnight66
ID: 24360588
2005
0
 

Author Comment

by:BlueKnight66
ID: 24360592
Correction........2000
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24360611
then why this question is in SQL Server 2005 zone.
SQL 200 doesnot support xml.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24360640
Modified acperkins function like this and try.
ALTER Function [dbo].[udf_GetDistributors] (@orderid as nvarchar(15))
 
Returns varchar(2000)
 
As
 
BEGIN
 
Declare @Distributors varchar(1000)
 
Select      @Distributors = IsNull(@Distributors + ', ', '') + DistributorName
From      Reconcile
Where      cast(orderid as bigint) = cast(@orderid as bigint)
 
Return @Distributors
 
END

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24360705
You did not say what were the data types for Order_no and orderid:

If it is a Order_no is bigint and orderid is nvarchar(20) then:

select      o.Order_no,
            o.orderdate,
            o.total Cust_Total,
            o.cost Our_Calculated_Cost,
            p.Disty_Invoiced,
            p.Invoice_Count,
            p.Distributors
from      Orders o
            Inner Join (
                        Select      orderid,
                                    SUM(p.tot_cost) Disty_Invoiced,
                                    COUNT(*) Invoice_Count,
                                    dbo.udf_GetDistributors(orderid) Distributors
                        From      Reconcile
                        Group By
                                    orderid) p On o.order_no = cast(p.orderid as nvarchar(20))
Where      p.Disty_Invoiced > o.cost
order by
            o.order_no


ALTER Function [dbo].[udf_GetDistributors] (@orderid as nvarchar(20))

Returns varchar(2000)

As

BEGIN

Declare @Distributors varchar(2000)

Select      @Distributors = IsNull(@Distributors + ', ', '') + DistributorName
From      Reconcile
Where      orderid = @orderid

Return @Distributors

END
0
 

Author Comment

by:BlueKnight66
ID: 24360706
Sharath:  My apologies, I use 2005 tools for several DBs and had forgotten this one is 2000...our oldest.

ACperkins:  Yours worked.....the casting was on the wrong orderid...switched it around and it worked great...any thanks.

To both of you many thanks.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now