sql

split string in sql query

I have a value in field called "postingdate" as string in 2009-11-25, 12:42AM IST format, in a table named "Post". I need the query to fetch the details based on date range. I tried the following query, but it throws an error. Please guide me to fix this issue. Thanks in advance. select postingdate from post where TO_DATE(postingDat...

What are the strategies to achieve performance for both reads and writes to Sql Server?

Hi, I'm a novice, both here at stack overflow as well as on sql server, so please let me know if this question is not appropriate in any way :) Well, I'm developing a web application that will be used to analyze large amounts of data, which is stored in a SQL Server 2008 database. The interface will not allow the users to update or inse...

Does having more attributes in a table reduce performance??

Hi all, So far I am quite comfortable working with C# windows application. I am about to shift to Asp.net to develop a website. The requirement has made me to put around 50 columns in a single table. I know this concept of breaking it into small tables using normal forms. I tried googling, but dint get much results. I need to know if ...

SELECT and UPDATE table so there is no overlap of Threads

Hi, Say I have the following table: ID|Read ------- 1|true 2|false 3|false 4|false ... and I need to read the smallest ID, that has [Read] == false; plus, update that I have now read it. So if i execute my Stored Procedure dbo.getMinID, it will return ID: 2, and update [Read] -> true. CREATE PROCEDURE [dbo].[getMinID] ( @Quer...

Sql Splitting Function

I have a string like that "10*cat*123456;12*rat*789;15*horse*365" i want to split it to be like that "cat, rat, horse" I have made this Function CREATE FUNCTION [dbo].[Split](@BenNames VARCHAR(2000)) RETURNS VARCHAR(2000) AS BEGIN DECLARE @tmp VARCHAR(2000) SET @tmp = @BenNames SET @tmp = SUBSTRING( S...

ADO.NET Transaction and SQL Server 2008 Transaction

Hi. If i run stored procedure in ADO.NET with transaction enabled and SP begin its own transaction inside(with COMMIT TRANS). What happens when ADO.NET rollback transaction? Is transaction from SP rollbacked as well? Is DB in state as it was before calling ADO.NET? Thank you. ...

MS Access SQL DELETE - why would someone specify column names?

I'm having to support an Access .mdb file that someone else has written. One of the button functions in this .mdb calls out to delete some data in an external MSSQL database. All very straightforward, but this syntax isn't something I've seen before: DELETE tblEquipmentConnections.SourceEquip, tblEquipmentConnections.EquipmentConn...

Transfer SQL Server Objects Task (SMO) fails 'Truncate data failed for Table' in SSIS

Hi I am getting below error in ssis package. Package contains Transfer SQL Server Object Task which Replace Data in destination table. I have source dimension table from which I am replacing data in destination dimension table.There is no FK constraint present on source & destination dimension table which may not allow truncate table. ...

SQL [Hard query - to make or to avoid]

SELECT Name, ( NOT (ID_ListGroupParIzm IN (SELECT ID_Param FROM TbUserParam WHERE ID_User=:ID_User ) ) ) Visi FROM CfgListParIzm WHERE ID_ListGroupParIzm=:ID_ListGroupParIzm Errors : Message 156, Level 15, State 1, Line 1 Incorr...

T SQL Convert a row to a string

Is there a valid way in SQL Server 2005 to read a row of data into a string? For example if my table looked like the following: ID | First Name | Last Name | Color ----------------------------------- 1 | Timmy | Jones | pink 2 | Martha | Fisher | green That I could get back as a result: '1 Timmy Jones pink' '2 Mart...

Finding all rows in a database where a field is different from another field

I have the following SQL query: SELECT * FROM table WHERE field_1 <> field_2 Which is the best index structure to use, to keep this query efficient: two indexes on field_1 and field_2 or a single index which includes both fields? EDIT: The database is MySQL ...

Querying for a unique value based on the aggregate of another value while grouping on a third value entirely

So I know this problem isn't a new one, but I'm trying to wrap my head around it and understand the best way to deal with scenarios like this. Say I have a hypothetical table 'X' that looks like this: GroupID ID (identity) SomeDateTime -------------------------------------------- 1 1000 1/1/01 1 1001 2/2/02 ...

SQL Query...nulls hosing me.

I tried to ask this question before but I don't think I explained myself very well. So here it is: asp.net 2.0 app hitting a SQL 2008 backend. This seems simple but I can't get it. 1 table. The user selects a status. The query should return all records = the chosen status only. If the user select "All Status", then ALL records should be ...

SQL Table linking... is it better to have a linking table, or a delimited column?

My database has two tables, one contains a list of users, the other a list of roles. Each user will belong to one or more roles, and of course each role will have multiple users in it. I've come across two ways to link the information. The first is to add a third table which contains the ID's from both tables. A simple join will the...

Why not "Invalid column name XYZ" error in subquery; although column name is not in subquery table?

When I run this query SELECT CustomerId FROM Stocks.dbo.Suppliers It gives me this error. Invalid column name 'CustomerId'. This error is valid as there is no column CustomerId in Suppliers table; but when I use same query in subquery it does not give any error E.g. SELECT * FROM SomeOtherDb.dbo.Customer WHERE CustomerId In( SEL...

SQL SELECT FROM: Inquiry

I have a table with multiple fields for address, such as address_line_1, address_line_2, etc. Is there any way that I can use a WHERE clause across multiple fields at once, without having to use an OR/AND statement? For example: SELECT * FROM FIN_LIVE.CUSTOMER_ADDRESSES WHERE SYS_ADDRESS_1, SYS_ADDRESS_2, SYS_ADDRESS_3 = 'data' ...

Convert SQL-query with subselect in select section to HQL

Hello. I have trouble with converting native SQL query to HQL. Query is something like follows: select count(*) , sum(select count(*) from employee e where e.company_id=c.id)) from company c where c.id = someID First returned value is count of companies, second - amount of employees for specified company. I.e. I have to get this tw...

MySQL absentee report script

Hi, I'm currently trying to write a query that will return all users that have not logged time for a given date in our timesheet system. We currently have 2 tables, timesheets and users. I am trying to make a query that will return a list of users that do not have an entry in the timesheets table for a date range. There is only one reco...

SQL join: selecting the last records in a one-to-many relationship

Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one SELECT statement. What is the best practice? Any advice on building indexes? Please use these table/column names in your answer: customer: id, name purchase: i...

How would I perform math within a sql query to calculate percent difference?

I would like to take surveys (2 or more choices), and place the results in a table. I just want to know how I would tally up the results in a sql query. Even more, should i be doing the math within the query, or just use PHP to do the math? Example Questions Table question_id (int) question (text) answer_1 (varchar) answer_2 (varchar...