?
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
Medium Priority
?
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 41

Expert Comment

by:Sharath
ID: 24360535

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

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 41

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 41

Expert Comment

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

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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

764 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