I'm looking for some "inference rules" (similar to set operation rules or logic rules) which I can use to reduce a SQL query in complexity or size. Does there exist something like that? Any papers, any tools? Any equivalencies that you found on your own? It's somehow similar to query optimization, but not in terms of performance. To sta...
I'm working with a client that starts almost all of their WHERE clauses in Oracle with 1=1. Forgive my ignorance, but isn't this a no-op? Are there any negative consequences of this usage? Here's a scrubbed example: SELECT gpz.zname ,gpp.pname FROM table1 gpp INNER JOIN table2 gpz ON gpz.p_id = gpp.p_id WHERE 1=1 ...
I'm doing Select * From table Where Col IN (123,123,222,....) If I put more than ~3000 numbers in the IN clause SQL throws and error. Does anyone know if there's a size limit or anything?!! Thank you. ...
I have table A and table B, same schemas. I want to insert certain rows from table A into table B. For example, insert into table B all rows from table A with column 'abc' > 10. Couldn't figure out how to do it ...
I've got a simple query (postgresql if that matters) that retrieves all items for some_user excluding the ones she has on her wishlist: select i.* from core_item i left outer join core_item_in_basket b on (i.id=b.item_id and b.user_id=__some_user__) where b.on_wishlist is null; The above query runs in ~50000ms (yep, the number is co...
Hello overflowers ;) I'm implementing home brew ACL system in web app and it gives me hard time. I believe this is trivial, just can't get my head around it. I have 3 tables in one to many relationship: resource, perms_group, perms_users where perms_group is a key table while perms_users allowes me to fine tune access per user basis if...
Hi - I'm querying an Iseries from ODBC in my app and am trying to perform a query that returns results from 2 tables. I need to join the tables but the tables are in different libraries. I don't want to use library identifiers in my query as my libraries change as I move from dev>qa>prod. However, I am certain that these tables will only...
I want to transfer data from a vertical db layout like this: --------------------- | ID | Type | Value | --------------------- | 1 | 10 | 111 | --------------------- | 1 | 14 | 222 | --------------------- | 2 | 10 | 333 | --------------------- | 2 | 25 | 444 | --------------------- to a horizontal one: ---------...
Hi, our stored procedures have developer comments and headers and as part of our deployment process we would like to remove these from the customer copy. Is there a method of achieving this within SQL Server 2005 or with another tool? ...
I have five tables: models: id, name, specification models_networks: id, model_id, network_id networks: id, name, description countries_networks: id, country_id, network_id countries: id, countryName, etc, etc the models table is connected to the networks table via models_networks with a many to man...
Can I make this type of SQL query on LINQ to SQL? (this query is only a example) select * from orders as o left outer join (select * from ordersdetail where status = 'A') as od on o.id = od.orderid What I need is how can I put a subquery inside de "from" statement. Thanks ...
I have a poor man's replication setup that I can't do anything about. Some identifying data (basically primary key) from a call_table is copied into another table via a simple trigger, and then the "replication server" runs a stored procedure to copy the data from the queue table to a #temp table (to prevent locking in SQL 6.5 is the cas...
I have 3 tables: users (id, name) currency (id, name) accounts (id, user_id, currency_id, amount) And I want to read the data from accounts and present it in table-like view: owner currency1 currency2 currency3 1 0 0 0 2 10 20 30 3 0 5 10 Where owner is ID of accounts.ow...
I would like to know about how NULL values affect query performance in SQL Server 2005. I have a table similar to this (simplified): ID | ImportantData | QuickPickOrder -------------------------- 1 | 'Some Text' | NULL 2 | 'Other Text' | 3 3 | 'abcdefg' | NULL 4 | 'whatever' | 4 5 | 'it is' | 2 6 | 'technically' |...
This seems like it should be something I already know. We need to run a bunch of sql updates in a transaction, and rollback if one of them fails. We also want to print a status message since we'll be running a large number of these. This would be simple if I were doing it in a general purpose programming language. But I am trying to...
What I'm looking for is a driver that is included with the .NET runtime to reference in my connection string (DRIVER={ ... }). I'm currently referencing "MySQL ODBC 3.51 Driver" but this must be installed on the target machine. I'm not against redistributing the driver but if there is one I can use that is built into .NET, I would much...
The code below is what I am trying to use in order to get the count of one column and the averages of two other columns from multiple tables and then put the results into another table. I thought this would work, but the count that is being put into the new table is incorrect as are the averages. A lot of times the averages are outside t...
What is the fastest way to loop thru a Query in T-SQL . 1) Cursors or 2) Temp tables with Key added or any thing else. ...
I have 1000 rows of data but need to only display 100 rows at a time. I would like navigation buttons (like on google next, prev) to go thought the groups of 100 My C cgi application runs and exits every new query, so it can't save states. Question is: What is the most efficient way to save the current state of the OFFSET, so I can n...
Is there a reason to use a single incrementing field for a primary key instead of multiple fields that actually represent the unique record? I'm working on an existing php application, and the tables all seem to have a single 'id' key instead of using the 2 or more fields that are actually unique to the record (like user, auction, bid)...