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...
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 (VPD)? ...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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? ...
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 ...
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...
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 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 ...
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...
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...