sql

What is the best way to implement this SQL query?

I have a PRODUCTS table, and each product can have multiple attributes so I have an ATTRIBUTES table, and another table called ATTRIBPRODUCTS which sits in the middle. The attributes are grouped into classes (type, brand, material, colour, etc), so people might want a product of a particular type, from a certain brand. PRODUCTS produ...

The "right" way to do stored procedure parameter validation

I have a stored procedure that does some parameter validation and should fail and stop execution if the parameter is not valid. My first approach for error checking looked like this: create proc spBaz ( @fooInt int = 0, @fooString varchar(10) = null, @barInt int = 0, @barString varchar(10) = null ) as begin if (@fooInt = 0 an...

What is the SQL Server equivalent to Oracle's Virtual Private Database?

What is the SQL Server equivalent to Oracle's Virtual Private Database (VPD)? ...

sql 2005 - The column was specified multiple times

Hi, I am getting the following error when trying to run this query in sql 2005: SELECT tb.* FROM ( SELECT * FROM vCodesWithPEs INNER JOIN vDeriveAvailabilityFromPE ON vCodesWithPEs.PROD_PERM = vDeriveAvailabilityFromPE.PEID INNER JOIN PE_PDP ON vCodesWithPEs.PROD_PERM = PE_PDP.PEID ) AS tb; Error: Th...

Manually inserting varbinary data into SQL Server

Hi, We have a SQL Server table for user settings. Originally the settings were domain objects which had been serialized as XML into the table but we recently begun serializing them as binary. However, as part of our deployment process we statically pre-populate the table with predefined settings for our users. Originally, this was as...

how do I make a select which always returns zero rows

I want to determine if a column exists in a table for any jdbc driver. In order to know the columns in a table, I have to make a query to the table and then get the ResultSetMetaData for the info, but this is pretty expensive in like 99% of times. In mysql I have: SELECT * FROM tablename LIMIT 0,0 In Intersystems caché I have: SELEC...

Grouping result by date in mysql

This SQL statement SELECT `ip`, `when` FROM `metrics` WHERE `vidID` = '1' GROUP BY DATE('when') returns one result, even though multiple are present in the table with different dates. I've tried using DATE_FORMAT as well. Am I doing something stupid? When is a timestamp column with full timestamp, including hours, minutes and seconds...

Searching across multiple tables (best practices)

I have property management application consisting of tables: tenants landlords units properties vendors-contacts Basically I want one search field to search them all rather than having to select which category I am searching. Would this be an acceptable solution (technology wise?) Will searching across 5 tables be OK in the long run...

How can I enforce compound uniqueness in MySQL?

I have run into a situation where I want to ensure that a compound element of a table is unique. For example: Table ( id char(36) primary key, fieldA varChar(12) not null, fieldB varChar(36) not null ) I don't want fieldA and fieldB to be a compound primary key, since they change frequently, and 'id' is used as a reference thro...

Extending SQL query to include records beyond a given date.

I have a query limited by a date range: select * from mytable where COMPLETIONDATE >= TO_DATE('29/06/08','DD/MM/YY') and COMPLETIONDATE <= TO_DATE('29/06/09','DD/MM/YY') The table is a log for activities for a ticket system. The content may be something like: ticket_id|activity|completiondate 1 1 some 1 2 som...

PostgreSQL: Defining a primary key on a large database.

I am planing a database to store lots of text. (blog posts, news articles, etc.) The database needs to have the title, content (50k characters max), date, link and language fields. The same content can't occur on one link. Old content (older then 30 days, for example) will be deleted. Now, the problem is the primary key. I could just se...

# in SQL Query

I have somebody elses code (C# ASP.Net) which contains the following query: string query = "SELECT distinct(destinations.name) as Destinations FROM destinations, flights WHERE destinations.d_ID = flights.d_ID AND flights.Date = #" + date.ToShortDateString() + "#"; I could not find w...

Noob question on SQL and sqlite triggers

I’m trying to write a trigger for sqlite and just running into all kinds of problems. In truth I think my real problem is with my poor background in the sql language. Anyway here goes… I have two tables Table1 and Table2. Table1 has a column named time (which is a 64bit integer time). I need a trigger that watches for a new row bein...

Recommendation for a good, lightweight, free SQL IDE?

I'm looking for a free and lightweight SQL IDE that can support MS SQL and Oracle. I have tried Aqua Studio and Rapid SQl, and they both are not free, and a bit bloated. Any recommendations? ...

Sparse dot product in SQL

Imagine I have a table which stores a series of sparse vectors. A sparse vector means that it stores only the nonzero values explicitly in the data structure. I could have a 1 million dimensional vector, but I only store the values for the dimensions which are nonzero. So the size is proportional to the number of nonzero entries, not ...

SQL Server Inserting Decimal, but selecting Int

I have a table with two decimal(18,0) fields. I am inserting into this table, two decimal values. For example, 1.11 When I select from the table (with no casts), I get 1. I'm losing all percision and I have no clue why. insert into TEST values (153, 'test', 'test', 1, 1, 1.11, 1.11) Select * from TEST and they are 1 and 1 instead of...

SQL join with a range of values (int ranges, date ranges, whatever)

I have two tables, the first is a big table (millions of rows), with the most interesting column being an integer I'll just call "key." I believe this solution would be identical for date or datetime ranges as well though. The second table is much smaller (thousands of rows) with a bunch of attributes that are interesting to me which a...

Is it possible to do a bulk update?

Is it possible to bulk update? I did a build insert, now I would like to update each of the user IDs to increase the count. Here's a sample of my build insert void updateMediaForSubscribers(long userId, long mediaId, Media_Base.Catagory cat, DateTime currentDate) { command.CommandText = "INSERT INTO user_media_subscription ...

Dataset's TBytes column and SQL VarBinary field combination

Hi, select convert(varbinary(8), 1) in MS SQL Server produces output : 0x00000001 On assigning the above query to dataset in Delphi, and accessing field value, we get byte array as [1, 0, 0, 0] . So Bytes[0] contains 1. When I use IntToHex() on this bytes array it would result me value as "10000000" . Why is IntToHex considering it...

can I prefix columns in a join in jet (access)?

I've got a demo app, using php with an Access/Jet backend. I've got these (simplified) tables: People ------ ID BankID // foreign key in Banks Name Address Banks -------- ID Name Address I'm doing a join: SELECT * FROM People LEFT JOIN on People.BankID = Banks.ID What's the best way to avoid name clashes? This works: SELECT Pe...