sql

Why there is a delay of adding a record in database in some websites?

In stackoverflow, and some other websites where people submits questions or posts, there is a delay of 20 seconds before the post displayed, is that means the post is delayed by the SQL server, or the website does not allows instant posting, or it's a CDN cache matter ? And the important thing , why there is a delay, is it in order to m...

Transact SQL: selecting a boolean expression

Query: SELECT TOP 1 ReportInvoked , EmailSent FROM tblReportInvoker WHERE WebUserId = 12345 This gives me two bit values. What I really want is a scalar result that is the logical AND of these two values. Is this possible? This seems like it would be easy but I'm not finding a syntax that will work. Edit: Of course the flaw in my...

Is there a way to conditionally use default column values in an INSERT..SELECT statement?

I've got code similar to the following in a stored procedure that inserts a row into a table, I'd like to set the last column (FieldD) to @prmSomeValue unless it is null, otherwise just use the default value defined for that column. IF (@prmSomeValue IS NULL) INSERT INTO MyTable (fieldA,FieldB,FieldC) SELECT A,B,C FRO...

How to do an INNER JOIN on multiple columns

I'm working on a homework project and I'm supposed to perform a database query which finds flights either by the city name or the airport code, but the flights table only contains the airport codes so if I want to search by city I have to join on the airports table. The airports table has the following columns: code, city The flights ta...

How to present features of aggregate functions (NULL)?

I'm looking for 'textbook' example of database to illustrate salient features of the aggregate functions (Max, Min, Sum, Avg and Count) when NULL values are involved. I must be able to discuss and illustrate/present the usage of these aggregates function in the presence of NULLs with example queries and their answers, using mentioned da...

Can table columns with a foreign key be null?

For example, I have a table which has several ID columns to other tables. I want a foreign key to force integrity only if I do put data in there. If I do an update at a later time to populate that column then it will still check the constraint (this is likely database server dependant, i'm using MySQL & InnoDB table type). I believe this...

Recursive Query - Only select nodes where leaf nodes represent active data

Given the following recursive query: WITH DepartmentHierarchy (DepartmentID, Name, IsInactive, IsSpecial, ParentId, HierarchyLevel) AS ( -- Base case SELECT DepartmentId, Name, IsInactive, IsSpecial, ParentId, 1 as HierarchyLevel FROM StoreDepartment WHERE ParentId IS NULL UNION ALL ...

Join query result to a single line of values separated by comma

Possible Duplicate: SQL Server: Can I Comma Delimit Multiple Rows Into One Column? I have a query like this: SELECT name from users and it's result is a number of records: 1 user1 2 user2 3 user3 I want to get all this records in a single line separated by comma: user1, user2, user3 and an empty line if query result i...

MS SQL: One Large, Multi-Column dbo.Person table or Multiple Subsets With Multiple Joins?

Hi, I am thinking through the layout of my "Person" table in my MSSQL DB. Is it better to have all columns in dbo.Person as such: Person ( personid first last email confirmcode isconfirmed homeaddress homecity homestate homezip session ipaddress workaddress workcity workstate workzip etc... ) OR, is it better to partition the tabl...

ORDER BY condition

I would like to retrieve an ordered query result, but I need to have some specific row(s) to be in front of the list. Something like here on Stack Overflow, in the list of answers the right answer is always the first one. Assumed I need to have the rows with IDs 1,2,3 to be the head, the rest sorted by a date field, is it possible to do...

MS-SQL Average Columns with NULL

So I've got 3 different columns (basket 1, 2, and 3). Sometimes these columns have all the information and sometimes one or two of them are null. I have another column that I'm going to average these values into and save. Is there a sleek/easy way to get the average of these three columns even if one of them is null? Or do I have to hav...

Programmatically change how a property in a Select is populated in an LINQ to SQL query

I'm writing a bit of LINQ to SQL that needs to perform a search for client information in our database, filtering the results and paging them. The filtering needs to be dynamic so I have broken the setup of the query into four stages: Find visible clients (basically apply a coarse grained filter) Filter clients by search criteria Sort ...

How do you stop a user-instance of Sql Server? (Sql Express user instance database files locked, even after stopping Sql Express service)

When using SQL Server Express 2005's User Instance feature with a connection string like this: <add name="Default" connectionString="Data Source=.\SQLExpress; AttachDbFilename=C:\My App\Data\MyApp.mdf; Initial Catalog=MyApp; User Instance=True; MultipleActiveResultSets=true; Trusted_Connection=Yes;" /> We find that we can't ...

JPQL to SQL converter

Is it possible to access the SQL which is generated by JPQL? I would like to use the SQL to later create a view for my purposes. I am using Hibernate if it matters. ...

SQL : Error converting data type varchar to numeric

I have met a very strange problem. There are two sql queries: Q1: SELECT * FROM tbl_1 WHERE ID IN (SELECT TargetID FROM tbl_2 WHERE PeopleID = 'AAA') AND ID = 49 Q2: SELECT * FROM tbl_1 WHERE ID IN (SELECT TargetID FROM tbl_2 WHERE PeopleID = 'BBB') AND ID = 49 We could find ...

How to select a value in the same table as the value for an update for each row.

I have a table structure with columns like this [ID] [Name] [ParentId] [ParentName] The parents are contained in the same table, and i would like to populate the parent name column using a statement like: UPDATE Table SET ParentName = (select Name from Table where Id = ParentId) ...

mysql query to sort some results but join data may or not be present

SELECT PLD_LINK.ID, PLD_LINK.TITLE, PLD_LINK.URL, PLD_CATEGORY.TITLE, TLL_SORT_STATUS.status, PLD_LINK_COMMENT.DATE_ADDED FROM PLD_LINK, PLD_CATEGORY, TLL_SORT_STATUS, PLD_LINK_COMMENT WHERE PLD_LINK.CATEGORY_ID = PLD_CATEGORY.ID AND PLD_LINK.ID = TLL_SORT_STATUS.link_id AND PLD_LINK.PAGERANK BETWEEN -1 AND 2 ...

How to create SQL Database from Linq2Sql Model

Is it possible to create a SQL DB from a Linq2Sql model? I managed to lose a DB for something I started building a year ago, but have the Linq2Sql model. If this is possible, what are the steps? ...

Getting error while executing dao

Hello friends i am running code given below which contains the setLogTimeEntery function and when this function is executed i am getting "Error : java.sql.SQLException: ORA-00917: missing comma" error and my database is oracle plese any one tell me wht is the problem. public int setLogTimeEntery(Connection con, LogTimeBean ltb) { i...

How to execute t-sql script using java?

I have found the following links for executing script using java i.e. http://stackoverflow.com/questions/2071682/how-to-execute-sql-script-file-in-java . But it is specific to postgre and mysql. I am looking for same solution for executing script in Sql server 2005. I am not acquinted with SQL Command Line. I have to make a batch conta...