sql

Cast Integer to Boolean in Informix

I need to design my schema in Informix such that querying a BOOLEAN with an INTEGER type will work. e.g. SELECT id FROM mytable WHERE isavailable = ? works if I provide either a boolean False or an integer 0. I know I need to set up the schema with some kind of cast, but I'm not sure how. The reason for doing this is a bug in another par...

Skip updating SQL row with empty values?

I have a table with several columns that allow NULLs. How would I go about writing a SQL Query that will allow me to skip that column in an update if the value is "empty". Should I use a boolean flag letting the query know to update that value? I do something similar in a SELECT like this SELECT * FROM table WHERE (@boolFlag = 1 OR col1...

How to optimize this linq query?

I have the following linq query: var allnews = from a in db.News where !(from c in db.NewsViews where c.UserGuid == thisUser.UserGuid select c.NewsGuid).Contains(a.NewsGuid) orderby a.Date descending ...

postgres public schema function aliasing

Hi all, I am currently running postgres 8.4.4 and I have the need to override calls to functions that reside in the public schema of my database. For instance in pg_catalog there exists a function upper(text) I have a function placed within the public schema that overrides upper(text) My question comes down to ...

Converting SQL number into a time, to perform date comparisons.

I am struggling with an SQL time comparison on two fields that have been set up as integers, rather than time stamps. I have some performance metrics in a database that the developer originally set up as int(8). The database contains the start and end times of a transaction. For example Some sample data may be id | start_time | end_ti...

System.ServiceModel.FaultException SQL Error, is it on their end?

So upon a method call to a webservice, I'm getting this error: "System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that ...

Is there a definitive or idiots guide to implementing strong web security using ASP.Net MVC 2/SQL 2008?

Hello. I'm about to begin building an e-commerce website using c#, ASP.Net MVC 2, IIS7 and SQL 2008. The site will allow users to login, make purchases, and manage their orders. Obviously, there's a need for strong security here. I've been searching around on SO and Google for a single definitive guide that covers enough on security ...

How to form this SQL Constraint, and does it work in reverse?

Say I have "Table A": Id | Col A 1 Z 2 I 3 Null ...and n number of tables that have this format: Id | A_FK | OtherInfo 1 1 "Some info" 2 2 "Some more info" 3 3 "Blah" ...where A_FK is a foreign key reference to the "Table A" Id. So there is one "Table A", and n number of "Table B's". For these "B" ...

SQL query about appointments....

Hello! Suppose we have this table.. CREATE TABLE `appointments` ( `idappointments` int(11) NOT NULL AUTO_INCREMENT, `timeStart` time DEFAULT NULL, `timeEnd` time DEFAULT NULL, PRIMARY KEY (`idappointments`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8$$ assumption Suppose that a range between timeStart and timeEnd ca...

Sort SQL records based on matched conditions

I have this query: SELECT * FROM table WHERE key LIKE '1,2,3,%' OR key LIKE '1,2,%' OR key LIKE '1,%' Is it posible to sort records returned from this query based on which conditions was matched first. I'd like to get all records that match key LIKE '1,2,3,%' first, then key LIKE '1,2,%' and the others after. For example, if I have t...

Eliminating NULLs when using CASE in SQL Server SELECT statement

I have a large, messy report to write that joins across 5 tables. There is one column in one table that is being used for several different values--essentially a "tag" column, where the tags are used in creative ways depending on what kind of miscellaneous metadata the users want to use. As a result, my query for the report returns 3 ne...

SQL Query to calculate number of occurrences from another query?

I have some code outputting data into html tables. I'm trying to have a column that shows the total number of answers for that specific questions (defined by intQAID). Since $result is a query defined in the same php script, how can I run a second query to run a 'mysql_num_rows' on to calculate how many answers for the question? $result...

Complex SQL Subquery to LINQ

I have this SQL query that is just impossible to get going in LINQ. select * from attribute_value t0 where t0.attribute_value_id in ( select t1.attribute_value_id from product_attribute_value t1 where t1.product_attribute_id in ( select t2.product_attribute_id from product_attribute t2 where t2.product_id in...

Multiple WHERE clause inputs to T-SQL stored procedure

I have a multiple selection listbox on a web form which lists various internal activity codes, and users can filter the results of a query based on the codes selected. I can easily add a single code as a parameter to my stored procedure and filter the results with a WHERE clause like so: WHERE ActivityCode = @ActivityCode OR @ActivityC...

SQL query WHERE problem

I have this query that is optimized for speed (that's why it might look a bit odd - got some help a while back). Want the exact result as this BUT I ONLY want results from within the LAST minute not older. This query returns the last 100 no matter what and not only results from the last minute. SessionGuid is not unique - in fact it'...

How to verify that an XML document downloaded fully

In my SSIS Package, I am using an XML file that was downloaded by another process. Before I proceed to other portions of work in the package, I want to ascertain that the file downloaded completely. I have the XSD file handy as well. How do I verify in SSIS that the XML file downloaded properly? ...

SQL Server like statement behavior for %%

In terms of performance, how does the like operator behaves when applied to strings with multiple % placeholders? for example: select A from table_A where A like 'A%' takes the same time to select than select A from table_A where A like 'A%%' ??? ...

SELECT FROM stored procedure?

If I have a stored proc in SQL Server 2008, I know I can run it from management studio like so: exec rpt_myproc @include_all = 1, @start_date = '1/1/2010' But I'm using an ad-hoc query tool that wasn't returning any results. So I asked it to give me the SQL it was running and it returns this: SELECT DISTINCT TOP 100000 [dbo].[rpt_myp...

SQL Best Practices - Ok to rely on auto increment field to sort rows chronologically?

I'm working with a client who wants to add timestamps to a bunch of tables so that they may sort the records in those tables chronologically. All of the tables also have an auto incrementing integer field as their primary key (id). The (simple) idea - save the overhead/storage and rely on the primary key to sort fields chronologically....

Query for newest record in a table, store query as view

I'm trying to turn this query into a view: SELECT t.* FROM user t JOIN (SELECT t.UserId, MAX( t.creationDate ) 'max_date' FROM user t GROUP BY t.UserId) x ON x.UserId = t.UserId AND x.max_date = t.creationDate But views do not accept subqueries. What this does is look for the la...