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.
In a SQL 2000 SP 1 installation I've a problem with a view.
When I run a select from this view with a where clause the view return me a result set, but if I insert a order by clause the result set is empty. I try to re-organize indexes and update statistics without results.
The server is on a client site so I can't update the SQL 2000 installation immediatly, I try to ask about the installation of the SP 3.
But in the meanwhile I need some information. Anyone see this problem before.
I've run into some trouble with an apparently simple UNION query:
SELECT [Date] AS D, C7, NULL AS L72A, NULL AS P02, PAV FROM [Process Variables Level 1] UNION SELECT [Date] as D, NULL AS C7, L72A, P02, NULL AS PAV FROM [Process Variables Level 2] ORDER BY D
Records in the 2 tables may or may not have the same Date/Time values.
The product of the above UNION query appears to be using dates from the [Process Variables Level 2] table only. I'm getting values returned for field C7 with Date/Time values from table [Process Variables Level 2]??
I'm trying to summarize costs assigned to active jobs for a manufacturing business. I need to aggregate work in process (WIP) cost that resides in labor-transaction and part-transaction tables based on transaction types, and transaction dates. Some transactions increase the WIP cost of the job while others decrease WIP. The business needs to see how much $$ is tied up in each job as of a particular date -- the calculation is: ToDate (cost of materials and labor assigned to job) - ToInv (cost of materials returned to inventory) - ToSales (cost of materials sold).
I developed this query incrementally and, so far, the #ToDate, #ToInv, and #ToSales temp tables seem to be populating with the correct data. My thought was to combine these three tables with a UNION and then extract the grand totals and here's where I started getting the following error: ------------------------------------------ Incorrect syntax near the keyword 'UNION'. ------------------------------------------ The problem is with the UNIONs going into #myTotal.
I would appreciate any help with this. Also, please let me know if you can suggest a better design for this. Thanks!
--M&S To Date SELECT pt.jobnum, SUM(pt.extcost) AS Cost FROM parttran pt JOIN jobhead jh ON pt.jobnum=jh.jobnum WHERE trantype IN ( <valid trans types> ) AND jh.JobReleased = 1 AND pt.TranDate < '2007-9-30' GROUP BY pt.jobnum
UNION -- This one works ok.
--L&B To Date SELECT jh.JobNum, sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs) AS Cost FROM LaborDtl l JOIN JobHead jh ON l.JobNum = jh.JobNum WHERE jh.JobReleased = 1 AND l.PayrollDate < '2007-9-30' GROUP BY jh.JobNum
SELECT pt.jobnum, SUM(pt.extcost) AS ToInv FROM parttran pt JOIN jobhead jh ON pt.jobnum=jh.jobnum WHERE trantype IN (<valid trans types>) AND jh.JobReleased = 1 AND pt.TranDate < '2007-9-30' GROUP BY pt.jobnum
SELECT pt.jobnum, SUM(pt.extcost) AS ToInv FROM parttran pt JOIN jobhead jh ON pt.jobnum=jh.jobnum WHERE trantype IN (<valid trans types>) AND jh.JobReleased = 1 AND pt.TranDate < '2007-9-30' GROUP BY pt.jobnum
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
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
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?
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...
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.)
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.
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
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:
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...
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.
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!
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!
lets say i have a table that looks like thisclient date amount account1 5/2/05 100 123451 5/2/05 110 123452 5/2/05 175 223342 5/2/05 10 22334How do I select all clients that have an 80 percent difference in amount for the same account on the same day. For example, only client 2 would be selected.
I have the following stored procedure in SQL 2000 and would like todiplay the database name where data is drawn from. I'm using 4databases 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)ASSelect a.Surname, a.GivenNamesFrom [db1]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db2]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db3]..Table1 As aWhere a.Surname LIKE @varSurname + '%'UNIONSelect a.Surname, a.GivenNamesFrom [db4]..Table1 As aWhere a.Surname LIKE @varSurname + '%'Order By a.Surname,a.GivenNamesGOI tried the followingSelect a.Surname, a.GivenNames, db_name()However it only gave me the name of the database where the storedprocedure is kept (in my case 'Common')I was hoping it would display results something like the followingSurname GivenNames Database------- ---------- --------Fred Smith db1Freddy Smith db2Fred Smith db3Fred Smithe db3Fred Smith db4Fred Smithye db4Instead I receiveSurname GivenNames Database------- ---------- --------Fred Smith commonFreddy Smith commonFred Smith commonFred Smithe commonFred Smith commonFred Smithye commonAny ideas?ThanksRick
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?
I have a table where one column is named 'Category'. I'm hoping to use a DataList to display a two column table, where each cell displays a column name and the number of records in that category. For example:Marketing (15) Finances (10)Bath & Body (3) Real Estate (7) Entertainment (6) Construction (2)I tried this:SELECT COUNT(DISTINCT Category) AS NumberFROM ResourcesWHERE (Approved = 1)I get this error:Column Resources.Category is invalid in the 'ORDER BY' clause because it is not contained in either an aggregate function or the Group By clause. When I try o add the GROUP BY I get a count of one for each category which isn't what I want. What am I doing wrong?Diane
I have several tables in a database which I always want to update with information from one table with new records (containing contact and demographical information). The setup is something like this:
NewRecordsTable: fn, ln, streetadd, city, emailadd, phonenumber, gender, birthdate
ContactTable: ID(primarykey), fn, ln, streetadd, city, state, zip, phonenumber, email
DemographicTable: ID(linked to primary key ID in Contact table), birthdate, gender
I want to update the ContactTable and DemographicTable with information from the NewRecords Table. What I have done so far is set the identity insert for the ContactTable to on, then inserted the fn, ln, streetadd, email, etc. from the NewTable. This works fine.
I then try to insert ID, birthdate and gender into the DemographicTable where NewRecordsTable.fn=ContactTable.fn AND NRT.ln=CT.ln AND NRT.streetadd=CT.streetadd AND NRT.emailadd=CT.emailadd - This mostly works, but the records which have NULL values any of those fields don't get inserted.
What I really want is to insert the records that have matching email addresses OR matching fn, ln, streetadd combos, but I can't figure out how to get that SELECT/WHERE statement to work.
The problem that underlies this is that I want to insert the ID values from the ContactTable into the DemographicTable, but the only way I can see to make them match properly is by matching the email addresses or fn, ln, streetadd combos from the NewRecordsTable to the ContactTable (all of the email addresses in our NewRecordsTable are unique, unless the person doesn't have an email address, in which case we make sure they have a unique fn, ln, streetadd combo)
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)
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?
I am doing a ‘Select into’ to make a table from at another table which has as many as 130 millions rows (its well indexed). The new table will most often have about 1000 rows. (During the running of the app, the app will be making many new tables, since hopefully this will be a ‘popular’ item by the users. After the users ‘use’ them, they are dropped.)
I know that locks are held on various system tables (including sysobjects) during this ‘select into’ process. Are they held for the entire process?
What should I be concerned about doing ‘select into’?
Trying to convert the following SELECT statement into a INSERT statement and having trouble. No doubt this will be a piece of cake to someone. To eventually get this to a trigger stage would be nice, but for the moment I'd settle for just plain SQL. Using MS SQL 2000. The database name is reporting. The table name is CallLog. I'm trying to convert seperate date (RecvdDate) and time (RecvdTime) columns into a single DateTime column. I've scoured a lot of web pages but I'm still lost.
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
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
