Difference between views and SELECT queries
If views are used to show selected columns to the user, and same can be done by using SELECT col1, col2 FROM xyz , what is the point of using views? ...
sql
SQL Round Function
round(45.923,-1) gives a result of 50. Why is this? How it is calculated? (sorry guys i was mistaken with earlier version of this question suggesting value was 46) ...
Can NVL Function be Cascaded?
can nvl() function be cascaded,...it was asked me in IBM interview .....and why???? ...
Insert, on duplicate update (postgresql)
Several months ago I learnt from here how to perform multiple updates at once in MySQL using the following syntax INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z) ON DUPLICATE KEY UPDATE field=VALUES(Col1), field2=VALUES(Col2); I've now switched over to PostgreSQL and apparently this is not correct. It's re...
Working with WPF & SQL database
I'm into creating a simple contact management application to test how WPF works with SQL database. is there any WPF-code for that shows how to connect with SQL database ? save and update data? I'm using KaXML/Sharp-develop for WPF and using SQLite/MS-SQL server 2005. I can't use Visual studio express(not allowed to install,anyway that'...
create index using openquery
How do I create an index on a table that exist in a remote SQL Server database using the openquery syntax? ...
does SQL Server 2008 supports table level readonly?
Hello everyone, Does SQL Server 2008 support table level readonly -- i.e. I can mark some table as readonly so that we could improve performance (for example, no transaction log needed for the readonly table)? thanks in advance, George ...
iSeries DB2 - Is there any way to select the identity value from an insert statement?
I know we're rare, us poor folk that are using iSeries for DB2/AS400, but I'm hoping someone can answer this simple question. Is there any way to return the identity value from an insert statement without using two lines of SQL? I'm being forced to use inline SQL in C# to perform an insert, and then I need to use the identity generated...
How do I remove "duplicate" rows from a view?
I have a view which was working fine when I was joining my main table: LEFT OUTER JOIN OFFICE ON CLIENT.CASE_OFFICE = OFFICE.TABLE_CODE. However I needed to add the following join: LEFT OUTER JOIN OFFICE_MIS ON CLIENT.REFERRAL_OFFICE = OFFICE_MIS.TABLE_CODE Although I added DISTINCT, I still get a "duplicate" row. I say "duplicat...
How to group this query so it behaves correctly?
I have a table called Stock and another called Listed, within the Stock Table is a Status Code that indicates when something is at the front of the queue of items of stock - I want to be able to find the most recently added item and set this to be the "front of queue" status. For example to get all the items listed and then order them by...
After ExecuteScalar: Object reference not set etc
What code I should add to accept null from WHERE statement. { int numApprovals = 0; string sql = "SELECT COUNT(Type) AS OpenforApproval " + "FROM dbo.LeaveRequest " + "WHERE Type IN (2, 3, 4, 5, 6, 8, 13, 14, 16, 22) " + "GROUP BY MgtApproval " + "HAVING MgtApproval IS NULL"; //"SELECT COUNT(Ef...
Character set mismatch
Hi All, I have a SQL statement like this: SELECT a.ColumnA, b.ColumnB || CASE WHEN TRIM(c.ColumnC) IS NOT NULL THEN ' (' || c.ColumnC || ')' ELSE '' END AS ClassName FROM TableA a INNER JOIN TableB b ON a.SomeColumn = b.SomeColumn INNER JOIN TableC c on a.SomeCol = c.SomeCol I'm getting an error "Character set mismatch" at the...
Proper DateTime Format for a Web Service
I have a webservice with a method which is called via a xmlhttprequest object in my javascript. The method accepts a datetime parameter which is subsequently converted to a string and run against the database to perform a calculation. I get the value from m_txtDateAdd and send off the xmlHttprequest <asp:textbox id=m_txtDateAdd tabInde...
Pull an xml schema from a sql 2005 database
In Microsoft SQL Server 2005, there is the option to store within the database under "Database -> Type -> XML Schema Collection" a XML schema to validate against if you are viewing the database within SQL management studio. What I need to do is store the schema under the specified path above, and then within code pull that schema out t...
Dynamic query in SSIS Package
I have an OLE DB Command component in an SSIS Package that runs an update statement against a SQL table. Now, my statement initially is like: update myTable set columnA=?, columnB=? where columnC=? Where the three "?"'s are inputs to my component from another Conditional Split component. I can link each column name with each input par...
Mono C# SQL Update "Concurrency violation"
Every time I try and update a row via SqlDataAdapter.Update() in Mono, I get: Unhandled Exception: System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 records. at System.Data.Common.DbDataAdapter.Update (System.Data.DataRow[] dataRows, Sy stem.Data.Common.DataTableMapping tableMapping) [0x00000] Th...
Primary Key Sorting
Is a table intrinsically sorted by it's primary key? If I have a table with the primary key on a BigInt identity column can I trust that queries will always return the data sorted by the key or do I explicitly need to add the "ORDER BY". The performance difference is significant. ...
How Do You Save An Image Out of a SQL Database Into the File System Using only SQL?
Similar to this question only the other way of flow. http://stackoverflow.com/questions/416881/insert-picture-into-sql-server-2005-image-field-using-only-sql I need to be able to save a image field out into the file system and be able to name the file with only using SQL. I don't want to use TEXTCOPY either because I need to use the c...
How can define a composite primary key in SQL?
How can I define a composite primary key consisting of two fields in SQL? I am using PHP to create tables and everything. I want to make a table name voting with fields QuestionID, MemeberID, and vote. And the QuestionID and MemberID will be the primary keys. How should I do this? ...
How to build a category table
Sorry for the not so great title, but I'm curious how people build a category table in SQL given the following example Two columns: ID, CATEGORY Which of these ways would you name the columns? ID, CATEGORY ID, DESCRIPTION CAT_ID, CAT_DESC Or something else? Just wondering what other people do. Sorry if this is less than clear. ...