I've got a sqlite table actions that looks something like this: uuid varchar (36) actiondate int username varchar (16) mood int bonus int status varchar (80) ... bunch of other similar fields (all short varchar or int fields) This design seems to be sufficiently performant for most types of queries, but struggles a bit with a particul...
I know that logically, there are some cases where NULL values make sense in a DB schema, for example if some values plain haven't been specified. That said, working around DBNull in code tends to be a royal pain. For example, if I'm rendering a view, and I want to see a string, I would expect no value to be a blank string, not "Null", an...
I am trying to alter a table in SQL server with a script. In the past I have always done this kind of thing through a GUI, but now I need to generate a script to do it for customers. I have an SQL Server database table that is like this: MyTable ------- ColA int NOT NULL ColB int NOT NULL ColC int NOT NULL ColD VARCHAR(100) The pri...
I have an MS Access table that has a many-to-one key to another one, and I would like a query to display those items in one row. For example, Foo, Bar and Baz link to the row with ID 1, so I would like to display: 1 | Foo | Bar | Baz instead of 1 | Foo 1 | Bar 1 | Baz ...
I have two tables, like these: Table People: VARCHAR Name INTEGER Age Table Message VARCHAR Message VARCHAR Name There are hundreds of insert and delete going on with the Message table with queries like this: insert into Message VALUES ('Hello there', 'John'); delete from Message where name = 'John'; My question is, is it worth whi...
Using SQL Server, I have... ID SKU PRODUCT ======================= 1 FOO-23 Orange 2 BAR-23 Orange 3 FOO-24 Apple 4 FOO-25 Orange I want 1 FOO-23 Orange 3 FOO-24 Apple This query isn't getting me there. How can I SELECT DISTINCT on just one column? SELECT [ID],[SKU],[PRODUCT] FROM [TestData] WHERE ([PRODUCT] = (SE...
I normally use fully-explicit transactions in my stored procs (BEGIN TRANSACTION .... COMMIT). I just ran across an old one that instead uses "CHECKPOINT" at certain places in the code. This won't do the same thing, right?? Even if the database is in simple mode, the whole thing will still run as one big transaction, even with a bunch o...
Hello, Using SQL Server (2005/2008) Is there an easy way to suppress the repeating sub & grand totals? select h.salesmanno ,c.custname ,h.amt ,sum(h.amt) over (partition by salesmanno) as subtotal ,sum(h.amt) over (partition by null) as grandtotal from hdr h, cust c WHERE h.custno = c.custno and h.hdate = '6/8/2009' basica...
What is the best way to attempt to debug and pinpoint the code causing a SQL transaction to be elevated to a DTC? I'm working with an existing API that is failing for me alone, so i believe it to be some kind of config error or something wacky. I'm not having much luck finding the culprit via debugger. Are there better ways to find the o...
Is there a SQL library handling simple sql related tasks for ASP classic? What I have in mind is sql to array, multiple insert, input sanitizing etc. Thanks ...
I hope this question isn’t too “right field” and I'll be upfront in saying I'm a newb compared to many people on stackflow... I want to compare object representations of images, audio and text for an AI project I am working on. I'd like to convert all three inputs into a single data type and use a central comparison algorithm to determi...
Is there a way to get a value of a local variable specified by its name dynamically in SQL Server SP? declare @foo int declare @bar int declare @variable_name varchar(10) set @variable_name = '@foo' print -- magic happens here - how to print the value of the variable -- which name is stored in @variable_name, in this case @foo ...
I have a table called baskets with these columns: basket (name of the basket) colour (colour of the basket) apples (the number of apples in the basket) bananas (the number of bananas in the basket) oranges (the number of oranges in the basket) pears (the number of pears in the basket) peaches (the number of peaches in the basket) Wit...
This is sort of a general question that has come up in several contexts, the example below is representative but not exhaustive. I am interested in any ways of learning to work with Postgres on imperfect (but close enough) data sources. The specific case -- I am using Postgres with PostGIS for working with government data published in...
Hi, I want to pass the Oracle SQL Expert Certification (1Z0-047). I want to know which books should I buy to prepare myself for this exam. If somebody did it...how was it? ...
Here's my scenario... SQL Role Staff_User Scheme People Tables People.Persons People.PhoneNumbers Views People.vtPersons - The vtPersons view filters the data from the Persons table showing only that which belongs to the currently logged in user. People.vtPhoneNumbers - The vtPhoneNumbers view filters the data from the P...
How can i resolve a hostname in t-sql? a 2000 compatible method is preferred. Although something that works on 2005/2008 would also be helpful. eg. If i have the hostname stackoverflow.com i want to return 69.59.196.211 ...
i am trying to create a table with a row where id=0 but the identity insert column starts at 1,1 so all following use the identity insert i have to execute DBCC CHECKIDENT ('Foo', RESEED, 0); after i manually insert (using a script) to get the next row to have id=1 is there another way to get id=0 for my first row which is from the i...
Here's the deal. I've got articles and issues, both of which can have a release date. (Issues must, articles can.) Whichever date is earlier is the release date. I've got all the logic for that basically worked out, and it all works perfectly, except I can't quite figure out the order clause to order by this release date. What is the cl...
I want an equivalent linq to object query for the below sql query SELECT SUM(AMOUNT) FROM ACCOUNTS a INNER JOIN DETAIL d ON a.CODE = d.CODE INNER JOIN ACCENTRIES e ON d.EID= e.EID and e.EDATE > '1/1/2000' GROUP BY d.CODE Thanks in advance ...