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
219 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility

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

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 40

Expert Comment

by:Sharath
Comment Utility
what is your sql server version?
0
 

Author Comment

by:BlueKnight66
Comment Utility
2005
0
 

Author Comment

by:BlueKnight66
Comment Utility
Correction........2000
0
 
LVL 40

Expert Comment

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

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

11 Experts available now in Live!

Get 1:1 Help Now