Different Rows Order In Result Grid
Hello!
I have two similar servers (hardware) with
SQL Server 2000 (SP1) on Win2000 (SP2).
(sp_helpsort - default)
When I run the same SELECT statement from Query Analyzer on each machine
I get different rows order in result Grid
(the order of rows stays the same for each machine but different for
machine1 and machine2)
SELECT table1.a,
table1.b,
table1.c,
table1.d
FROM table2,
table1,
table3
WHERE table2.nTestDefnId = 1034
AND table2.nDefnId = table1.nDefnId
AND table3.szClinDiscType = 'X'
AND table2.nDiscId = table3.nDiscId
What is a reason for such behavior and how I can fix it?
Thank you in advance,
Elena.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Best Way To Eliminate Rows From Grid
Below is code from a 2003/1.1 project that I inherited. I have to modify the filled grid so that Index_Field8 either is excluded or included, depending on the content in the field which in turn will be determined by a checkbox. Would this be better approached in the stored procedure, or would it be better to delete the rows in the grid if they don't contain the required criteria? The latter is my preference (because I would know how to do it better) but if it's quicker to do it via a SP, I will buckle down and learn how to do it. If you have any suggestions on syntax for this in a SP, please let me know. Thank you. If chkCompletedDocs.Checked = True Then strIncludeCompleted = "1" If chkDeletedDocs.Checked = True Then strIncludeDeleted = "1" If chkActiveDocs.Checked = True Then strIncludeActive = "1" If chkArchivedDocs.Checked = True Then strIncludeArchived = "1" sqlCmd = New SqlCommand("W_SP_DOC_SEARCH1", sqlConn)sqlCmd.CommandType = CommandType.StoredProceduresqlCmd.CommandTimeout = 3000Dim prmDocSearch As SqlParameter = sqlCmd.Parameters.Add("@user_id_no", SqlDbType.Int) prmDocSearch.Value = IIf(intUserID = "" OrElse intUserID Is Nothing,DBNull.Value,CType(intUserID, Object))prmDocSearch = sqlCmd.Parameters.Add("@howmany", SqlDbType.Int) prmDocSearch.Value = (IIf(intHowMany = "" OrElse intHowMany Is Nothing,DBNull.Value,CType(intHowMany, Object))) prmDocSearch = sqlCmd.Parameters.Add("@DOC_SUBTYPE", SqlDbType.Int) prmDocSearch.Value = (IIf(strDept = "0" OrElse strDept Is Nothing,DBNull.Value,CType(strDept, Object))) prmDocSearch = sqlCmd.Parameters.Add("@DOC_CLASS", SqlDbType.VarChar, 3) prmDocSearch.Value = (IIf(strWorkGroup = "" OrElse strWorkGroup Is Nothing,DBNull.Value,CType(strWorkGroup, Object))) prmDocSearch = sqlCmd.Parameters.Add("@DOC_TYPE", SqlDbType.VarChar, 2) prmDocSearch.Value = (IIf(strDocType = "" OrElse strDocType Is Nothing,DBNull.Value,CType(strDocType, Object))) prmDocSearch = sqlCmd.Parameters.Add("@dcn", SqlDbType.VarChar, 250) prmDocSearch.Value = (IIf(strDCN = "" OrElse strDCN Is Nothing,DBNull.Value,CType(strDCN, Object))) prmDocSearch = sqlCmd.Parameters.Add("@sinvoice_Dt_From", SqlDbType.VarChar, 25) prmDocSearch.Value = (IIf(strFromDate = "" OrElse strFromDate Is Nothing,DBNull.Value,CType(strFromDate, Object))) prmDocSearch = sqlCmd.Parameters.Add("@sinvoice_Dt_To", SqlDbType.VarChar, 25) prmDocSearch.Value = (IIf(strToDate = "" OrElse strToDate Is Nothing,DBNull.Value,CType(strToDate, Object))) prmDocSearch = sqlCmd.Parameters.Add("@sINDEX_FIELD1_To", SqlDbType.VarChar, 25) prmDocSearch.Value = (IIf(Index_Field1_To = "" OrElse Index_Field1_To Is Nothing,DBNull.Value,CType(Index_Field1_To, Object))) prmDocSearch = sqlCmd.Parameters.Add("@sINDEX_FIELD1_From", SqlDbType.VarChar, 25) prmDocSearch.Value = (IIf(Index_Field1_From = "" OrElse Index_Field1_From Is Nothing,DBNull.Value,CType(Index_Field1_From, Object))) prmDocSearch = sqlCmd.Parameters.Add("@INDEX_FIELD2", SqlDbType.VarChar, 250) prmDocSearch.Value = (IIf(Index_Field2 = "" OrElse Index_Field2 Is Nothing,DBNull.Value,CType(Index_Field2, Object))) prmDocSearch = sqlCmd.Parameters.Add("@INDEX_FIELD3", SqlDbType.VarChar, 250) prmDocSearch.Value = (IIf(Index_Field3 = "" OrElse Index_Field3 Is Nothing,DBNull.Value,CType(Index_Field3, Object))) prmDocSearch = sqlCmd.Parameters.Add("@INDEX_FIELD4", SqlDbType.VarChar, 250) prmDocSearch.Value = (IIf(Index_Field4 = "" OrElse Index_Field4 Is Nothing,DBNull.Value,CType(Index_Field4, Object))) prmDocSearch = sqlCmd.Parameters.Add("@INDEX_FIELD5", SqlDbType.VarChar, 250) prmDocSearch.Value = (IIf(Index_Field5 = "" OrElse Index_Field5 Is Nothing,DBNull.Value,CType(Index_Field5, Object))) prmDocSearch = sqlCmd.Parameters.Add("@INDEX_FIELD6", SqlDbType.VarChar, 250) prmDocSearch.Value = (IIf(Index_Field6 = "" OrElse Index_Field6 Is Nothing,DBNull.Value,CType(Index_Field6, Object))) prmDocSearch = sqlCmd.Parameters.Add("@INDEX_FIELD7", SqlDbType.VarChar, 250) prmDocSearch.Value = (IIf(Index_Field7 = "" OrElse Index_Field7 Is Nothing,DBNull.Value,CType(Index_Field7, Object))) prmDocSearch = sqlCmd.Parameters.Add("@INDEX_FIELD8", SqlDbType.VarChar, 250) prmDocSearch.Value = (IIf(Index_Field8 = "" OrElse Index_Field8 Is Nothing,DBNull.Value,CType(Index_Field8, Object))) prmDocSearch = sqlCmd.Parameters.Add("@INC_COMPLETED", SqlDbType.Int, 4) prmDocSearch.Value = (IIf(strIncludeCompleted = "" OrElse strIncludeCompleted Is Nothing,DBNull.Value,CType(strIncludeCompleted, Object))) prmDocSearch = sqlCmd.Parameters.Add("@INC_DELETED", SqlDbType.Int, 4) prmDocSearch.Value = (IIf(strIncludeDeleted = "" OrElse strIncludeDeleted Is Nothing,DBNull.Value,CType(strIncludeDeleted, Object))) prmDocSearch = sqlCmd.Parameters.Add("@sortfield", SqlDbType.VarChar, 50) prmDocSearch.Value = strSortBy prmDocSearch = sqlCmd.Parameters.Add("@sortway", SqlDbType.VarChar, 10) prmDocSearch.Value = strSortWay prmDocSearch = sqlCmd.Parameters.Add("@queue_name", SqlDbType.VarChar, 50) prmDocSearch.Value = (IIf(strQueueName = "" OrElse strQueueName Is Nothing,DBNull.Value,CType(strQueueName, Object))) Dim adapter As New SqlDataAdapter(sqlCmd) Dim ds As New DataSet() adapter.Fill(ds, "InvoiceList")
View Replies !
View Related
Incorrect Order Result Set When Join Table
Hi all, I faced a problem, I have two tables - part and partmaster part : part_no, part_qty (no key) partmaster : part_no, part_description (primary key : part_no ) I want to select table part.* and partmaster.part_description. (run on mssql 2k) select a.*, b.part_description from part a, partmaster b where a.part_no *= b.part_no I want to and expect to have the result order like table "part". However, after the join, the order is different. I try to run it on mssql 7.0, the order is ok. Then I modify and run the statement select a.* from part a, partmaster b where a.part_no *= b.part_no on 2k again. The result order is ok. can anyone tell me the reason? Now I try to fix this problem is adding a sequence field "part_seq" into table "part" and run the statement by adding a order by part_seq. It does work! Regards, Simon
View Replies !
View Related
Column Order/presentation In Virtual Table (result Set From Viewor UDF)
I was just messing around with some ad hoc views and table returningUDFs today so I could look at and print out data from a small tableand noticed something strange.If I stick my select statement into a View the columns are returned inthe order I specify in the SELECT, but if the same statement is in a UDF(so I can specify a parameter), the columns are not returned in theorder specified in statement.I know that relations don't have a specified column order, but it was myunderstanding that a SELECT statement could be used to define how youwant your data presented. Views seem to respect the order specified inthe SELECT, but functions don't.What am I missing? Is there some way to force the order of the columnsreturned from a SELECT?View:CREATE VIEW dbo.View1ASSELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE '4.2.%')Column order from this view:Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,ElseStmt, NextStmtFunction:ALTER FUNCTION dbo.Function1(@SearchPrm varchar(255))RETURNS TABLEASRETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,ChildStmt, ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE @SearchPrm) )Column order from this function:Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,ParentStmt, ForStmtTable:(I know that this table isn't entirely normalized, but it serves mypurposes to have a matrix instead of a fully normalized relation):CREATE TABLE dbo.tblStmt (StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,Ident VARCHAR(255),Text TEXT,ErrorText TEXT,Type INT,ParentStmt VARCHAR(255),ChildStmt VARCHAR(255),IfStmt VARCHAR(255),ForStmt VARCHAR(255),ThenStmt VARCHAR(255),ElseStmt VARCHAR(255),NextStmt VARCHAR(255),FullName VARCHAR(255),LocalName VARCHAR(255),Method INT)INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')
View Replies !
View Related
Numbering Rows In A Result Set
work_id Work_item_code -------------------------------- 3 Sup 14 100001 5 100002 16 100003 8 IWSM 9 ES3000 10 AppDev 11 100004 I want to have a counter on this select like work_id Work_item_code counter ---------------------------------------- 3 Sup 1 14 100001 2 5 100002 3 16 100003 4 8 IWSM 5 9 ES3000 6 10 AppDev 7 11 100004 8 I can use rownum in Oracle, what do I do to get this in SQL server ? Thanks Sonali
View Replies !
View Related
Mailing Result Set Rows
In the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1071904&SiteID=1 mike.groh stated that you would define the result set as a data set variable through an execute sql task. He then pushed the variable out as a dataset type. (ds = CType(Dts.Variables("Email_CurrentDataset").Value, DataSet) How would this type of object be declared as a user variable? Is it of type object? What is the logic behind converting a result set to a data set? Specifically I am having problems with converting / casting the result set to a data set in that I am getting an error stating that it cannot convert the com object to a data.dataset class... Imports ADODB Imports System Imports System.Xml Imports System.Collections Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.SqlServer.Dts Imports Microsoft.SqlServer.Dts.DtsClient Public Class ScriptMain Public Sub Main() Dim ds As DataSet Dim dr As DataRow Dim str As String Dim dtm As String Dim s As String Try ds = CType(Dts.Variables("resultSet").Value, DataSet) ' <-- Error on converting Dts.TaskResult = Dts.Results.Success Catch ex As Exception Dts.TaskResult = Dts.Results.Failure End Try
View Replies !
View Related
Combine Rows In Search Result
In Sql Server 2005 Express I have this table:CREATE TABLE [dbo].[Sections]( [SectionID] [int] NOT NULL, [DocumentNo] [smallint] NULL, [SequenceNo] [smallint] NULL, [SectionNo] [smallint] NULL, [DocumentTypeID] [smallint] NULL, [SectionText] [ntext] NULL) Each paragraph of text (SectionText) is in its own row (SectionNo) Each primary document has a DocumentTypeID of 1 with three subdocument types (2=Index, 3=Background, 4=Report).I run this query and return a collection of single rows from various documents grouped together by DocumentNo: SELECT * FROM Sections WHERE CONTAINS (SectionText, 'exercise') ORDER BY DocumentNo For each row that contains the search term, I would like to return the full document (all rows as parapraphs within one row of returned data). In other words, I want to reconstitute the full document as it existed prior to being inserted into the database with paragraph separation. For exampe, if the search term is in row 3 of DocumentNo=5, DocumentTypeID=2, I want to return all the rows of that document in one block of text that retains paragraph format (preferablly with a line break and carriage return between paragraphs). How can this be done?
View Replies !
View Related
Getting The Number Pf Rows In A Query Result
My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section: "In the forums there is X results for the word X In the articles there is X results...." And when the user click one of those lines, the list shows the specific results in that section. My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows… Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?) Great thanks
View Replies !
View Related
Update Columns With Rows Result
Hi, for example i need to update table with top 5callers who called the most to one number: With subselect i get results: Number Caller Times 555-11111 555-11112 10 555-11111 555-11113 9 555-11111 555-11114 8 555-11111 555-11115 7 555-11111 555-11116 6 And now i need update table and get this results: Number Top1Caller Top1Caller Top1Caller Top1Caller Top1Caller 555-11111 555-11112 555-11113 555-11114 555-11115 555-11116 And i need to do this with every number in database
View Replies !
View Related
Convert Rows To Columns In Result
Hi I have a 'settings' table similar to: * Setting_ID * SettingGroup * SettingSubGroup * SettingKey * SettingValue example data: 1, Comms, Com1, Speed, 9600 2, Comms, Com1, Parity, N 3, Comms, Com1, DataBits, 8 4, Comms, Com1, StopBits, 1 5, Comms, Com2, Speed, 2400 6, Comms, Com2, Parity, E 7, Comms, Com2, DataBits, 7 8, Comms, Com2, StopBits, 2 I want to have a query like SELECT SettingKey, SettingValue FROM Settings WHERE SettingGroup='Comms' GROUP BY SettingSubGroup where the result set has a single row for each distinct value of SettingGroup. I want the result set to look like: SettingSubGroup, Speed, Parity, DataBits, StopBits <--column headings ---------------- Com1, 9600, N, 8, 1 Com2, 2400, E, 7, 2 I've had a look at the PIVOT command but it seems to require an aggregate function... is there a way to simply flip from rows to columns?
View Replies !
View Related
Rows Insert Out Of Order
I am currently trying to insert or import some rows into a table and sql server always seems to sort it by one of the columns in a different order that I insert the data. I would appreciate any feedback on this issue. Here is my table structure. columnA columnB columnC columnD columnE columnF columnG char char int int int smallint char it keeps sorting by column F and seperates them by odds and evens. Does any have a clue why this is happening? I am just using these two inserts. insert into tableNAME values('AA', 'A55', 0, 31, 1, 1, 3) insert into tableNAME values('AA', 'A55', 0, 31, 1, 2, 2) These two rows would be seperated by any other rows already contained in the table. If I add more rows. It lumps them by odds and evens. Thanks
View Replies !
View Related
To Eliminate Duplicate Rows From The Result Of The Query
Working in Sql Server 2005,got 3 different table.Need to fetch the list of persons.A person can belong to different categories.When i am using inner join on tables i am getting the duplicate rows because a perosn can belong to different categories.I want that there should be only onoe row for the person and the different categories he belongs to can come up in single field as comma sepatated string Now the results are like this: firstname lastname adress category abc xyz aaa a abc xyz aaa b abc xyz aaa c I want like below: abc xyz aaa a,b,c I am thinking of using cursors in the stored procedure.Can you provide me the solution of this including the stored procedure..
View Replies !
View Related
Display A Group In A Report That Has No Data Rows In The Result Set
Hello I have a report that retrieves its data from Analysis Services. The data includes a count and dollar value of projects against their current status: It looks something similar to (group1) status1 10 $200,000 (detail) p1 1 $5,000 p2 1 $10,000 . . p10 1 $20,000 (group1) status3 5 $90,000 (detail) . . (group1) status4 15 $150,000 (detail) . . In the report I hide the detail rows. I have a fixed/known number of statuses (in this case 4) and need to show all 4 in the report. eg (group1) status1 10 $200,000 (detail) p1 1 $5,000 p2 1 $10,000 . . p10 1 $20,000 (group1)status2 0 $0 (group1) status3 5 $90,000 (detail) . . (group1) status4 15 $150,000 (detail) . . ie in this case I need to show status 2 (that doesn't exist in the data set) with zero totals. Does anyone know if this is possible to get SSRS to display each of the status groups (in a known fixed list) and then match them to the records in the dataset. As an alternative, if I were using SQL Server I could add rows to the dataset using a union statement. Is there similar functionality using mdx? My mdx skills are very basic. Thanks Stewart
View Replies !
View Related
Flat File To Table - Rows Out Of Order
Hi, I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table. In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file. All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case. Is this normal?? Thanks
View Replies !
View Related
How To Change The Order Of Rows In Datatable Randomly?
Hello! I would like randomly change the order of the rows in my table. Is there any way to do that? I also have a question about random generator. Is it possible to get a repeatable sequence of random numbers between 1 and 10 in T-SQL? (for example 2,7,6,5,8,9,3,2,....each state with the same probability). But i need the same sequence every time i run my procedure. I know this is just a pseudo generator. I tried to use function rand([seed]) and change the seed value, but I got some strange results...(floor(rand([seed])*100)) Thanx for any idea, Z.
View Replies !
View Related
MS SQL Query, What's The Default Order The Rows Returned Are Sorted By?
i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted? this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
View Replies !
View Related
Order Of Rows In The Inserted And Deleted Psuedo Tables
I have a table that sometimes has modifications to column(s) comprising the primary key [usually "end_date"]. I need to audit changes on this table, and naturally, turned to after triggers. The problem is that for updates, when the primary key composition changes, I'm not able to relate/join using the primary key - obviously, it no longer matches across INSERTED and DELETED. Now, for a single row update, it's easy to check for updates on PK columns and then deduce what changes were made... So the real question is: are rows in INSERTED and DELETED always in matching order (1st row in INSERTED corresponds to the 1st row in DELETED...)? I don't want to put a surrogate key (GUID nor IDENTITY) on the base table if at all possible. INSERT... SELECT from the inserted/deleted tables into a temp table with identity column is fine, and is what I'm currently doing; I would like MVP or product engineer level confirmation that my ordering assumption is correct. Testing using an identity surrogate key on base table, and selecting from the Ins/del tables, and the temp tables without an order by clause seems to always return in proper order (proper for my purposes). I've tested under SQL 2005 RTM, SP1, SP2, and SP2 "3152". FYI, I've lost the debate that such auditing is better handled by the application, not the database server... Aside: why doesn't the ROW_NUMBER() function allow an empty OVER( ORDER BY() ) clause? Will SQL ever expose an internal row_id, at least in the pseudo tables, so we can work around this situation? Thanks Mike
View Replies !
View Related
Order Of Rows And Columns Returned When Reading With Excel ODBC Driver
Hi, I'm reading values from a named range within an Excel spreadsheet using the Excel ODBC driver. If I ask for all columns within a range, using e.g. select * from 'named range', does the driver ensure that the returned rowset has the same column and row ordering as in the spreadsheet? In other words, if a named range on a spreadsheet is the block of cells: name age richard 54 jemima 27 I want to make sure that my returned rowset is not going to be: age name jemima 27 richard 54 I know that proper databases do not guarantee the order of returned values (unless you specify it) but since Excel is a fixed view of data I was hoping that a returned rowset of values would retain their spreadsheet ordering. Thanks, aionaut
View Replies !
View Related
SP To Perform Query Based On Multiple Rows From Another Query's Result Set
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
View Replies !
View Related
Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter. However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance). For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed). Something like: List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac How can I accomplish this? I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell. Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
View Replies !
View Related
How To Remove Partially Duplicate Rows From Select Query's Result Set (DB Schema Provided And Query Provided).
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 1 Java Overall a@b.com 2008-01-27 14:44:29.000 2 1 2 JSP NULL NULL 0 1 3 EJB NULL NULL 0 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 1 5 AWT NULL NULL 0 1 6 Web Services NULL NULL 0 1 7 JMS NULL NULL 0 1 8 XML,HTML NULL NULL 0 1 9 Javascript NULL NULL 0 2 10 Oracle NULL NULL 0 2 11 Sql Server NULL NULL 0 2 12 MySQL NULL NULL 0 3 13 CSS NULL NULL 0 3 14 FLASH/DHTLML NULL NULL 0 4 15 Best Practices NULL NULL 0 4 16 Longue NULL NULL 0 5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 I want the result set as follows:- forumid topicid name author lastpostdate NoOfThreads 1 1 Java Overall x@y.com 2008-01-27 14:48:53.000 2 1 2 JSP NULL NULL 0 1 3 EJB NULL NULL 0 1 4 Swings p@q.com 2008-01-27 15:12:51.000 1 1 5 AWT NULL NULL 0 1 6 Web Services NULL NULL 0 1 7 JMS NULL NULL 0 1 8 XML,HTML NULL NULL 0 1 9 Javascript NULL NULL 0 2 10 Oracle NULL NULL 0 2 11 Sql Server NULL NULL 0 2 12 MySQL NULL NULL 0 3 13 CSS NULL NULL 0 3 14 FLASH/DHTLML NULL NULL 0 4 15 Best Practices NULL NULL 0 4 16 Longue NULL NULL 0 5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
View Replies !
View Related
Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View Replies !
View Related
Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?
Hi all, I copied the following code from Microsoft SQL Server 2005 Online (September 2007): UDF_table.sql: USE AdventureWorks; GO IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL DROP FUNCTION dbo.ufnGetContactInformation; GO CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int) RETURNS @retContactInformation TABLE ( -- Columns returned by the function ContactID int PRIMARY KEY NOT NULL, FirstName nvarchar(50) NULL, LastName nvarchar(50) NULL, JobTitle nvarchar(50) NULL, ContactType nvarchar(50) NULL ) AS -- Returns the first name, last name, job title, and contact type for the specified contact. BEGIN DECLARE @FirstName nvarchar(50), @LastName nvarchar(50), @JobTitle nvarchar(50), @ContactType nvarchar(50); -- Get common contact information SELECT @ContactID = ContactID, @FirstName = FirstName, @LastName = LastName FROM Person.Contact WHERE ContactID = @ContactID; SELECT @JobTitle = CASE -- Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee e WHERE e.ContactID = @ContactID) THEN (SELECT Title FROM HumanResources.Employee WHERE ContactID = @ContactID) -- Check for vendor WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = @ContactID) THEN (SELECT ct.Name FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = @ContactID) -- Check for store WHEN EXISTS(SELECT * FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE sc.ContactID = @ContactID) THEN (SELECT ct.Name FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE ContactID = @ContactID) ELSE NULL END; SET @ContactType = CASE -- Check for employee WHEN EXISTS(SELECT * FROM HumanResources.Employee e WHERE e.ContactID = @ContactID) THEN 'Employee' -- Check for vendor WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc INNER JOIN Person.ContactType ct ON vc.ContactTypeID = ct.ContactTypeID WHERE vc.ContactID = @ContactID) THEN 'Vendor Contact' -- Check for store WHEN EXISTS(SELECT * FROM Sales.StoreContact sc INNER JOIN Person.ContactType ct ON sc.ContactTypeID = ct.ContactTypeID WHERE sc.ContactID = @ContactID) THEN 'Store Contact' -- Check for individual consumer WHEN EXISTS(SELECT * FROM Sales.Individual i WHERE i.ContactID = @ContactID) THEN 'Consumer' END; -- Return the information to the caller IF @ContactID IS NOT NULL BEGIN INSERT @retContactInformation SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType; END; RETURN; END; GO ---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise. Thanks in advance, Scott Chang
View Replies !
View Related
Saving Query Result To A File , When View Result Got TLV Error
HI, I ran a select * from customers where state ='va', this is the result... (29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record Thanks for your help Ali
View Replies !
View Related
End Result Is Main Query Results Ordered By Nested Result
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows: SHOWS showID showTitle SHOWACCESS showID remoteID VIDEOS videoDate showID SQL is as follows: SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC; I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong? thanks
View Replies !
View Related
CASE Function Result With Result Expression Values (for IN Keyword)
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function. WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END ) I kept on getting errors, like Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View Replies !
View Related
Grid And Textboxes
hi, now theres something i want to achieve but, again dont know how :-( In a grid of my site it needs to be able to select an item, ( not with checkboxes) but maybe when you click on the item in the FromName column then the data in the column in my database messageTEXT needs to be able to show in a textbox.( textbox is outside of grid) now i was thinking that maybe it is need to be done with datareaders or something? Greetz Roy
View Replies !
View Related
Only When I Use A Grid View!!
hi, i have done some testing and its only when i put a grid view or any other type of data viewer on the page, and then connect it to the sql datasource that i get an error Line 1: Incorrect syntax near ')'. now i really cant figure out what it is, here is the code i am using SQL data source code : asp:SqlDataSource ID="SQLDS_view_one_wish" runat="server" ConnectionString="<%$ ConnectionStrings:wishbank_DBCS %>" SelectCommand="SELECT [msg], [Date_Time] FROM [tbl_MSG] WHERE (([Activated] = @Activated) AND ([msgID = @msgID]) )ORDER BY [Date_Time] DESC"> <SelectParameters> <asp:Parameter DefaultValue="Y" Name="Activated" Type="String" /> <asp:SessionParameter Name="msgID" SessionField="sWV" Type="String" /> </SelectParameters> </asp:SqlDataSource> session variable code which sends it to this page protected void GridView1_SelectedIndexChanged(object sender, EventArgs e) { GridViewRow row = GridView1.SelectedRow; Session["sWV"] = row.Cells[1].Text; Response.Redirect("www/viewwf.aspx"); } if you have an idea please let me know as im stuck!
View Replies !
View Related
Regarding Data Grid
what datafield shoud i put in my database if its a checkbox option? coz in my datagrid i added a checkedbox so that i can easily manipulate it.but when i run it. it produces error. and doesnt recognize the checkbox column.it said that there none in the datasource.but i dont know what should i put in the database! pls help!thanks
View Replies !
View Related
Designer Grid
Can you turn on a grid in the designer to assist you with aligning tasks, etc. I've trie all kinds of things, but can't get a grid. Or even maybe rules? I'm probably missing something real simple, but can't find.
View Replies !
View Related
Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following... SQL Server Express edition Visual Studios 2005 Jet 4.0 newest upgrade .Net Framework 2.0 (or should I use 3.0) VS2005 Security upgrade Anything else I need for just creating a database for my VS2005 Visual Basic project? I was trying to use MS Access as my backend db but would like to try SQL Express Thank you, Mark
View Replies !
View Related
Problem With Grid View
i seem to have problems with my grid view not displaying anything when if i test my SQLDataSource, it shows me rows of data.i seem to have this problem only with my Stored procedure, or when i change my Database structure, like add fieldsHere is my procedure: can i do this? select a row and also return a varchar?ALTER PROCEDURE dbo.SelectUpload ( @FileName varchar(50), @Return varchar(MAX) = NULL OUTPUT ) AS SELECT * FROM Uploads WHERE FileName = @FileName RETURN SELECT WebPath FROM Uploads WHERE FileName = @FileName
View Replies !
View Related
SQL Statements For A Data Grid
I've created a Data Set with a connection string to my sql database however when i go to create queries and select 'generate update, delete and insert statements' it doesn't do it. I'm wondering why and how do i get this fixed since i don't know how to code it myself. Thank you.
View Replies !
View Related
Key In New Recird With Grid View
Inside my gridview, the user can key in new record, delete record and update record. but dont know why my insert function cant work out and i dunno why this is happen? Can somebody help me out with this?Thanks My Code:<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"InsertCommand="INSERT INTO [rest_info] ([resname], [menu], [price], [date]) VALUES (@resname, @menu, @date, @price)"<InsertParameters> <asp:Parameter Name="resname" Type="Char" /> <asp:Parameter Name="menu" Type="char" /> <asp:Parameter Name="price" Type="Decimal" /> <asp:Parameter Name="date" Type="datetime" /> </InsertParameters></asp:SqlDataSource>
View Replies !
View Related
Grid Vs Text Output
In SQL Query Analyzer, there is a Query drop down window that gives youthe option to change the output from grid to text for printing ifneeded. My question is, can this be programmed so a stored procedurewill always print in text without having to manually change the windoweach time the procedure is run? I could find nothing under the logicalsearches in books online.Thanks JAB
View Replies !
View Related
ASP Data Grid Needed
I need to display data from a sql server 2000 db on an asp web page and I need a vertical split. Does anyone have any solutions? I really can't afford to spend much/any money so freeware/shareware or coding suggestions is what I am interested in. Thanks!
View Replies !
View Related
Grid Veiw , Latest On Top
Good day all I have a grid veiw of my data base on a windows form . As the data grows , the more I have to scroll down to add new data in a row . Does anyone know if there is an option or propertie I can set so as the latest data row is on top? Thanks Rob
View Replies !
View Related
DB Grid And Stored Procedure
Hi I would like to create and run a stored procedure from VB. I will pass a variable to the stored procedure and after that according to the parameter, records are selected and shown in the DB grid on the form.. I'm using VB 5 and mssql 2000. I have to show selective data in the DB Grid. Any suggestions and help are welcomed. Thanks in advance.
View Replies !
View Related
Default Sort Order - Open Table - Select Without Order By
Hi! I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order. The table returns the data in the same order in SQL Manager "Open Table" So I started to wonder what deterimins the sort order when there is no order by clause ? I researched this for a bit but found no straight answers. My table has no PK, but an identiy column. Peace. /P
View Replies !
View Related
|
TRACKER
