What is the best way to build the table that will represent the tree? I want to implement a select ,insert ,update and delete that will work well with big data. The select for example will have to support "Expand ALL" - getting all the children (and there children) for a given node. ...
if i use this query thath creates a table(look Table1), But VisitingGap column is not correct format. i used cast method to give format. But not working correctly.i need Table2 declare @date1 nvarchar(100) , @date2 nvarchar(100) , @countgap int,@count int set @date1='2009-05-12' set @date2 = '2009-05-13' set @countgap = 30 set @count=...
Well I have a series of sps that are running a data warehousing solution that we have developed in house. While on the most part it runs pretty good, there is one stored procedure that runs really slow. It takes about 30 minutes on average to execute. I know exactly where the bottle neck is, I just don't know how to fix it. Basically...
I was asked this question and could not come up with the answer. Is there a way to find the entry at the first row and first column in a table? (In matrix notation, that would be the [1,1]th location) ...
I need to write some sql that will allow me to query all objects in our Oracle database. Unfortunately the tools we are allowed to use don't have this built in. Basically, I need to search all tables, procedures, triggers, views, everything. I know how to search for object names. But I need to search for the contents of the object. i....
I have RO access on a SQL View. This query below times out. How to avoid this? select count(distinct Status) from [MyTable] with (NOLOCK) where MemberType=6 The error message I get is: Msg 121, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server (provider: TCP Pro...
I have a pair of SQL server tables. P contains id and name. PR contains id, interestrate, tiernumber, fromdate, todate and P.id. PR may contain many rows listed per p.id / tier. (tiers are a list of rates a product may have in any given date period.) eg: Product 1 tier 1 starts 1/1/2008 to 1/1/2009 and has 6 rates shown 1 row per rate...
I have a search form used to query service provisions, which I will call 'provisions'. Provisions may have certain eligibility requirements in a has and belongs to many relationship, but they may also have no requirements at all. My issue is that when I exclude certain provisions on the basis of a particular requirement, those provisio...
I am trying to restrict the results returned from a query. If a user has access to more Entities than just those listed for the current user, then that user should not show up on the list. Using the data below, and assuming User 1 is running the query, since the user with UserId 2 has matches that User 1 does not have, even though they h...
Is there a way to use the value from the CASE WHEN test as one of its results without writing out the select statement twice (since it could be long and messy)? For example: SELECT id, CASE WHEN ( (SELECT MAX(value) FROM my_table WHERE other_value = 1) IS NOT NULL ) THEN ( SELECT (MAX(value) FROM my_ta...
Hi, Why does the following query return 'zero' records: SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10 OR SELECT * FROM records WHERE rownum >= 5 Whereas the following query return proper records: SELECT * FROM records WHERE rownum <= 15 Regards, - Ashish ...
Chances are i will need to do a lot of userid->username lookups. So i was thinking, instead of having a table like the below userid int PK username text userpass_salthash int user_someopt1 int user_sig text to have it broken up as the below //table1 userid int PK username text //table2 userid int //fk userpass_salthash int user_some...
I'm trying to pull data using a SqlDataReader, one column of which is in datetime. I'd like to do something like this SqlCommand command = new SqlCommand("SELECT * FROM table", connection); //connection is defined earlier SqlDataReader data = command.ExecuteReader(); while(data.Read()){ DateTime birthday = ...
I'm an accomplished user of SQL; I'm confident creating schema and optimizing queries in ANSI-SQL, for Sybase, MS SQL Server, MySQL, or postgresql. I understand joins, subqueries, indexes, etc., so I don't need to recapitulate any of that that isn't different under Oracle. I'll be taking a job that'll require using Oracle. Point me to o...
I am writing a a report and was wondering if there is there any way to find items that join into identical sets? For example in the following query, I want to find all areas that join against the same set of products: SELECT Area.Name, AggregateSetOfProductsId FROM Area INNER JOIN AreaToProduct ON AreaToProduct.AreaId = Area.Id GROUP ...
I have a SQL table that contains data of the form: Id int EventTime dateTime CurrentValue int The table may have multiple rows for a given id that represent changes to the value over time (the EventTime identifying the time at which the value changed). Given a specific point in time, I would like to be able to calculate the count of d...
How can I read the value of a system environment variable in a T-SQL script? This is to run on SQL Server 2005. ...
Hi guys I have the following statement: SELECT CASE WHEN (1 = 1) THEN 10 ELSE dbo.at_Test_Function(5) END AS Result I just want to confirm that in this case the function wont be executed? My reason for asking is that the function is particularly slow and if the critiria is true I want to avoid calling the function... Cheers Antho...
I have a SQL query that I need to represent using NHibernate's ICriteria API. SELECT u.Id as Id, u.Login as Login, u.FirstName as FirstName, u.LastName as LastName, gm.UserGroupId_FK as UserGroupId, inner.Data1, inner.Data2, inner.Data3 FROM dbo.User u inner join dbo.GroupMember gm on u.Id = ...
Lets frame the question again--- table1{date, bID, sName, fID} {11/05,B1,A1,P1} {12/05,B2,A2,P2} {13/05,B1,A3,P1} {15/05,B3,A4,P1} {16/05,B1,A5,P2} {19/05,B1,A6,P2} This is the table and the data stored in the table is also specified... Now the query that i want is that:- Depending of fId (lets say, P1 is s...