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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
poor performance from  MySQL stored procedure 6 23
ASP.NET 5 Templates 2 66
IIS Authorization for Web Service 2 22
Oracle 10g - Select rows into colums 4 12
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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