I want to use a computed bit column that will be true if another column in the table is not null. What's the correct formula for this? HasLabel = computed column (bit) Label = varchar NULL The following formula does not validate - what am I missing? Formula for HasLabel = Label IS NOT NULL ...
I understand that DML technically encompasses SQL verbs which merely query but do not modify persistent data. (See, e.g., wikipedia or oracle or orafaq) However, I often wish to refer to "all and only those SQL verbs which modify stored/persistent data" -- basically INSERT, UPDATE, DELETE but not a plain SELECT. Is there an official/st...
Hi, I have one weird requirement. In a SQL table, I have two columns of same type, say type ObjA. The values in these two columns repeats. e.g. ObjA_Column1 ObjA_Column2 obj1 obj2 obj2 obj3 obj1 obj2 obj3 obj4 obj4 obj5 I want a ...
Hello! I have a SQL table with a datetime field. The field in question can be null. I have a query and I want the results sorted ascendingly by the datetime field, however I want rows where the datetime field is null at the end of the list, not at the beginning. Is there a simple way to accomplish that? ...
I have three columns with three values that can be set for each column Column_1 | column_2 | column_3 ________ | ________ | ________ COMPLETED| FAILED | PENDING COMPLETED| COMPLETED| COMPLETED FAILED | COMPLETED| COMPLETED COMPLETED| PENDING | COMPLETED COMPLETED| COMPLETED| PENDING COMPLETED| COMPLETED| COMPLETED COMPLETED| COMPLE...
I want to migrate to Kohana with my small websites and I'm trying to separate the SQL, PHP and the view, but I've some problems with this one. I have to tables. Every category can have multiple products. Categories table id category Products table id category_id product This was my previous code (converted to Kohana's query bui...
I would like to do something in Java (using iBatis, JDBC, etc., really in SQL) like: SELECT SUM(rowName) FROM myTable WHERE id = [myArrayOfIds] Where myArrayOfIds can be almost any length. Now I know you can do: SELECT SUM(rowName) FROM myTable WHERE id IN (x, y, z) but what happens for longer lists? For example my list could be as...
Setup: create table main(id integer unsigned); create table test1(id integer unsigned); create table test2(id integer unsigned); insert into main(id) value(1); insert into test1(id) value(1); insert into test1(id) value(1); insert into test2(id) value(1); insert into test2(id) value(1); insert into test2(id) value(1); Using: ...
Hello All, I am having a problem in one of the teams that I am working in. One of the guys is a bit SQL happy in my opinion and wants to store the log information generated by a small python FTP downloader into a database, instead of just a nice formatted text file. Now its always been my opinion that a database should only be used if...
Hi all, can anybody explain me the below code for trigger?Please note that the above code was written by one of my class mate and I cannot understand anything in it. I am not able to understand it. Also if there is any other way to accomplish the same task then please let me know. CREATE trigger [dbo].[trg_InsertInBookIssuedDetails] ...
setup: mysql> create table main(id integer unsigned); mysql> create table test1(id integer unsigned,body text); Query OK, 0 rows affected (0.84 sec) mysql> insert into main(id) value(1); Query OK, 1 row affected (0.16 sec) mysql> insert into test1(id,body) value(1,'something1'); Query OK, 1 row affected (0.27 sec) mysql> insert into...
I apologise for the long-winded, rantyness of this but I'm up at 3am dreading having to go to work tomorrow and deal with this database... it just feels wrong but maybe I'm wrong and just like things done my way... Please tell me what you think. Our database schema looks something like: page: id, label, contentid, parentpageid content...
I have a text field in my web app where I want to do auto-completion (e.g. the user types "St" and I can suggest "Steve"). The names I'm matching against are in a SQL database table of users. My question is, how can I make this happen in a way that will scale to massive amounts of users? There's DB full text search or something like ...
I am migrating from one system to another and in the process, I will be running both systems concurrently. I need to be able to synchronize uni-directionally from one table to another while maintaining each table's primary keys. In this example, I have two tables (A) and (B). I need to synchronize value1 and value2 (below) from table B ...
I am attempting to use a LIKE clause in a SQL statement to match a certain pattern within Oracle. I would like to do something as such: LIKE '[A-Z][A-Z][1-4]%' ..but I can't use a regex because this is on Oracle9i (regex support came in 10g). I am attempting to match something that has two characters before it, then a number between...
I have a table with the following columns: id int(10) winner int(10) profit double created datetime I'm trying to create a query that returns the largest drawdown, or "peak to trough decline" in terms of the profit column. (More info on drawdown here.) Ideally it would return the value for created for the beginning and end of the dra...
Is there a way to limit the code generated to specific tables in a database? My database has a few hundred tables and I only really want to use SubSonic on a handfull of them. ...
I have a function written in pl/pgsql which works in the same way as the one described below: CREATE FUNCTION reffunc(text) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT col FROM test WHERE c1=$1; RETURN $1; END; ' LANGUAGE plpgsql; I want to be able to use this with a single select command as opposed to the documented way w...
I have 2 tables that are similar but not the same so a union is not a possibility. I need to combine the tables bearing in mind there's about 40 columns where only 20 are common to both. Any ideas on the best approach? Table1 ActivityCategory ActivityType Nationality Language ----------------------------------------------------...
The purpose of this query is to bring back products and their prices for products on sale and the price should be from the date closest but not equal to the date passed in, essentially the most recent price available. There are not price records for every day. Something feels a little wrong about having the aggregate select statement i...