Is there an elegant way to do this: SELECT Cols from MyTable WHERE zip = 90210 OR zip = 23310 OR zip = 74245 OR zip = 77427 OR zip = 18817 OR zip = 94566 OR zip = 34533 OR zip = 96322 OR zip = 34566 OR zip = 52214 OR zip = 73455 OR zip = 52675 OR zip = 54724 OR zip = 98566 OR zip = 92344 OR zip = 90432 OR zip = 91532 OR ... (zip codes...
Do you know a good source to learn how to design SQL solutions? Beyond the basic language syntax, I'm looking for something to help me understand: What tables to build and how to link them How to design for different scales (small client APP to a huge distributed website) How to write effective / efficient / elegant SQL queries ...
Hi, Here is my SQL Statement which is not returning DISTINCT Thread Titles. SELECT DISTINCT TOP 5 tblThread.Title, tblPost.Date FROM tblPost INNER JOIN tblThread ON tblPost.ThreadID = tblThread.ThreadID ORDER BY tblPost.Date DESC The common field between tblThread and tblPost is ThreadID. What I want this to do is return The late...
I have had a few problems with log files growing too big on my SQL Servers (2000). Microsoft doesn't recommend using auto shrink for log files, but since it is a feature it must be useful in some scenarios. Does anyone know when is proper to use the auto shrink property? ...
We are porting an app which formerly used Openbase 7 to now use MySQL 5.0. OB 7 did have quite badly defined (i.e. undocumented) behavior regarding case-sensitivity. We only found this out now when trying the same queries with MySQL. It appears that OB 7 treats lookups using "=" differently from those using "LIKE": If you have two valu...
What is better as far as performance goes? ...
If i have a parameterized SQL statement like this: SELECT * FROM table WHERE my_field = :field_value Does anyone know if PDO will recognize this(see below) as the same SQL statement and use the cache instead of assuming it's a completely different SQL statement: SELECT * FROM table WHERE my_field = :new_field_value So, I guess the...
I'm using SQL Server 2005. I have a field that must either contain a unique value or a NULL value. I think I should be enforcing this with either a CHECK CONSTRAINT or a TRIGGER for INSERT, UPDATE. Is there an advantage to using a constraint here over a trigger (or vice-versa)? What might such a constraint/trigger look like? Or is the...
How to truncate and shrink large log files in SQL Server 2005? How to apply truncation at regular intervals? Is there any difference between truncation and shrinking? Thanks in advance ...
Here's the situation: we have an Oracle database we need to connect to to pull some data. Since getting access to said Oracle database is a real pain (mainly a bureaucratic obstacle more than anything else), we're just planning on linking it to our SQL Server and using the link to access data as we need it. For one of our applicati...
Hi, I have a problem that I would like have solved via a SQL query. This is going to be used as a PoC (proof of concept). The problem: Product offerings are made up of one or many product instances, a product instance can belong to many product offerings. This can be realised like this in a table: PO | PI A | 10 A | 11 A | 12 B ...
Hello, after a lot of research i think its time to ask some questions. So this is how it goes. I have built a database in Ms Access. There i have a table called Customers which also has a cell called Employee type: integer. I also built a program in C++ which controls all data. I have a problem thought. lets say i have a string like th...
How can you join between a table with a sparse number of dates and another table with an exhaustive number of dates such that the gaps between the sparse dates take the values of the previous sparse date? Illustrative example: PRICE table (sparse dates): date itemid price 2008-12-04 1 $1 2008-12-11 1 $3 2008-12-15...
I have a set of data that models a hierarchy of categories. A root category contains a set of top-level categories. Each top-level category contains a set of sub-categories. Each sub category has a set of organizations. A given organization can appear in multiple sub categories. The leaf nodes of this hierarchy are organizations. An o...
Is there any way to map to a network drive by using a stored procedure? I have tried: xp_cmdshell 'net use Q: [shared_network_drive] [pwd] /user:[username]' but I got an error saying something like 'System error 1312 has occurred.' 'A specified logon session does not exist. It may already have been terminated.' However, when I ru...
Your basic SP with a default parameter: ALTER PROCEDURE [usp_debug_fails] @DATA_DT_ID AS int = 20081130 WITH RECOMPILE AS BEGIN /* Usage: EXEC [usp_debug_fails] WITH RECOMPILE */ -- Stuff here that depends on DATA_DT_ID END The same SP with a local that is hardcoded. ALTER PROCEDURE usp_debug_works] WI...
Does anyone have a method to understand complex SQL statements? When reading structural / OO code there are usually layers of abstraction that help you break it down into manageable chunks. Often in SQL, though, it seems that you have to keep track of what's going on in multiple parts of a query all at the same time. The impetus for t...
I work at a company that has some very non-standardized SQL conventions (They were written by Delphi Developers years ago). Where is the best place that I can find SQL industry standard convention definitions that are most commonly used? ...
I have an assortment of database objects (tables, functions, views, stored procedures) each scripted into its own file (constraints are in the same file as the table they alter) that I'd like to be able execute in an arbitrary order. Is this possible in SQL Server 2005? Some objects as an example: Table A (references Table B) Table B (...
Hello, I have a large-ish Oracle table containing rows representing units of work, with columns for start time and end time in addition to other meta-data. I need to generate usage graphs from this data, given some arbitrary filtering criteria and a reporting time period. E.g., show me a graph of all of Alice's jobs for the 24-hour pe...