• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

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

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
BlueKnight66
Asked:
BlueKnight66
  • 5
  • 5
  • 4
1 Solution
 
BlueKnight66Author Commented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Anthony PerkinsCommented:
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
 
SharathData EngineerCommented:

Can you explain what do you want with an example?
0
 
SharathData EngineerCommented:
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
 
BlueKnight66Author Commented:
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
 
SharathData EngineerCommented:
what is your sql server version?
0
 
BlueKnight66Author Commented:
2005
0
 
BlueKnight66Author Commented:
Correction........2000
0
 
SharathData EngineerCommented:
then why this question is in SQL Server 2005 zone.
SQL 200 doesnot support xml.
0
 
SharathData EngineerCommented:
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
 
Anthony PerkinsCommented:
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
 
BlueKnight66Author Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now