sql

charindex in SQL doesn't give the desired result

I have a string which is a output from a function eg: "1,3,16,..,..". I used the following sql query and ran it in the query builder in VS and it didn't give me any syntax errors. SELECT ItemID, Name, RelDate, Price, Status FROM item_k WHERE (ItemID = cast(charindex(',',@itemIDs) as int)) I gave 3,16 as the @itemID parameter values, b...

Hibernate mapping and inheritance issues with @SecondaryTable

I have a pretty simple class hierarchy structure that is listed below, each sub class having a @DiscriminatorValue enumeration type, which works very well. Abstract class: AbstractNode (abstract class mapped to the Node table) Subclassed classes: FieldNode (simple class mapped to Node table as well, but with different @DiscriminatorV...

SQL Reporting Services: Finding the folder a report is in

Hi there, If I have the report name how can I programmatically get the name of the project/folder the report is in? So for example if I have a report like so http://server/Reports/Pages/Report.aspx?ItemPath=/ReportProject1/ReportName Given "ReportName" how can I figure out that the report is in the folder "ReportProject1"? So I gues...

RDBS when to use complex indexes for queries and when use simple?

Suppose I have table in my DB schema called TEST with fields (id, name, address, phone, comments). Now, I know that I'm going to perform a large set of different queries for that table, therefore my question is next, when and why I shall create indexes like ID_NAME_INDX (index for id and name) and when it's more efficient to create separ...

Continuous sequences in SQL

Having a table with the following fields: Order,Group,Sequence it is required that all orders in a given group form a continuous sequence. For example: 1,2,3,4 or 4,5,6,7. How can I check using a single SQL query what orders do not comply with this rule? Thank you. Example data: Order Group Sequence 1 1 3 2 1 4 3 1 5 4 1 6 ...

How to find the average time difference between rows in a table?

I have a mysql database that stores some timestamps. Let's assume that all there is in the table is the ID and the timestamp. The timestamps might be duplicated. I want to find the average time difference between consecutive rows that are not duplicates (timewise). Is there a way to do it in SQL? ...

Circular Match

I have a database with three tables: userid_tbl, need_tbl, have_tbl create table userid_tbl (user_id varchar2(15) not null primary key); create table need_tbl (user_id varchar2(15) not null, have_item varchar2(100) not null, foreign key (user_id) references userid_tbl (user_id) ); create table have_tbl (user_id varchar2(15) not null,...

simple query with brackets - how to build?

I'm trying to build a query to use for a search engine, that would look like this one: SELECT * FROM sometable WHERE col1 = 1 AND col2 = 2 AND (col3a = 3 OR col3b = 3 OR col3c = 3) I though the code below would work: SubSonic.Query query = new SubSonic.Query("sometable"); query = query.WHERE("col1", 1); query = query.WHERE("col2...

Validate DateTime String in SQL Server 2005

Hi, I require a SQL script to validate a VARCHAR field in a table in a SQL Server 2005 database that contains DateTime values, in the format DD/MM/YYYY, or NULL values. I would like to identify all invalid dates. Can anyone suggest a method? UPDATE The answer has to make use of T-SQL; for performance reasons, I can't make use of SQ...

Descending sort order indexes

The Database Engine Tuning Advisor has finally given up the ghost and can't help me any more, so I'm having to learn a little bit more about indexes (shouldn't it be indices?). I think I'm more or less there. I know when to use composite indexes, what to include with my indexes, the difference between clustered and non-clustered indexe...

'SELECT *' from inner joined tables

How do you select all fields of two joined tables, without having conflicts with the common field? Suppose I have two tables, Products and Services. I would like to make a query like this: SELECT Products.*, Services.* FROM Products INNER JOIN Services ON Products.IdService = Services.IdService The problem with this query is that I...

SQL query to join several columns

I'm trying to join some data together from 2 tables, but on several columns. here's an example: Source table ID | Desc| AAAA| BBBB| Table2 table ID | Text| ID1 | ID2 | ID3 | where ID1, ID2 and ID3 in Table2 are ID's from the Source table I'd like to do a query which yields the results: Table2.Text, Source.Desc(ID...

How do I calculate a moving average using MySQL?

I need to do something like: SELECT value_column1 FROM table1 WHERE datetime_column1 >= '2009-01-01 00:00:00' ORDER BY datetime_column1; Except in addition to value_column1, I also need to retrieve a moving average of the previous 20 values of value_column1. Standard SQL is preferred, but I will use MySQL extensions if necessary. ...

Passing a varchar full of comma delimited values to a SQL Server IN function

Duplicate of Dynamic SQL Comma Delimited Value Query Parameterized Queries with Like and In I have a SQL Server Stored procedure where I would like to pass a varchar full of comma delimited values to an in function. Example Declare @Ids varchar(50) Set @Ids = ‘1,2,3,5,4,6,7,98,234’ Select * from sometable where tableid in(@Ids) Thi...

T-SQL Subquery Max(Date) and Joins

I'm trying to join multiple tables, but one of the tables has multiple records for a partid with different dates. I want to get the record with the most recent date. Here are some example tables: Table: MyParts Partid Partnumber Description 1 ABC-123 Pipe 2 ABC-124 Handle 3 ABC-125 Light Table:...

Oracle Select numbers from an IN clause

I'm looking for the best way to select numbers directly from an in clause. Basically like: SELECT * FROM (2,6,1,8); That doesn't work. I can do it this way: SELECT Lv FROM ( SELECT Level LV FROM DUAL CONNECT BY Level < 20) WHERE Lv IN (2,6,1,8); But that seems to be a bit clunky. Is there a m...

Separate String with separator #@#

Hi All, The string below I have to call three times using sql. String: JAN#@#PIET#@#HENK The first call shoudl return: JAN The second call: PIET The third: HENK So we could use the #@# as separator but it could be that the string is: JAN#@#PIET only. Still all three calls will be done where call 1 returns: JAN call 4 returns: PIET c...

SQL -- Assigning a bit variable in select statement

For example: declare @bitHaveRows bit select @bitHaveRows = count(*) from table where (predicate) Are there any functions I can call on this line: select @bitHaveRows = count(*) to assign this bit a value of 0 if there are no rows, or 1 if there are one or more rows? ...

Debugging stored procedure in SQL Server 2005 from Visual Studio?

I see a lot of frustrated questions here and elsewhere with no clear answer. I am trying to get the stored procs to debug, but with no success. Client: either VS2005 or VS2008, works in neither. When I select 'Step into Stored Procedure' from the sproc context menu, I get "Cancelled by User' in the debug window and that's the end of...

SQL order of operations

If I run the following SQL query SELECT * FROM A LEFT JOIN B ON A.foo=B.foo WHERE A.date="Yesterday" Does the WHERE statement get evaluated before or after the join? If after, what would be a better way to write this statement so that only rows in A from "Yesterday" are joined to B. ...