Hi gurus, This question explained about a way of getting distinct combination of multiple columns. But I want to know the difference between the methods of DISTINCT, UNION, GROUP BY keyword method for this purpose. I am getting different results when using them. My queries are like this Query 1. select column1, column2, column3 from ta...
Hi, I'm writing a converter for my database from MSSQL Express Edition to Oracle. The amount of rows in the table is around 5 millions. On MSSQL side I use LINQ to SQL to select data. I'd use the same approach for Oracle, but unfortunately there's no LINQ to Oracle in .NET. I know there is open source LINQ to Oracle implementation, but I...
I have a table create table us ( a number ); Now I have data like: a 1 2 3 4 null null null 8 9 Now I need a single query to count null and not null values in column a ...
Hi - T-SQL DateTime Question. I have a set of time ranges. During those time ranges there could be a set of overlapping time ranges that I call 'blocked' out time. The blocked time wouldn't span more than one day. What I want to do is split the time to exclude the blocked out time, basically giving me the time ranges that are not 'block...
When joining to a subset of a table, any reason to prefer one of these formats over the other? Subquery version: SELECT ... FROM Customers AS c INNER JOIN (SELECT * FROM Classification WHERE CustomerType = 'Standard') AS cf ON c.TypeCode = cf.Code INNER JOIN SalesReps s ON cf.SalesRepID = s.SalesRepID vs the WHERE clause at the e...
hello i have two tables: table_1 and table_2 table_1 table_2 field_1 field_2 field_3 field_4 1 a 1 2 b 4 3 c 3 4 d 2 now i need a query using update so that in table_2 i can ha...
I have written a Windows application that stores data locally in a SQL Server Express database. It is used by multiple people from remote locations who need to synchronize their data occassionally. It requires a fairly simple synchronization process of only a handful of tables and I'm currently accomplishing this by linking each of the...
I have a matrix with rows indicating a name (Ex Store name) and I have data pertaining to that in the DATA cells. I also have another date field in this format (MM/DD/YYYY). I would like to use a cross tab between Store Names and Just the Month of the date field. Jan Feb Mar Apr (From Date) A (Store Name) 10 5 ...
I'm using ODBC, OLE is available if needed to dump approx 3 different types of data to different excel sheets. But to use insert statements in excel, the table has to be created first I believe, especially if the sheet doesn't exist yet. I used to use sql server 2000's import/export wizard that automatically generated the create table s...
It's fairly general question, but I'd like to know what do you use in determination of primary key of the table. Examples supplied with you reasoning are highly desired. I noticed that many programmers add ID column and use it as a primary key. I think, it is flawed from design point of view, as ID in that case HAS NOTHING TO DO WITH T...
I'd like to know if this kind of stored procedure is possible, do i need some sort of looping structure or something? I want to do this, basically in this order: get all rows from one table or view. (table1) based on columns from table 1, i want to set variables for use in insert/update table2. i want to reference another table, (table...
I know about Scott Gu's LINQ to SQL visualiser, but is there a way to take a LINQ expression at run-time and do something to find out the full resulting SQL? ...
How do you find centroid values for each cluster using the excel data mining plug-in (or SQL Server 2008)? In particular, how can the centroids be measured accurately when their vectors include nominal and boolean values? I've read the online books and I only got as far as node_distribution. I'm looking for centroid values or an algori...
I'll preface this question by stating that I'm using Oracle 10g Enterprise edition and I'm relatively new to Oracle. I've got a table with the following schema: ID integer (pk) -- unique index PERSON_ID integer (fk) -- b-tree index NAME_PART nvarchar -- b-tree index NAME_PART_ID integer (fk) -- bitmap index Th...
I've got a (for my abilities) rather complex query that I am trying to improve upon. I use the same query multiple times, changing only some parameters, so I'm hoping to not need a full-re-write. I am searching for a bunch of events by date and location. I put these into an html table where Date 1 Date2 Date3 event1...
I am looking for a performant default policy for dealing with the .dbo prefix. I realize that the dbo. prefix is more than syntactic noise, however I got through the past 8 years of MS based development skipping typing the dbo. prefix and ignoring its function. Apart from a performance issue with stored proc compile locks is there a d...
Here is a split function, it can apply as dbo.Split('sf,we,fs,we',','), when I change the string to column name it doesn't work, such as dbo.Split(table.columnName,','). Select * from dbo.Split('[email protected]','@') is works but Select * from dbo.Split((Select Email from Users),'@') has an error message: Msg 102, Level 15, State 1...
I am trying to use some variables in a creation script for database setups. I am not exactly sure how to use them. Please explain how to properly format my code. Below is the code I try, and the error I get: SET @username = 'xxxx'; -- store number goes here SET @password = 'xxxxxx'; -- store password goes here CREATE TABLE IF NOT EXIST...
I am testing my knowledge of ADO.NET and SQL and am currently just trying to do the basics. I want to read from a table and when the user clicks a button, a message box pops up with the value in the ApplicationName column. It currently doesn't do anything when I click the button... any ideas? protected void TestSubmit_ServerClick(objec...
Is there a way to have a query return either a 'Y' or 'N' for a column if that column has a value above a certain number say '25'. I am using DB2 on ZOS ...