In a query, I have a column like this: case when X = 1 then case when Y <> 0 then YY else XX end else ZZ end as MyColumn Is there a way, in another column, to check the above column value referencing MyColumn instead of rewriting the case statement? I should value another column based on the MyColumn value. Thanks ...
I want to generate composite sequences in the following format: <Alphabet><2 digit numeric code> Each alphabet series will have numeric values ranging from 00 to 99. The initial value will be A00, the subsequent values will be A01, A02 and so on. Upon reaching A99, the next sequence should carry-on to B00. When the "B" series is exha...
Do you see a better way to read data from sqlite database into business collections? These are 2 methods of my Repository. I want to get all Schoolclass entites with/without referencing Pupil entities (see Left outer join) Is there anything that I can improve ? Haven`t found any good tips about that scenario in google so I tried mysel...
Whats the most efficient way to search for a sub string in SQLite? I'm looking at the LIKE operator. Do I have the right idea? Has this worked well for you? http://www.sqlite.org/lang_expr.html Thank You. ...
Howdy, I am working on a legacy app that is being extended to run in a multi-tenant configuration. The basic architecture takes the old application and adds a StoreID column to every table. Each tenant then sees the legacy tables through a set of views that filter on store id, something like: create view AcmeBatWings.data as select ...
I'm trying to use a join on three tables query I found in another post (post #5 here). When I try to use this in the SQL tab of one of my tables in myPHPAdmin, it gives me an error: #1066 - Not unique table/alias: 'm' The exact query I'm trying to use is: select r.*,m.SkuAbbr, v.VoucherNbr from arrc_RedeemActivity r, arrc_Merchant m...
I have a MERGE query to create an upsert operation on my database with data entered through my application. When I go to call it to save my data on large transactions (>5000) it takes a very long time (~20-40 seconds). Here's my MERGE statement MERGE TestTable AS target USING (SELECT @Guid) AS source (target.Guid = source.Guid) WHEN M...
Here is my object def: CREATE OR REPLACE TYPE FALCON.contacts AS OBJECT (phone VARCHAR2(50) ,phoneusage VARCHAR2(25) ,phonetype VARCHAR2(25) ,email VARCHAR2(150) ,phoneext VARCHAR2(25) ,anytext VARCHAR2(250)) Here is the table def: CREATE OR REPLACE TYPE FALCON.co...
Hi, Someone asked me today how they should store quest goals in a SQL database. In this context, think of an RPG. Goals could include some of the following: Discover [Location] Kill n [MOB Type] Acquire n of [Object] Achieve a [Skill] in [Skillset] All the other things you get in RPGs The best I could come up with is: Quest 1-* Que...
My question is similar to this one: http://stackoverflow.com/questions/3061804/choose-a-xml-node-in-sql-server-based-on-max-value-of-a-child-element except that my column is NOT of type XML, it's of type nvarchar(max). I want to extract the XML node values from a column that looks like this: <Data> <el1>1234</el1> <el2>Something</el2> ...
I want to select some rows based on certain criteria, and then take one entry from that set and the 5 rows before it and after it. Now, I can do this numerically if there is a primary key on the table, (e.g. primary keys that are numerically 5 less than the target row's key and 5 more than the target row's key). So select the row with ...
I have a problem with SQL Server 2008 full text search I have the following query SELECT * FROM cigars WHERE CONTAINS(cigars.*, @Search ) @Search is a value that is passed in through a stored procedure. But, thats not the problem. What is is if someone searches for say 'Punch' it works fine and I get all cigars that match. Where the...
I'm developing a search function for a website. I have a table called keywords with two fields id and keyword. I have two separate search queries for AND and OR. The problem is with the AND query. It is not returning the result that I expect. The printed SQL is : SELECT COUNT(DISTINCT tg_id) FROM tg_keywords WHERE tg_keyword='keywor...
Hi, I have a DTS package, which after running daily with no problems for a couple of years, has started to play up. Originally it was inserting data into a table which would then fire an insert trigger. The trigger used [inserted] to update three columns in the table. Usually, it was updating about 500,000 rows of inserted data. When t...
Well, NoSQL is a buzzword right now so I've been looking into it. I'm yet to get my head around ColumnFamilies and SuperColumns, etc... But I have been looking at how the data is mapped. After reading this article, and others, it seems the data is mapped in a JSON like format. Users = { 1: { username: "dave", passwo...
Say for example I have this table: Items: ------- id title price And I wanted to create a query that created a new row that for each item, it would query another table that matched the id, with the id in the other table, set the row another row in the other table. ...
I have a database Class_Books which links ISBNs (from Books table) to Class_ID's (from Classes table). I'm changing my Books table so the primary key is a Book_ID (autoincrement INT) instead of the ISBN. Is there a way to update Class_Books so it uses Book_ID now? ...
One of our system finally launch in production and during the peak hour (about 200 concurrent user at any one time), traffic can ramp up to 30,000 user transaction within an hour. What we notice is a strange behavior that right after our SQL server restarted, the performance is very fast. Even with 200 concurrent user at the beginning,...
I have a table where I have to relate groups of primary keys to one another. There is no information about these groups other than that they exist. Specifically, I am storing groups of Editions of Books together, so I can know that a certain set of books are editions of each other. I currently have a setup where I have an Edition_Group_...
My last quest was too vague, so I will revise it. I know the following isn't correct SQL syntax, however you should be able to get the gist of the query I'm trying to perform. select id, title, count(select x from otherTable where otherTable.id = thisTable.id) as new_row from thisTable I...