Displaying Database Name Within Select Within UNION

Jul 23, 2005

I have the following stored procedure in SQL 2000 and would like to
diplay the database name where data is drawn from. I'm using 4
databases db1, db2, db3, db4 which all have the same table (Table1)
with identical column names (Surname, GivenNames).

CREATE PROCEDURE [dbo].[x_searchwildcard] @varSurname VARChar(25)
AS
Select a.Surname, a.GivenNames
From [db1]..Table1 As a
Where a.Surname LIKE @varSurname + '%'
UNION
Select a.Surname, a.GivenNames
From [db2]..Table1 As a
Where a.Surname LIKE @varSurname + '%'
UNION
Select a.Surname, a.GivenNames
From [db3]..Table1 As a
Where a.Surname LIKE @varSurname + '%'
UNION
Select a.Surname, a.GivenNames
From [db4]..Table1 As a
Where a.Surname LIKE @varSurname + '%'
Order By a.Surname,a.GivenNames
GO

I tried the following
Select a.Surname, a.GivenNames, db_name()
However it only gave me the name of the database where the stored
procedure is kept (in my case 'Common')

I was hoping it would display results something like the following

Surname GivenNames Database
------- ---------- --------
Fred Smith db1
Freddy Smith db2
Fred Smith db3
Fred Smithe db3
Fred Smith db4
Fred Smithye db4

Instead I receive

Surname GivenNames Database
------- ---------- --------
Fred Smith common
Freddy Smith common
Fred Smith common
Fred Smithe common
Fred Smith common
Fred Smithye common


Any ideas?

Thanks
Rick

View 1 Replies


ADVERTISEMENT

Displaying Distinct Results From Union Query

Aug 31, 2000

I've got a union query (below)and it returns rows that have duplivate itemno's, descrip's, imsrp3's, and imsrp4's, while the remaining columns are not duplicate for the same row. An Excel report uses this query to populate itself and for a more visually appealing look, I'd like to skip the duplicated columns in the display. I'm not sure how to use the Distinct or Group by in this case, since technically I'm dealing with two separate queries, neither one separately returning any duplicate rows.
thanks for any suggestions...

~
select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco,
watrdj,wapddj,wauorg,wauser
from nowo
where nowo.wasrst <='40'
union
select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco,
watrdj,wapddj,wauorg,wauser
from nopo
where nopo.wasrst <='499'

View 1 Replies View Related

Stored Procedure Using UNION Joins Is Not Displaying Correctly... Can Someone Help Me With My Logic?

May 16, 2007

I have a stored procedure using UNION joins on three SQL queries.
Sadly, I'm only now learning how to use Stored Procedures, so if this is a really dumb question, please forgive me.  I'm not used to big UNION statements like this either... usually I'm just programming websites to serve information out pretty simply :)
I need to return one result set, sorted by date... one complete result per day.  eg: 5/15/2007 |  XX | XX | XX | XX | XX | XX |5/16/2007 |  XX | XX | XX | XX | XX | XX |5/17/2007 |  XX | XX | XX | XX | XX | XX |
Currently, when I run the query, I'm getting three separate date values for each date...
eg:5/15/2007 |  XX | XX | 00 | 00 | 00 | 00 |5/15/2007 |  00 | 00 | XX | XX | 00 | 00 |5/15/2007 |  00 | 00 | 00 | 00 | XX | XX |5/16/2007 |  XX | XX | 00 | 00 | 00 | 00 |5/16/2007 |  00 | 00 | XX | XX | 00 | 00 |5/16/2007 |  00 | 00 | 00 | 00 | XX | XX |etc
How do I fix this?  I've looked through my query ad naseum and don't see anything that sets me off as "wrong".
Here is the stored procedure if you can help.  I'd really really love the help!

C R E A T E  P R O C E D U R E  sp_ApptActivityDate
(@strWHERE  as varchar(500), @strWHERECANCELED as varchar(500))
as
exec ('SELECT   [date] AS Date, SUM(length) AS TotalSlots, COUNT(cast(substring(appointUniqueKey, 1, 1) AS decimal)) AS TotalAppts,  SUM(length * 5) / 60 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, 0 AS totalCancelSlots,  0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM         dbo.vw_ALL_ApptActivity ' + @strWHERE + '
UNIONSELECT    [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, SUM(length) AS TotalActiveSlots,  COUNT(cast(substring(appointuniquekey, 1, 1) AS decimal)) AS TotalActiveAppts, SUM(length * 5) / 60 AS TotalActiveSlotHours, 0 AS totalCancelSlots,   0 AS TotalCancelAppts, 0  AS TotalCancelSlotHoursFROM         dbo.vw_Active_ApptActivity' + @strWHERE + '
UNIONSELECT    [date] as DATE,  0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts,    0 AS TotalActiveSlotHours, SUM(length) AS totalCancelSlots, COUNT(cast(substring(AppointUniqueKey, 1, 1) AS decimal)) AS TotalCancelAppts,   SUM(length * 5) / 60 AS TotalCancelSlotHoursFROM         dbo.vw_CANCELED_ApptActivity ' + @strWHERECANCELED + '
ORDER BY dbo.vw_ALL_ApptActivity.[Date] '   )GO

View 12 Replies View Related

SELECT UNION SELECT Condition

Dec 14, 2006

let say i got such condition

INSERT INTO TABLE
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)
UNION
SELECT
WHERE XX NOT EXISTS (SELECT 1 FROM TABLE)

do you think that mssql will produce error or problem?
from what i heard it will.

View 1 Replies View Related

Sum A Union Select

Mar 21, 2008

Hi all
I'm new to SQL Server 2005 and its queries. I've created a DB that stores bonusses. For example, a person goes out with usually 4 other people, and does their work. The first person is the engineer, the second is the team leader, and the third and forth is the helpers. According to the number of notes they capture for that day, they get a bonus. if 3 notes has been done for the day, the enigeer gets 100 bucks per note, aka 300 bucks - 3 * 100 = 300. the team leader gets half of it, 150, and the helpers get each half of that, 75 and 75.
 in a select query i did the match/calculation
now i want to add everything up to get a total, 300+150+75+75 - how does one do it, and write it to a table? Note: The amount of people can change, the role of the person can change, so a helper can be a team leader or engineer as well, and the amount also changes depending on performance.DECLARE @BonusID int;
SET @BonusID = '1';


SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, b.Notes * a.Amount AS Total, bd.DateModified
FROM tblBonusDetails AS bd INNER JOIN
tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN
tblUsers AS u ON bd.UserID = u.UserID INNER JOIN
tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN
tblRoles AS r ON bd.RoleID = r.RoleID
WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Surveyor')

UNION

SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, (b.Notes * a.Amount)/2 AS Total, bd.DateModified
FROM tblBonusDetails AS bd INNER JOIN
tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN
tblUsers AS u ON bd.UserID = u.UserID INNER JOIN
tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN
tblRoles AS r ON bd.RoleID = r.RoleID
WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Team Leader')

UNION

SELECT bd.BonusDetailID, bd.BonusID, u.Name, u.Surname, r.Role, ((b.Notes * a.Amount)/2)/2 AS Total, bd.DateModified
FROM tblBonusDetails AS bd INNER JOIN
tblBonusses AS b ON bd.BonusID = b.BonusID INNER JOIN
tblUsers AS u ON bd.UserID = u.UserID INNER JOIN
tblAmounts AS a ON b.AmountID = a.AmountID INNER JOIN
tblRoles AS r ON bd.RoleID = r.RoleID
WHERE (bd.BonusID = @BonusID) AND (r.Role = 'Helper')

ORDER BY Total DESC

  










BonusDetailID
BonusID
Name
Surname
Role
Total
DateModified

-------------
-----------
------------------------
-----------------------
--------------------------
-------------
-----------------------

1
1
Riaan
de Lange
Surveyor
300
2008/03/21 14:17

2
1
Kobus
Vermaak
Team Leader
150
2008/03/21 14:17

3
1
Johan
Bester
Helper
75
2008/03/21 14:17

4
1
Pieter
Koen
Helper
75
2008/03/21 14:17 
 
How do i get the total for the bonusid = 1? which should be 600

View 6 Replies View Related

Union Select

Sep 9, 2006

I want to include product added date and time in my querry but getting this error "The number of columns in the two selected tables or queries of a union query do not match".


Code:

SELECT Products.*,ProdPics.* FROM Products INNER JOIN ProdPics ON Products.ItemID=ProdPics.ItemID WHERE
Products.ItemID = 4 UNION SELECT Date, Time FROM History WHERE ProdID = 4



What am i doing wrong ?

View 2 Replies View Related

IF SELECT UNION

Jul 23, 2005

Using SQL 2000...tblCustomer:CustomerID intCompanyName varchar(20)HasRetailStores bitHasWholesaleStores bitHasOtherStores bittblInvoiceMessages:MessageID intMessageText varchar(100)CustomerID intAllRetailStores bitAllWholesaleStores bitAllOtherStores bitAllStores bitIsActive bitThe Invoice Messages are text blocks which will be added to invoicesgoing out to customers. A customer can have Retail stores, Wholesalestores, and/or Other Stores. The messages can go to only thosecustomers with a specific type of store, or all customers, or to aspecific customer. It is important to note that a customer can have 1,2 or all 3 types of stores. Here are a couple of sample entries in theinvoice messages table:tblInovoiceMessages1,For Customers with Retail and Wholesale Stores,0,1,1,0,02,Only For Customer # 10,10,0,0,0,0....Attempt #1 (IF SELECT UNION SELECT)IF (SELECT TC.HasRetailDestinationsFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)UNIONSELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)Attempt #1 checks if the Customer has retail stores, and if it does,returns all messages for Retail Stores. The second Select statementchecks for all messages designated for that particular Customer. I useUnion to combine the tables (which have identical structures) and itworks great.Attempt #2 (IF SELECT UNION SELECT UNION IF SELECT)IF (SELECT TC.HasRetailStoresFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllRetailStores = 1)UNIONSELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.CustomerID = @CustomerID)UNIONIF (SELECT TC.HasWholesaleStoresFROM tblCustomer TCWHERE TC.CustomerID = @CustomerID) = 1SELECT *FROM tblInvoiceMessages IMWHERE (IM.IsActive = 1) AND (IM.AllWholesaleStores = 1)Attempt #2 is the same as Attempt#1 except that I attempt to Unionanother If Select query to the first two queries. This attemptgenerates:Server: Msg 156, Level 15, State 1, Line 12Incorrect syntax near the keyword 'IF'.I have tested each individual If Select statement, and they all returnproper results. However, anytime I attempt to Union more than 1 IfSelect statement together, I get the Msg 156 error. Is there somelimitation that I am not aware of?

View 4 Replies View Related

Displaying Select Results On The Page

Aug 28, 2006

I have the following select statement on my page: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>"
SelectCommand="SELECT emp_lname + ', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @emp_username)">
<SelectParameters>
<asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" />
</SelectParameters>I want to say "Welcome, emp_fullname" at the top of the page, but can't figure out how to write the results to the page.  I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things. I appreciate any help you can give.

View 1 Replies View Related

Bout Union Select....

Jan 25, 2007

can someone help me how can i access datas using union? or show my data in the gridview.....pls.....
coz i have 3 tables...
i need to output  the datas of the 3 tables in 1 gridview.
tables are: TT0001,TM0011,TM0001
TT0001 has syain_id(PK),time_in,time_out,year(PK),month(PK),day(PK)
TM0011 has office_name,office_id(PK)
TM0001 has syain_id(PK) office_id(PK),empl_date
the scenario is:
i have a combo box for office_name, and a textbox for imputting the date( 2007/01/23).when i click the button OK.
the time_in,time_out and syain_name of the person who is present in the choosen date(ex. 2007/01/23)  will be shown in my gridview_info.
i already have some codes but  it still confuse me and has many errors....
my code: 
GridView_info.Visible = True
        '//for odbc        Dim StrConn As String = "Dsn=MS_PKG01;UID=emiline;APP=Microsoft® Visual Studio® 2005;WSID=MSHNP200603;DATABASE=MS_PKG01;Trusted_Connection=Yes"        Dim MyConn As Odbc.OdbcConnection = New Odbc.OdbcConnection(StrConn)
        'Dim MyConn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MS_PKG01ConnectionString").ConnectionString)
        MyConn.Open()
        '//parsing 2        Dim MyString As String = TextBox_date.Text        Dim MyDateTime As DateTime = DateTime.Parse(MyString)        Console.WriteLine(MyDateTime)
       Dim stringQuery2 As String = "SELECT * from tempo_db"
       Dim SQLcommand2 As New Odbc.OdbcCommand("Create table tempo_db ( syain_name char(40),year char(4), month char(2), day char(2), in_hh int, in_mi int, out_hh int, out_mi int )", MyConn)        SQLcommand2.ExecuteNonQuery()
        Dim SQLcommand1 As New Odbc.OdbcCommand("Drop table tempo_db", MyConn)        SQLcommand1.ExecuteNonQuery()
        Dim da As New Odbc.OdbcDataAdapter("Select TT0001.Year,TT0001.Month,TT0001.Day,TT0001.in_hh,TT0001.in_mi,TT0001.out_hh,TT0001.out_min where TT0001.Year =" + MyString + " and TT0001.Month =" + MyString + " and TT0001.Day =" + MyString + " and TT0001.syain_id =" + TM0001.syain_id + " Union Select TM0001.syain_name where TM0001.syain_id =" + TT0001.syain_id + "  Union Select TM0011.office_name where TM0011.office_id =" + TM0001.office_id + "        Dim ds As New DataSet()        Dim foundrow As DataRow        Dim ds2 As New DataSet        'Dim temp_data_table As New DataTable
        GridView_info.DataSource = ds        da.Fill(ds, "TT0001")
        Dim sqldataadapter2 As New Odbc.OdbcDataAdapter(stringQuery2, MyConn)        sqldataadapter2.Fill(ds2, "tempo_db")        Dim date_ctr As Integer        date_ctr = 1
        While date_ctr <= Date.DaysInMonth(Now.Year, Now.Month)
            ds.Tables(0).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Year")}            'ds.Tables(1).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Month")}            'ds.Tables(2).PrimaryKey = New DataColumn() {ds.Tables(0).Columns("Day")}
            foundrow = ds.Tables(0).Rows.Find(date_ctr)            'foundrow = ds.Tables(1).Rows.Find(date_ctr)            'foundrow = ds.Tables(2).Rows.Find(date_ctr)
            Dim in_hh, in_mi, out_hh, out_mi As String
            If foundrow IsNot Nothing Then                in_hh = foundrow.Item("in_hh")                in_mi = foundrow.Item("in_mi")                out_hh = foundrow.Item("out_hh")                out_mi = foundrow.Item("out_mi")            End If
        End While
        GridView_info.DataSource = ds2        GridView_info.DataBind()        MyConn.Close()
    End Sub
 
/// ps: im having problem with my select statements.....
can someone help me analyze what i had written... and what is my mistake...
 
any help is greatly appreciated....
 thanks
nat!
 

View 1 Replies View Related

ADO.NET && SELECT ... UNION Issue???

Feb 3, 2007

Hi,
I have discovered something weird.
I prepared a dataset that consists of a table adapter which has a select command of type stored proc. My stored procedure performs a select on a table1 and then table2 using UNION. My stored proc is running perfectly in the SQL Management studio (SQL2005) - no questions.
In Visual Studio when I test my dataset querying my tableadapter I get a result that is not just a UNION but a join of empty columns (number of empty columns = number of columns from one of my tables) and then the result of select statement from my stored proc.
And then my asp.net code fails as well because my gridView is expecting only 3 columns but instead I am getting 6 (3 empty + 3 those I was expecting in the first place.)

View 2 Replies View Related

Union Select Problem

Feb 25, 2007

hi all....im having problem with union selection query....this is the situation...i have a details view with data source from two table in the sql server...both select query has same query string parameter,the thing is i dont know how to display the data from both table in the column in details view...here i paste the code.... 1 <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
2
3 <script runat="server">
4
5
6 </script>
7
8 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
9  <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
10 CellPadding="4" DataKeyNames="programID" DataSourceID="programdetailsSqlDataSource"
11 ForeColor="#333333" GridLines="None" Height="50px" Width="272px">
12 <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
13 <CommandRowStyle BackColor="#D1DDF1" Font-Bold="True" />
14 <EditRowStyle BackColor="#2461BF" />
15 <RowStyle BackColor="#EFF3FB" />
16 <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
17 <Fields>
18 <asp:BoundField DataField="namaprogram" HeaderText="Nama Program" SortExpression="namaprogram" />
19 <asp:BoundField DataField="lokasi" HeaderText="Lokasi" SortExpression="lokasi" />
20 <asp:BoundField DataField="tarikh" DataFormatString="{0:dd/mm/yyyy}" HeaderText="Tarikh"
21 HtmlEncode="False" SortExpression="tarikh" />
22 <asp:BoundField DataField="kapasiti" HeaderText="Kapasiti" SortExpression="kapasiti" />
23 <asp:HyperLinkField DataTextField="nama" HeaderText="Nama Staff" NavigateUrl="~/staffdetail.aspx?staffID={0}" DataNavigateUrlFields="staffID" />
24 </Fields>
25 <FieldHeaderStyle BackColor="#DEE8F5" Font-Bold="True" />
26 <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
27 <AlternatingRowStyle BackColor="White" />
28 </asp:DetailsView>
29   
30 <asp:SqlDataSource ID="programdetailsSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:blksConnectionString %>"
31 SelectCommand="SELECT * FROM [program] UNION SELECT staffID,nama,programID FROM [staff] WHERE ([programID] = @programID)">
32 <SelectParameters>
33 <asp:QueryStringParameter Name="programID" QueryStringField="programID" Type="Int32" />
34 </SelectParameters>
35 </asp:SqlDataSource>
36 <br />
37 </asp:Content> and here is the errors:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.  

View 6 Replies View Related

UNION: Select Order

Aug 31, 2007

i have 2 selects:select * from view_veiculos where nome_marc like '%fiat%' and ano='2001'union select * from view_veiculos where nome_marc like '%fiat%'when i execute it on sql server, i get the following results:id 1 _______ ano 2004id 2 _______ ano 2001the row with ano 2004 is before the row with ano 2001the problem is that id like it to be ordered following the select order, which means that 2001 should be displayed before 2004,like that:id 1 _______ ano 2001id 2 _______ ano 2004all the results from the first select from the query need to be placed before the results from the second query.how can i make it ?thanks for all

View 23 Replies View Related

After Union Select Distinct

Nov 14, 2007

That's about it (subject line), I have used UNION and now I want to select DISCTINCT from that resultset
Code:

SELECT c1 FROM t1
UNION
SELECT c2 FROM t2

That code gives me half of what I want.
I would like a list of the unique results.
I know I could use a TempTable and do the DISTINCT on that, but I'm hoping there is a more elegant way.


EDIT: The following code gives me the result I want:
Code:

CREATE TABLE #TempTable (
TempCol VARCHAR (20) collate database_default,
)

INSERT INTO #TempTable
SELECT c1 FROM t1
UNION
SELECT c2 FROM t2

SELECT * FROM #TempTable

DROP TABLE #TempTable

. . . but, can it be done without a TempTable??

View 2 Replies View Related

Select From UNION Into Table

Sep 21, 2005

What am I missing?

I have three tables "UNIONED" and I want the this inserted into a table.

INSERT INTO mytable (A, B, C, D, E)
SELECT A, B, C, D, E
FROM
(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)
GROUP BY A,B,C,D,E

This part alone works just like I want it:

(SELECT * FROM temp_PARTS1 UNION SELECT * FROM temp_PARTS2)
UNION
(SELECT A, B, C, D, E
FROM a_lot_of_parts)

I just want it inserted inte stated columns in my table.

I've stared so much at this I'm "homeblind", ie I can't see the forest because of all the trees...

View 7 Replies View Related

HELP With A Select/Union Statement

Mar 31, 2006

I have 3 tables One table is the order Table, Bill to table and ship to table
I have to Views created as followed

This query uses the Ship to table to pull the ship to information to the shipping system.
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);


This query uss the Bill to as the ship to inforamtion
SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;


I need this infroamtion in one table which I have done in the UNION statement as followed:
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
UNION ALL
SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;

Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.

NAMEADDR_1ADDR_2ADDR_3CITYSTATEZIPCODECOUNTRYSHIP_VIAID
DIEBOLD INC (4076A)ATTN: RANCE AARON343 MANOR DRPACIFICACA9404418932
DIEBOLD, INCOHUPS #88X08X18932

MY POINT: Is there a way to select a over all DISTINCT order ID.

Thank you for any help hope this make sense!

View 4 Replies View Related

Get Count From Union Select

Jul 20, 2005

Hi,I'm trying to get the count of rows from the union of several tables.My code is:select count(*) from (select * from #AdvSearch_Mainunionselect * from #AdvSearch_Atty)This will not get past the syntax check saying that the error occurs onthe final closing ")".Can someone tell me how to correctly write this?Thanks,Glen--------------------------Numbers 6:24-26----------------------------------------------------Numbers 6:24-26--------------------------*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

UNION ALL SELECT Statement Help

Aug 9, 2007

I am not completly sure if I have this posted in the right forum so if I don't just let me know and I will move it. Here is my problem. I need to be able to use the WHERE keyword more than once in one SELECT statement and have not been able to figure this out. I need to be able to first search for information under one column with the WHERE keyword like usual and then I need to be able to search the returned results with another WHERE keyword to narrow down the returned results. I tried writing two SELECT statements and joining them with a UNION ALL keyword like this:

"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.CNID = '1'" & _
"UNION ALL" & _
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.FDName = 'Car1"


This hasn't worked and I didn't expect it to. Everytime I run this code I get an Unhandled SqlExecption:

Invalid column name 'Towing'.

Can anyone help me with figuring out how to use the WHERE keyword more than once. I am using Visual Basic.Net with ADO.Net. Thanks!

View 5 Replies View Related

Displaying SELECT Query Results Conditionally...

Apr 7, 2008


Hello all,

I have a problem... I have a SELECT query I have used in my PHP report. It is as follows:

SELECT DISTINCT callref, CASE WHEN (stage.due_date < stage.completed_date) THEN 'SLA Breach' ELSE ' In SLA' END AS sla
FROM tableX....
WHERE call_status=open.....


I wish to search through all stages (i.e response, fix, end) of the calls logged in our database, & then return 'In SLA' for each call that had all its stages completed within the SLA, & 'Breach' for all calls that had even one of its stages completed outside its SLA.

At the moment the SELECT query above gives me the following results:

Call Ref sla

10001 In SLA
10002 Breach
10002 In SLA
10003 In SLA
10004 In SLA
10005 Breach
10005 In SLA
... ...


What it should look like is this....

Call Ref sla

10001 In SLA
10002 Breach
10003 In SLA
10004 In SLA
10005 Breach
... ...


Please let me know if anyone has the answers or any clues to this! thanks.

View 5 Replies View Related

Select Query Union Trouble

Nov 26, 2007

Given the following tables:

[Members]
-memberID (PK)
-memberName

[Questions]
-questionID (PK)
-questionText

[Surveys]
-surveyID (PK)
-surveyName
-surveyDescription
-surveyType (FK)

[SurveyQuestions]
-surveyID (PK/FK)
-questionID (PK/FK)

[SurveyQuestionMemberResponse]
-surveyID (PK/FK)
-memberID (PK/FK)
-questionID (PK/FK)
-yesResponse(bit)
-noResponse(bit)
-undecidedResponse(bit)

How can I write a query to return the results for a given survey for all members (including members who have not given responses) given the surveyID.

In the [SurveyQuestionMemberReponse] table I record survey results for any members who have answered the survey. However, if a member has not responsed to the survey they will not have a record in this table.

I want to return a list of members with their response to each question in the survey. If a member has not given a response I would like to indicate they have not responded to the survey and they should still appear in the list.

When I attempt to write a query to UNION the results of a query aimed at gathering all of the results in the [SurveyQuestionMemberReponse] to all of the people in the [Members] table I recieve an error when I include the questionText field in my result set.

The error indicates:

The text data type cannot be selected as DISTINCT because it is not comparable.

Can someone please point me in the right direction. I suspect I am going about this all wrong.

[NOTE] The 'surveyType' in the [Surveys] table indicates which subset of members a given Survey should be available to. For this example let's just assume that every survey should belong to all members.

Thanks,

Zoop

View 3 Replies View Related

Insert With Union All Vs Multi Select

May 21, 2008

if there are 2 insert statement

insert ... select * from table1 union all select * from table2
or
insert ... select * from table1
insert ... select * from table2

pls advise which 1 is faster ...

View 14 Replies View Related

UNION SELECT From Multiple Subselects

May 14, 2007

I'm trying to get a UNION of UserIDs from multiple subselects. Theoriginal query--which puts all the found UserIDs into separatecolumns, looks like this:--------------------------------------------------------------------------------SELECTtmp_MY_TABLE_2_3_4.LAST_UPDATED_USER_ID AS UserID_1MY_DB_1.MY_TABLE_5.LAST_UPDATED_USER_ID AS UserID_2MY_DB_1.MY_TABLE_6.LAST_UPDATED_USER_ID AS UserID_3FROM(SELECT MY_DB_1.MY_TABLE_1.PRIMARY_KEY_IDFROM MY_DB_1.MY_TABLE_1WHERE MY_DB_1.MY_TABLE_1.PRIMARY_KEY_ID = 12345) AS tmpMY_TABLE_1LEFT JOIN(SELECT MY_DB_2.MY_TABLE_2.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_2.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_2UNIONSELECT MY_DB_2.MY_TABLE_3.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_3.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_3UNIONSELECT MY_DB_2.MY_TABLE_4.PRIMARY_KEY_ID, MY_DB_2.MY_TABLE_4.LAST_UPDATED_USER_IDFROM MY_DB_2.MY_TABLE_4) AS tmp_MY_TABLE_2_3_4ON tmpMY_TABLE_1.PRIMARY_KEY_ID = tmp_MY_TABLE_2_3_4.PRIMARY_KEY_IDLEFT JOINMY_DB_1.MY_TABLE_5 ON tmpMY_TABLE_1.PRIMARY_KEY_ID =MY_TABLE_5.PRIMARY_KEY_IDLEFT JOINMY_DB_1.MY_TABLE_6 ON tmpMY_TABLE_1.PRIMARY_KEY_ID =MY_TABLE_6.PRIMARY_KEY_ID--------------------------------------------------------------------------------As you can see, I'm getting "LAST_UPDATED_USER_ID" from all tables/aliases--but those tables/aliases need to LEFT JOIN to"tmpMY_TABLE_1.PRIMARY_KEY_ID", because I only care about"LAST_UPDATED_USER_ID"'s that are related to my "tmpMY_TABLE_1"records.I tried putting, parenthesis around the whole SQL (aliasing as"tmpAllTables") and putting in the front:--------------------------------------------------------------------------------SELECT tmpAllTables.UserID_1 AS UserID FROM tmpAllTablesUNIONSELECT tmpAllTables.UserID_2 AS UserID FROM tmpAllTablesUNIONSELECT tmpAllTables.UserID_3 AS UserID FROM tmpAllTables--------------------------------------------------------------------------------But that didn't work. Any thoughts?

View 1 Replies View Related

Ordering A UNION Select With Subquery

Jun 26, 2006

I have a select such as this:

select COLUMNS from TABLE where WHERE_CLAUSE1
union
select COLUMNS from TABLE where WHERE_CLAUSE2

Now, I want to order the result set by COLUMNS. When I try the following query, the SQL fails.

select * from
(
select COLUMNS from TABLE where WHERE_CLAUSE1

union

select COLUMNS from TABLE where WHERE_CLAUSE2

) as T
order by COLUMNS

Any idea how this can be done?

Thanks

View 4 Replies View Related

Displaying All On A Report When Select All Is Chosen From A Multi-value Parameter

Sep 28, 2007

Hi All

New user here. I've got a report with a couple of multi-value parameters and I'm displaying the selected values on the report using = Join(Parameters!ReportingGroup.Value, ", "). This works properly.

The users now want the report to display "All" if "Select All" is chosen, rather than showing the whole list.

Can anybody help me with this? I'm thinking of using an IIF statement in my expression, but have no idea how to define the condition?

Thanks in advance

Grant






View 3 Replies View Related

Multiple Order By Statements In Union Select

Jan 20, 2005

I'm new to SQL stored procedures, but I'm looking to be able to select order by statement.

declare @OrderBy
@OrderBy = 'first_name'

Select first_name,last_name from table1
Union
Select first_name,last_name from table2

if @OrderBy = 'first_name' begin
Order By first_name
else
Order By last_name
end

You'll have to excuse my if statement if the syntax is incorrect (used to only writing asp ifs :P). Thanks for any help

View 6 Replies View Related

Can I Make A Temp Table With A Union All Select?

Mar 26, 2008

I'm having trouble creating a temp table out of a select statement that uses multipe union alls.

Here's what I have, I'm trying to get the results of this query into a temp table...

select
parent,
(select cst_id from co_customer (nolock) where cst_key = Parent) as cst_id,
(select cst_name_cp from co_customer (nolock) where cst_key = Parent) as cst_name_cp,
(select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent) as Parent_Total_assets,
sum(own_assets) as Total_child_own_assets

from
(
Select parent,
Child,
(select org_own_assets_ext from dbo.co_organization_ext where org_cst_key_ext = child) as Own_assets

from
(Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,1) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,1) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,2) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,2) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,3) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,3) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,4) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,4) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,5) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,5) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,6) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,6) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,7) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,7) is not null )as c
) as d

group by parent

having sum(own_assets) <> (select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent)

View 8 Replies View Related

Union Select Of Two Tables And Join Of Another Table Afterwards

Apr 2, 2008

I have got the following union statement:


SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr,
FROM plan2008
GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr

UNION

SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
FROM fsp_auftrag
GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr


My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.

My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.

Is it possible to join the tables after the union took place?

Thanks in advance!

View 8 Replies View Related

Deadlock In View With Select Union - Creating A/S Dimension

Jul 23, 2005

I've got a view that creates a parent child relationship, this view isused in Analysis Services to create a dimension in a datastore. Thisquery tends to deadlock after about 10 days of running smoothly. Onlyway to fix it is to reboot the box, I can recycle the services for aquick fix but that usually only works for the next 1-2 times I call theview.This view is used to create a breakdown of the bill-to locations fromContinent-Global Region-Country-Sub Region-State/Province- City-ZipCodeYes, I know that sounds crazy, but it was a requirement.So why would I get a deadlock on a SELECT Query? Is there a way to setthe Isolation level to Repeatable Read for a view?Here is the view code:CREATE View dbo.vwBillToas-- US ZipCodeSelect 'Parent'=z.City+' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')',z.Zipcode_WK as 'Child',z.ZipCode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToWherez.US_Region_wk IS NOT NULLGroupby z.City,z.ZipCode_WK,US_Region_wk, z.State_shrtUnion--CitySelect 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) asvarchar)+')',z.City as 'Child',z.City + ' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')' as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zWherez.US_Region_wk IS NOT NULLGroupby z.State_Long,z.City,z.State_shrt,z.US_Region_wkUnion-- Canada ZipCodeSelect 'Parent'=z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') +')',z.Zipcode_WK as 'Child',z.Zipcode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion--CitySelect 'Parent'=z.Province_Long,z.City as 'Child',z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion-- Canada ProvinceSelect 'CANADA',Province_Long,Province_LongFromdbo.DIM_POSTAL_CODES_CANGroupby Province_LongUnion-- CountrySelect t.Region_NK,c.Country_Name,c.Country_NameFromdbo.DIM_COUNTRY cInnerJoin dbo.DIM_WORLD_REGION tOnc.Region_WK=t.Region_WKWherec.Country_Name Is Not NullGroup by t.Region_NK, c.Country_NameUnion-- SubRegionSelect c.Country_Name,sr.US_Region_Name,sr.US_Region_NameFromdbo.DIM_US_REGION srInnerJoin dbo.DIM_COUNTRY cOnsr.Country_wk=c.Country_WKGroupby c.Country_Name, sr.US_Region_NameUnion--RegionSelect sr.US_Region_Name,c.State_Long,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'Fromdbo.DIM_US_REGION srInnerJoin dbo.DIM_POSTAL_CODES_US cOnsr.US_Region_WK=c.US_Region_WKGroupby sr.US_Region_Name, c.State_Long,c.US_Region_wkUnion-- ContinentSelect Null,Region_NK,Region_NK[color=blue]>From dbo.DIM_WORLD_REGION[/color]WhereRegion_NK Is Not Null

View 1 Replies View Related

Out Of Range Datetime Value Error When Inserting Using Select...union

Mar 7, 2006

Hi all,I am getting this error when insert values from one table to another inthe first table the values are varchar (10). In the second they aredatetime. The format of the data is mm/dd/yyyy to be easily convertedto dates. The conversion in this case is implicit as indicated in SQLServer documentation. Here is my query:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionUNIONSELECT program_id + 100000, program_name, start_date, end_date,campaign_id AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.programsUNIONSELECT execution_id + 200000, execution_name, start_date,end_date, program_id + 100000 AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.executionsUNIONSELECT wave_id + 300000, wave_name, start_date, end_date,mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL ASListIDFROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executionsWHERE mysqlfactiva.dbo.waves.execution_id =mysqlfactiva.dbo.executions.execution_idI am referencing programs table two times. If I just select this all Iget all data I need. When doing insert I get a message:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value. The statement has been terminated.If I execute just first part of the query before first union, I insertdata fine:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionAs soon as I use union I get the above error. This is very strangesince even when I execute the query using first union where the datescome from the same table 'programs' I get the error. Why I can insertfrom programs first time and can's second time?Any help will be appreciated.Thanks,Stan

View 1 Replies View Related

Select A Subset Of Data From The Dataset Returned By A Union Query

Mar 30, 2004

hi all

Any day, another question. I love you guys.

I want to select a subset of data from a dataset returned by either another subquery or a union.

e.g. this is how i would do it in oracle, but i have no idea how this can be done in mssql or whether it's possible at all.

select * from
(
select col1, col2, col3 from table1
union
select col1, col2, col3 from table 2
)
where col1 = 'blah'

in essence oracle treats the data returned by the subquery as a table that it would select from.


how would i do the same in mssql?

thank you


James :)

View 5 Replies View Related

SELECT With UNION And DATEDIFF Does Not Work On SQL Server Compact Edition

Jan 24, 2008

Hi there,

i've tried using the SELECT SQL sentence on PPC with UNION and INNER JOIN in combination with DATEDIFF function which works fine on ordinary SQL but obviously does not work on compact edition. Is there some workaround?

SQL sentence that works:





Code Snippet

SELECT * FROM
(
SELECT 'O' Type, O.Name , OC.Name Contact, Birthday FROM OutletContact OC
INNER JOIN Outlet O ON OC.OutletID=O.OutletID
UNION
SELECT 'W' Type, W.Name,WC.Name Contact, Birthday FROM WholesalerContact WC
INNER JOIN Wholesaler W ON W.WholesalerID=WC.WholesalerID
) S
WHERE Birthday IS NOT NULL AND (DATEDIFF(day,GETDATE(),Birthday) BETWEEN 0 AND 14)


Thanks for any hint or solution to my problem,

Gorazd

View 1 Replies View Related

GridView Based On SQLServerDataSource Using A Select Union Statement, Impacts On Update And Insert?

Jan 3, 2006

I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):

SELECT    FirstName,    LastNameFROM    MasterUNION ALLSELECT    FirstName,    LastNameFROM    CustomORDER BY    LastName,    FirstName
I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom).  Any ideas if or how this can be done?  Specifically, I want the Custom table to be editable, but not the Master table.  Any examples or ideas would be very much appreciated!
Thanks,
Randy

View 5 Replies View Related

Using UNION With 2 SELECT Statements Gives 'Conversion Failed When Converting Datetime From Character String.'

Apr 30, 2008



Good day all.

Let's see if I can stump you with this one.....




Code Snippet
SELECT Account.New_DistributorAccountNumber, Account.New_NameonBill, ActivityPointer.Subject, ActivityPointer.CreatedOn AS ExprX
FROM Account INNER JOIN
ActivityPointer ON Account.AccountId = ActivityPointer.RegardingObjectId
UNION
SELECT Account_1.New_DistributorAccountNumber AS Expr1, Account_1.New_NameonBill AS Expr2, ActivityPointer_1.CreatedOn AS Expr19,
ActivityPointer_1.Subject AS Expr20
FROM Account AS Account_1 INNER JOIN
Contact AS Contact_1 ON Account_1.AccountId = Contact_1.AccountId INNER JOIN
ActivityPointer AS ActivityPointer_1 ON Contact_1.ContactId = ActivityPointer_1.RegardingObjectId







gives the error mentioned above. Each select statement, when run separately, works fine. Any clues?

Thank you in advance

View 4 Replies View Related

T-SQL (SS2K8) :: Using Union ALL Or Union Kills Performance On Stored Proc

Jun 12, 2014

SQL Server 2008 r2...

I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).

Any way to join the result sets together without using Union All.

Each result set has exactly the same structure returned...

Query below [for reference]...

WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]

[Code] .....

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved