Not Part Of DISTINCT Clause But I Do Want It Selected
I get the rows(records) I want by removing the RequestDateTime column from the Select query
SELECT distinct et.configname as 'Part Number',
substring(email,(charindex('@',email)+1),len(email)) as 'email ending',
u.company_name as 'Company',
u.city as 'City',
u.country as 'Country'
--,RequestDateTime as 'Date Downloaded'
from
new_products.dbo.modelrequests et
left outer join
cc_host.dbo.usr_list u
on
et.username = convert(varchar(50),u.[user_id])
where
interfacename like '%download%'
and
result=0
and
u.country = 'JP'
and
CONVERT(DATETIME, RequestDateTime, 102)>= convert(DATETIME,'12/5/2007', 102)
GROUP BY email,et.configname,u.company_name,u.city,u.country,requestdatetime
I want to show the RequestDateTime for the 1600 records that do get selected.
How can I put RequestDateTime back in the resulting table output without it being part of the Distinct clause?
View Complete Forum Thread with Replies
Related Forum Messages:
SELECT DISTINCT - But Just One Of The Selected Columns
Hi, I have a table with 3 columns - userID, score, dateCompleted From this table I wish to select the highest score for each userID and the date for that score. If there is more than one row with the same userID and score (i.e. the user got the same score twice), I want to select the earliest date. To clarify, there are multiple rows which have the same userID, and may have the same score too, but I want to pull out just 1 score for each userID. I could do this quite easily if the DISTINCT operator worked on a per column basis, but it works on the entire row instead. Is there a way to SELECT DISTINCT column, but still output other columns in those rows? I hope this is clear, but please let me know if I've confused you.
View Replies !
Ntext Data Type Cannot Be Selected As DISTINCT
Hello, When I am using DISTINCT in the SELECT clause for a table having a column of datatype "ntext" in SQL 2000 as well SQL 2005, it fails giving the error as "ntext data type cannot be selected as DISTINCT". This is coz "The text, ntext, or image data type cannot be selected as DISTINCT." But I am able to insert into a temporary table having a column whose datatype is nvarchar) using this SELECT clause in SQL 2000. Please advice me how this happens. If I am not worng, implicit conversion has happened. But this insert fails raising the same error in SQL 2005. Please help me to find the difference thanks in advance!
View Replies !
The Text Data Type Cannot Be Selected As DISTINCT Because It Is Not Comparable.
I'm having a problem building a query for an application I'm writing. Basically, there are a bunch of records in a table, and some of them may have the same identifier code. I only want one of each identifier code. The table looks like this: Memo --------- Msg_Identifier varchar(60) Memo_ID int (pk) Subject varchar(64) Message text Priority int Deleted int My query: SELECT DISTINCT Msg_Identifier, Memo_ID, Subject, Message, Priority FROM Memo WHERE Deleted = '0' When I run this query, I get "The text data type cannot be selected as DISTINCT because it is not comparable." I only want Msg_Identifier to be the distinct field, and it's obviously not set up as text. I've used SELECT DISTINCT in the past but I don't ever recall hitting an issue like this. What's the best way to do it?
View Replies !
GROUP By Clause Or DISTINCT Clause
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
View Replies !
Looking To Collect Distinct Date Part Out Of Datetime Field
from this, circdate being a datetime field:SQLQuery = "select distinct circdate from circdata order by circdate"I need the distinct date portion excluding the time part.this has come about when I discoveredI am inserting and updating some datetime values with the same value,but for some reason, the values are always off by a few seconds. I seta variable called SetNow assigned to NOW and then set the datetimefields to this SetNow variable. Then when I collect the distinct datetime I am assuming they will have the same values recorded incircdate, but no, they are off by several seconds. Makes no sense to meat all. I tried renaming the variable several times but it makes nodifference at all.any help appreciated, thanks.
View Replies !
How SQL Handles The OR Part In The WHERE Clause.
Hi, I have a Query like this and i am using fulltext search. Select * FROM [Search] WHERE (@limitByName = 0 OR CONTAINS([Name], @searchStringOR)) Say @limitByName is 0, will my second part still executes.??? How Sql server 2005 handles the OR part in WHERE clause.(Need detailed answer)..pls give any useful link to refer. How can i find this, if i use Actual Execution plan. Thanks in Advance, Vijay.
View Replies !
How Do I Exclude Carriage Returns As Part Of A IS NULL Exclusion Clause...
Hi I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return. I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work. Does anyone know how I can elegantly get around this problem other than my best guess below: Best guess pseudo code: IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL. Here's some code that reconstructs the problem: select datalength(char(13)) CarriageReturnVisible , datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work] Cheers - Andy
View Replies !
Error While Using OUTPUT Clause - The Multi-part Identifier Could Not Be Bound
I was trying to copy child records of one parent record into another, and wanted to report back new child record id and corresponding child record id that was used to create it. I ran into run-time error with OUTPUT clause. Following is a script that will duplicate the situation I ran into: CREATE TABLE Parent( ParentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentName VARCHAR(50) NOT NULL) GO CREATE TABLE Child( ChildID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ParentID INT NOT NULL REFERENCES Parent(ParentID), ChildName VARCHAR(50) NOT NULL) GO INSERT INTO Parent(ParentName) VALUES('Parent 1') INSERT INTO Parent(ParentName) VALUES('Parent 2') GO INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 1') INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 2') GO At this stage, there Child table looks like: ChildID ParentID ChildName 1 1 Child 1 2 1 Child 2 What I want to do is copy Parent 1€™s children to Parent 2, and report back which source ChildID that was used to create the new child records. So I wrote the query: DECLARE @LinkTable TABLE (FromChildID INT, ToChildID INT) INSERT INTO Child(ParentID, ChildName) OUTPUT c.ChildID, inserted.ChildID INTO @LinkTable SELECT 2, c.ChildName FROM Child c WHERE c.ParentID = 1 SELECT * FROM @LinkTable In the end I was expecting Child table to look like: ChildID ParentID ChildName 1 1 Child 1 2 1 Child 2 3 2 Child 1 4 2 Child 2 and OUTPUT clause to return me: FromChildID ToChildID 1 3 Child record with ID 3 was created using ID of 1. 2 4 Child record with ID 4 was created using ID of 2. But infact I€™m getting following error: Msg 4104, Level 16, State 1, Line 9 The multi-part identifier "c.ChildID" could not be bound. Any ideas on how to fix the OUTPUT clause in the query to return me the expected output? Thanks Yogesh
View Replies !
When Does Ssrs Use A DISTINCT Clause In It's Queries?
I am having a heck of a time figuring out what controls how/when the generated SQL for a report puts a DISTINCT clause in front of it. For instance, not that this report makes any sense, but I have 58 rows in my fact table/entity €“ If I pull in a lookup field and execute, the distinct is put in the query and I basically get a list of the possible domain values. It runs the whole joined table query to get them, but it does list (in this case) just 4 records. Now I put in the primary ID of the fact entity and the distinct goes away and I get my 58 rows. If I put two lookup fields, the DISTINCT is back. If I pull in the description field (text string just a direct source field mapping not part of the identifying attributes), the distinct is there. If I pull in the Company Name field on a different entity (which is essentially the same as pulling in Description only it is part of the identifying attributes), there is no DISTINCT. I can pull in all my fields on this entity and none of them drive the distinct. And I swear (ok, I am probably lying but not on purpose) the field/attribute and roles properties are all the same on the attributes. But you get my general question/situation... Any insight for me? Does it have to do with how I am building the report rather than the underlying model? Thanks in advance,
View Replies !
How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?
Hi, I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow... Can anyone help? Thanks in advance Gemma
View Replies !
Web Part Deserialization Error When Trying To Change Report Viewer Web Part Programmatically.
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site. I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part. While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as "Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again" If someone has a solution, please respond at your earlist. Thanks Shankar
View Replies !
Split A Decimal Number Into The Integer Part And The Fraction Part
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
View Replies !
Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?
Hi, I have the following script segment which is failing: CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude)) INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'." Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values. The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why. Any ideas?
View Replies !
Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.
I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks
View Replies !
DISTINCT To ShortDateString, Not DISTINCT To The DateTime; How?
Hello, I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime ) This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table). This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day. So far so good, and everything works fine except one thing: Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows) What I need to do is to get a result like (in a neat fashion :) "SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals" So, how to do it in a suitable way? Thanks in advance.
View Replies !
Filtering Results In The Where Clause Vs A Having Clause
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following. When on SQL Server 2000 the following statement ran without issue: UPDATE dbo.Track_ID SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed WHERE Processed = 0 AND LegNum = 1 AND TrackID IN ( SELECT TrackID FROM dbo.Track_ID GROUP BY TrackID HAVING MAX(LegNum) = 1 AND TrackID + 'x1' IN ( SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID)) FROM dbo.Track_ID INNER JOIN dbo.transactions ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id GROUP BY dbo.Track_ID.TrackID ) ) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time. I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same? UPDATE dbo.Track_ID SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed WHERE Processed = 0 AND LegNum = 1 AND TrackID IN ( SELECT TrackID FROM dbo.Track_ID WHERE TrackID + 'x1' IN ( SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID)) FROM dbo.Track_ID INNER JOIN dbo.transactions ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id GROUP BY dbo.Track_ID.TrackID ) GROUP BY TrackID HAVING MAX(LegNum) = 1 )
View Replies !
What Different Are There Between ConnectionString (Part 1) And ConnectionString (Part 2) In Web.config
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config The CCWW is my PC's name, normally I can connect to the database ASPNETDB.MDF correctly either Part 1 or Part 2 in a web page,After I open Database Explorer panel and browse ASPNETDB.MDF, I can't connect to database using Part 2 when I open a webpage in Microsoft Visual Web Developer 2005 Express Edition,but I can correctly open a webpage using Part 1 after I open Database Explorer panel. What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config? I guess while I use Part 1 to connect, maybe it will be cancel exclusive method of the database ASPNETDB.MDF first, but when I connect to database using Part 2, maybe two programms both Part 2 and Database Explorer visit ASPNETDB.MDF at the same time! --------------------------------------Part 1------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" /> --------------------------------------Part 1------------------------------------------------------------------------ --------------------------------------Part 2------------------------------------------------------------------------<add name="MyConnect" connectionString="Data Source=CCWWSQLEXPRESS;Initial Catalog=ASPNETDB.MDF;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />--------------------------------------Part 2------------------------------------------------------------------------
View Replies !
Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples: 1) Rows ordered using textual id rather than numeric id Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id Result set is ordered as: 1, 11, 2 I expect: 1,2,11 if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. 2) SQL server reject query below with next message Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique. Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. It reproducible on Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) and Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) In both cases database collation is SQL_Latin1_General_CP1251_CS_AS If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again. Could someone clarify - is it bug or expected behaviour?
View Replies !
If There Is No Picture Selected
Dear readers, On my page people can opload foto's with a <asp:FileUpload ID="myfile" runat="server" BorderWidth="3px" BorderColor="Silver" BorderStyle="Inset" /> and stored the filePath in the database Now i want, that when there is no picture selected, the path to Noimage.jpg picture who is in my image map will stored on my datadbase. my script start like this If Not (myfile.HasFile) Then ////how can i select a standard image from my imageMap and store path to database ///// Dim cnn As Data.SqlClient.SqlConnection Dim cmd As Data.SqlClient.SqlCommand Dim strSQL As String Dim connString As String = (ConfigurationManager.ConnectionStrings("Personal").ConnectionString) strSQL = "Insert Into tblMateriaal(ArtikelGroep,Artikelnaam,ArtikelType,ArtikelMaat,Aantal,Prijs,ContactPersoon,EmailAdress,Aanvul) Values(@ArtikelGroep,@ArtikelNaam,@ArtikelType,@ArtikelMaat,@Aantal,@Prijs,@ContactPersoon,@EmailAdress,@Aanvul)" cnn = New Data.SqlClient.SqlConnection(connString) cmd = New Data.SqlClient.SqlCommand(strSQL, cnn) Dim plaatje As New Data.SqlClient.SqlParameter("@plaatje", System.Data.SqlDbType.NVarChar) plaatje.Value = " " cmd.Parameters.Add(plaatje) Dim ArtikelGroep As New Data.SqlClient.SqlParameter("@ArtikelGroep", System.Data.SqlDbType.NVarChar) ArtikelGroep.Value = ddl1.SelectedValue cmd.Parameters.Add(ArtikelGroep) Dim ArtikelNaam As New Data.SqlClient.SqlParameter("@ArtikelNaam", System.Data.SqlDbType.NVarChar) ArtikelNaam.Value = tb1.Text cmd.Parameters.Add(ArtikelNaam) cnn.Open() cmd.ExecuteNonQuery() cnn.Close() End if Lots of thanks
View Replies !
Update From Selected Row
Hi, I am having a problem updating one field in a table the update should be the product of two other fields from the same row. There are atleast 3000+ records need to be updated here. e.g. update A set A.b = A.c * A.D here b c and d are from same row .. I was wondering if someone knows how to solve this problem. Thanks in advance.
View Replies !
Dts Only Selected Rows
I have to dts rows by timestamp. For example if my dts downloaded at 10 am then in the next run i want to grab rows updated in the AS400 after 10am. what is the best way to go in ssis? thanks... kushpaw
View Replies !
No Cube Selected
Hello, I've gotton both the sprocs in these tutorials to work in the C# app: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/4503.aspx http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/2271.aspx But when I try to call them in the Managment Studio I get the following error: "No cube specified. A cube must be specified for this command to work." What am I missing here? Thanks in advance, Adam
View Replies !
Selected Value Of Dropdown
How would I reference the selected value of a dropdown parameter in SSRS using VS2005? For instance, the city Miami is selected I want to find out if both dropdowns match eachother... iif(dropdown1.selected.value = dropdown2.selected.value,false,true)
View Replies !
Selected Record's Id
How can I get an Id of selected record? something like Code Snippet DECLARE @Id uniqueidentifier SELECT @Id = ID FROM DataTable BUT! I need also retrieve a data in the same query. Is that possible or there is a different way? Thanks.
View Replies !
Selected Backup
I need to get the Bacup of my SQl 2000 database but i need only last 100 records of all tables in my database. or i want to create new database from existing , schama is same but i need to import only 1000 record from all tables
View Replies !
Formatting A Selected Value
I am new to writing sprocs so forgive me if this is trivial. I am selecting fields from a table and placing them into a temp table in row format. (Row 1 in temp table is the first row in a file that will be created using DTS package). My question is: How can a format a field that I have selected that only has, say 3 chars, into a value of 5. Ex: field in DB = aaa I need to format it as: 2 spaces + aaa But the length of the value will be varying from record to record.
View Replies !
WildCard If Not Value Selected
HI Guys, I have a question. I am converting Access SQL to SQL Server. One of the statements calls for a wildcard if the user does not select a value for the designated parm field. The value is selected from a cbolist (of names). Current Statement: And tblRetailer_Contact.faxcontact LIKE * I substituted: And tblRetailer_Contact.faxcontact LIKE ‘%@faxContacts%’ This might work if the User selects a name but if the User leaves it blank it will not work. Any ideas on how I go about establishing a wildcard if not name is selected? DECLARE @FaxContact as varchar (50) SET @H_Date = (SELECT StartDate FROM tblRpt_Params WHERE RptID = 5) SET @Start_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', '')) SET @H_Date = (SELECT EndDate FROM tblRpt_Params WHERE RptID = 5) SET @End_Date = (REPLACE(REPLACE(CONVERT(VARCHAR (8), @H_Date, 112), '-', ''), ' ', '')) SET @FaxContact = (SELECT FaxContact FROM tblRpt_Params WHERE RptID = 5) SELECT tblEData.Timestamp As [TimeStamp], LTRIM(RTrim([ResultsCustName])) AS CustName, LTRIM(RTrim([ResultsPH])) AS Phone, Status As [Status], FaxContact AS FaxContact, ResultsPKey As ResultsKey INTO tmpE_Callbacks FROM tblEData LEFT JOIN tblContact ON tblEData.RetailerPrefix = tblContact.Prefix WHERE tblEData.Timestamp BETWEEN @Start_Date And @End_Date AND FaxContact Like '%@FaxContact%' Thanx so much, Trudye
View Replies !
Events For Selected Month
I know how to get the events that start say on May, and I know how to get the events that end on May, however, How would I get the events that start on January and end in July. The month of May should display that event too. so far, as an example, I have: SELECT Events.startDate, Events.endDate FROM Events WHERE Events.Active = 1 AND startDate BETWEEN convert(smalldatetime, '5/1/2006') AND convert(smalldatetime, '5/31/2006') ORDER BY Events.startDate ASC; thank in advance.
View Replies !
Bring A Data That Not Already Selected...
Hi I have a problem that seems to be stupid… but still I cant solve it so please if you can help ASAP I'll appreciated. Its 2 tables "Courses" and "Facilitators" The Course table contain: Course_Id and Course_Name The Facilitators table contain: Facili_Id and Facili_Name The two tables have (many to many) relation between them. So I split them into a 3rd table called "Trans" which contain the PK from each Table That will be : "Trans_Course_Id" and "Trans_Facili_Id" I put some data on each table… at least 3 records and related some of them in the "Trans" table Now im looking for an SQL command that brings me All the "Facili_ID" and "Facili_Name" for a specific course. And only those who is not already selected by the same course? Like if I have the data in the "Courses" tableid: 1 and Name: VB Id:2 and Name: C# And in "Facilitators" table: Id:1 and Name: Adam Id:2 And Name: George Id:3 and Name: Sam Now in the relation table "Trans" Course_Id:1 and Facili_Id:1 Course_Id:2 and Facili_Id:1 Course_Id:2 and Facili_Id:3 Now I want the SQL Commands that brings me the he "Facili_ID" and "Facili_Name" For Course_id "For example" and should not be selected by the same course… That would be: Id:2 And Name: George Id:3 and Name: Sam And the same for eash time I pass the course_id for the command Thank you.
View Replies !
Listbox Selected Values
Hi. With VWD i've produced the following code.<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @liedID)"><SelectParameters><asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue" Type="Int16" />But the query is only returning one row of the table. Even when multiple values were selected in the ListBox1. Could someone tell me how to do?Thanks, Kin Wei.
View Replies !
Is There Any Functions For Counting Each Row Selected
Hi ! Is there any function for counting each row selected. For example if I do "select * from employers order by salary" and get: 10 Matthew Norton 5000 15 Ben King 4000 13 Caroline Stewart 2500 97 Joe Langkow 1300 How can I rank this so that I can get a number like that Caroline has got the third best salary in the company ?
View Replies !
MMC: Selected File Could Not Be Found
I need some big help here. It appears that my SQL Server Enterprise Manager is corrupted. Whenever I try to launch Enterprise Manager, I get the Microsoft Management Console telling me that "selected file could not be found" . I can launch Query Analyzer and work from it. I can also connect to my OLAP Cubes. This error just occurs when I launch the Enterprise Manager. I don't do much from the command line; so, I need to get my Enterprise Manager back up. Please help. - Joe
View Replies !
Retrieving Selected Records
Hi every body, I am making a program which is currently dealing with thousands of records. What I want is to have a button in the front END which make me able to fetch only first 100 record.If the desired record doesn't come with in first 100 record I press this button another time to fetch next 100 records i.e. from 101 to 199. How can it be possible in SQL Server stored procedure. Eagerly waiting from all of you GENIUS people. Kailash
View Replies !
Using A User Selected Parameter
Ok..............so I'm totally new to this whole SQL thing and I need some help..........please! I have a table rf_log with the following fields: PACKSLIP varchar(20) BINLABEL varchar(8) EXTENDED varchar(50) TERMID smallint USERID varchar(8) ACTION varchar(8) QUANTITY int Q_SCALER smallint REFERENCE2 varchar(30) REFERENCE3 varchar(30) DATE_TIME varchar(23) DATE_CREAT timestamp(8) LOCATION varchar(20) TOTLABEL varchar(20) CLIENTNAME varchar(15) PO_NUM varchar(25) SERIAL varchar(25) LICENSE_PLATE varchar(22) What I need to do is to get a list of packslips where the packslip number will BEGIN with the user selected parameter. For example if the user input is 'ORD000888' then I could possibly get a return of: Ord000888-0 Ord000888-1 Ord000888-2...............and so on. I have tried this but unsuccesfully: SELECT PACKSLIP,BINLABEL,EXTENDED,TERMID,USERID,QUANTITY,Q_SCALER,TOTLABEL,REFERENCE2,REFERENCE3,DATE_TIME, DATE_CREAT,CLIENTNAME,PO_NUM,SERIAL,LOCATION,LICENSE_PLATE FROM rf_log WHERE PACKSLIP LIKE ?C I have also tried: DECLARE @Pickslip varchar(30) SET @Pickslip = ?C SELECT PACKSLIP FROM rf_log WHERE PACKSLIP LIKE @Pickslip None of these worked. Any help would be greatly appreciated.
View Replies !
Only Want Field With Selected Values
I set up this query to grab the footage changes , when restocks are done. So basically the restock is a question "what was the footage before the restock" AKA Question_code 'SN015' and After the restock footage is Question_code "SN017". I just want to grab the Responses where the footage went down after the restock. So i created a subquery that grabs The max footage from the 2 footages in each group, and a field that has the min footage from the 2 footages in each group. I wanted to add a where clause that said If the Question_code is 'SN015' (Before) and Greater then the min footage or Question_Code 'SN017' (After) and Less then Max footage then show results. But for some reason its giving me most of them and then some. Plus in reporting services i get this error :"PARAMETER NAME FORMAT" Here's the code, please let me know if there is an easier way to code this. Code Block SELECT Qry_Questions.Territory, Qry_Questions.SalesResponsible, Qry_Questions.Customer, Qry_Questions.Date, Qry_Questions.StoreName, Qry_Questions.PostCode, Qry_Questions.Address2, Qry_Questions.[Question Code], Qry_Questions.Question, Qry_Questions.[Response Type], Qry_Questions.Response, Qry_Questions.sales_person_code, Qry_Sales_Group.Region_Key, Qry_Sales_Group.Region, CONVERT(varchar, Qry_Questions.Date, 110) AS shortDate, CusCat.Customer_Category_Description, Max_Footage.Max_Footage, Max_Footage.Min_Footage, Customer.Customer_Code, CAST(REPLACE(Qry_Questions.Response, 'ft', '') AS smallint) AS Response_No FROM Qry_Questions INNER JOIN Qry_Sales_Group ON Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = Qry_Sales_Group.SalesPerson_Purchaser_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer AS Customer ON Customer.Customer_Code = Qry_Questions.Customer COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Category AS CusCat ON CusCat.Customer_Category_Id = Customer.Customer_Category_Id LEFT OUTER JOIN (SELECT MAX(CAST(REPLACE(Qry_Questions_1.Response, 'ft', '') AS smallint)) AS Max_Footage, CONVERT(varchar, Qry_Questions_1.Date, 110) AS shortDate, Customer.Customer_Code, MIN(CAST(REPLACE(Qry_Questions_1.Response, 'ft', '') AS smallint)) AS Min_Footage FROM Qry_Questions AS Qry_Questions_1 INNER JOIN Qry_Sales_Group AS Qry_Sales_Group_1 ON Qry_Questions_1.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = Qry_Sales_Group_1.SalesPerson_Purchaser_Code INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer AS Customer ON Customer.Customer_Code = Qry_Questions_1.Customer COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN RC_DWDB_INSTANCE_1.dbo.Tbl_Customer_Category AS CusCat ON CusCat.Customer_Category_Id = Customer.Customer_Category_Id WHERE (Qry_Questions_1.[Question Code] IN ('SN017', 'SN015')) AND (CusCat.Customer_Category_Id = 6201) GROUP BY CONVERT(varchar, Qry_Questions_1.Date, 110), Customer.Customer_Code, Qry_Questions_1.StoreName) AS Max_Footage ON Qry_Questions.Date = Max_Footage.shortDate AND Max_Footage.Customer_Code = Customer.Customer_Code WHERE (CusCat.Customer_Category_Id = 6201) AND (Qry_Questions.[Question Code] = 'SN015') AND (CAST(REPLACE(Qry_Questions.Response, 'ft', '') AS smallint) > Max_Footage.Min_Footage) OR (Qry_Questions.[Question Code] = 'SN017') AND (CAST(REPLACE(Qry_Questions.Response, 'ft', '') AS smallint) < Max_Footage.Max_Footage) order by shortdate desc
View Replies !
Connections Are Not Selected As Documented
The following test seems to contradict documented behavior.... 1) Running SQL Server 2005 Express with Shared Memory (SM) and TCP enabled connect via ADO. 2) Connect with "localhost", it connects using SM. 3) Disable SM and restart the Server Service. 4) Connect with "localhost", it connects using TCP. 5) Enable SM and restart the Server Service. 6) Connect with "localhost", it still connects using TCP!! To restore a SM connection, the only way that I have figure out is... 7) Disable TCP and restart the Server Service. 8) Connect with "localhost", it connects using SM. And then restoring TCP does not change the connection 9) Enable TCP and restart the Server Service. 10) Connect with "localhost", it still connects using SM. If you run steps 1 - 6 but skip #4, step #6 will connect with SM. So one concludes that the TCP connection is somehow "sticky". Any ideas on how to force a shared memory connection in step #6?
View Replies !
SQL Commands For Selected Date
I want to display the data in a datasheetview in ms access project. The data is between particular date. What command should i used for this? My form contailn feilds : UserId,FromDate,ToDate Enter the userid & Particular date & after click on submit data for that period should display in datasheet view. Please help me in this as soon as possible.
View Replies !
INSERT SELECTed Values And Other..
Hi, Wondering if anyone can help with this.. I want to INSERT some data already SELECTED in a query and also some other variables.. Will the **dodgy code** make it easier to understand I wonder.. ** DODGY CODE ** INSERT INTO tblTableOne(Column1, Column2, Column3, Column4) VALUES( ( SELECT Column1, Column2 FROM tblTableTwo ), "Text for Column3", "And text for column4") ** END OF DODGY CODE ** I understand it may have something to do with TEMP TABLES perhaps?? Any help greatly appreciated.. KingRoon Chaotician Man, Slice the lines of virgin pathways. Harmony Hero.
View Replies !
Having Clause Without GROUP BY Clause?
Hi, What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ? eg : SELECT SUM(col1) from test HAVING col2 < 5 SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5 I want the equivalent query in MSSQLServer for the above Oracle query. Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?. Thanks, Gopi.
View Replies !
SQL Inner Join Clause And The Where Clause
Hi everyone, I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ? Thanks
View Replies !
Diff In On Clause And Where Clause?????
hi.. i have basic question like what is differance between conditions put in ON clause and in WHERE clause in JOINS???? see conditions that shown in brown color select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) ) select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) ) another thing... if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove d1.ImplicitID >= d2.ImplicitID from WHERE clause????
View Replies !
Top Clause With GROUP BY Clause
How Can I use Top Clause with GROUP BY clause? Here is my simple problem. I have two tables Categories Products I want to know Top 5 Products in CategoryID 1,2,3,4,5 Resultset should contain 25 Rows ( 5 top products from each category ) I hope someone will help me soon. Its urngent thanks in advance regards Waqas
View Replies !
Executing A Procedure For Every Selected Record
In a few places in my application I want to execute a procedure for every selected record. The only way that I know how to do this is to use a cursor, as below. This code works perfectly, but everything I read says that one should use set operations rather than cursors wherever possible, as they are much more efficient. So what I really want to do is something like Exec procedure argument [,argument]... where argument in (Selet value from ....)or perhaps Exec procedure (select ...) [,argument]or SELECT (procedure.... all of which are invalid. Is there any syntax that avoids the cursor in: ---Copy all facts (and their details, if any) to the new INDI Declare ccopyIndi cursor for --Step through facts Select Factid from gdbfact where factindiid = @IndiidFrom Open ccopyIndi FETCH Next from ccopyIndi into @Factid while @@Fetch_status = 0 Begin exec dbo.gdbcopyfact @NewIndiid, @Factid Fetch next from ccopyIndi into @Factid End CLOSE ccopyIndi DEALLOCATE ccopyIndi (BTW, dbo.gdbcopyfact is NOT a simple INSERT statement)
View Replies !
Trying To Enter More Than One Selected Value Into One Column In The Database.
Hi i am trying to enter more than one value into my constructor field in my database, at the moment when the values are entered into the database through the front end, they appear in the database as seperate records however they have the same ID, the only thing that changes is are the data in the fields which i want in just one. This is the part of the stored procedure I am sure is causing this to happen; IF @Access_Right_ID=8 or @Access_Right_ID=3 or @Access_Right_ID=9 BEGIN DECLARE @Delimiter char(1) SET @Delimiter = ',' DECLARE @CommaPos int SET @CommaPos = Charindex(@Delimiter,@Constructor_IDs) DECLARE @Remaining_Constructor_IDs VARCHAR(8000) SET @Remaining_Constructor_IDs = @Constructor_IDs DECLARE @Constructor_ID int WHILE (@CommaPos>=0) BEGIN SET @CommaPos = Charindex(@Delimiter,@Remaining_Constructor_IDs) if @CommaPos >0 SET @Constructor_ID = ltrim(rtrim(Substring(@Remaining_Constructor_IDs,1,@CommaPos-1))) else SET @Constructor_ID = @Remaining_Constructor_IDs INSERT INTO tblUser_Constructor ( User_ID, Constructor_ID) VALUES (@User_ID, @Constructor_ID ) SET @Remaining_Constructor_IDs = SUBSTRING(@Remaining_Constructor_IDs,@CommaPos+1,LEN(@Remaining_Constructor_IDs)-@CommaPos) if @CommaPos=0 set @CommaPos = -1 END
View Replies !
|
TRACKER
