i have two tables products and reviews each product has several reviews linked by a foreign-key product_id in the reviews table additionally each review has a field called rating with a decimal value i wish to get the names of all products whose average rating is above a certain threshold something in the lines of SELECT p.name FROM pr...
What's the equivalent of Oracle's RowID in SQL Server? ...
How can I work around the Oracle's limitation of not allowing subqueries in triggers. Here's an example trigger I'm trying to create, but am unable to because I can't use a subquery. CREATE OR REPLACE TRIGGER trigger_w_subquery AFTER UPDATE OR INSERT ON project_archiving FOR EACH ROW WHEN (old.archiving_status <> new.archiving_status ...
Hey guys, I'll get straight to the point. I'm building a website on an existing database, which did not have relationships to keep it consistent. I'm now trying to add relationships to take care of this consitency problem. simplified: I have a stock items table, locations table and a parts table. the stock items table uses ID's that ...
Hi, I do an import and convert columns to other datatypes. During the import I check, if I can convert the values stored in the columns into the target datatypes. If not I put a default value, e.g. I convert a string to int and when I cannot convert the string I insert -1. At this moment it would be great if I could log the erroneous e...
Hi, i want to realize a construct in MS SQL that would look like this in Oracles PL/SQL: declare asdf number; begin for r in (select * from xyz) loop insert into abc (column1, column2, column3) values (r.asdf, r.vcxvc, r.dffgdfg) returning id into asdf; update xyz set column10 = asdf where ID = r.ID; end loop; end; Any i...
Can you give an MS Access equivalent to MySQL 'CREATE TABLE IF NOT EXISTS ...'? Update Something like this IF <no such table> CREATE TABLE history(<fields>) would be suitable as well ...
Oracle: select systimestamp from dual MySQL: select current_timestamp SQL Server: select current_timestamp PostgreSQL: select current_timestamp The question is, how can I get the current timestamp in HSQLDB? I use version 1.8.0.10 ...
I need to achieve this update [table] set [column c] = ( select [column a] + ' ' + [column b] from [table] ) but I get this error message Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. How can I achieve the desired effect withou...
Hi, The query below is perfectly functional, and queries a single table to find the last 50 usernames added via a sequential userid number column. Logic so far is to: find out the highest userid; subtract 50 from that; pull usernames back where greater. However, it doesn't look elegant, and uses two subqueries to achieve it's goal: ...
Hi there, While I was using web.sitemap the security Trimming on my menu was working fine. Now that I have switched to a Sql Site Map Provider, the security trimming just stopped working. <siteMap enabled="true" defaultProvider="AspNetSqlSiteMapProvider"> <providers> <add name="AspNetSqlSiteMapProvider" type="SqlSiteMapProvi...
One of my tables in my SQL database has a growth rate of two nibbles per nanosecond. I was wondering how many megabytes per day that is and should I be worried? My hard disk is 150 GB. ...
Hi All, I am trying to execute following syntax under transaction but it throws error:- this is the script that I am executing under transaction:- IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) -- full text search is installed. Run the necessary procedures BEGIN declare @dbName nvarchar(128) select @dbName = DB_Name() ...
I have a script that generates DDL scripts to define materialized views for a normalized database. Some tables have columns like "owner" that point to a particular database user, which I can then create views for that will show only the rows of a table that the current database user created. Such views in some cases would be beneficial...
I need a DataRow with every column of a table T without the column Y, which holds the primary key. The SQL is: SELECT * FROM T WHERE Y=N LIMIT 1 "LIMIT 1" is a mysql dialect so that is the reason I need an agnostic ISQLQuery (query.SetMaxResults(1);) to do the work. I can get a row in the form of object[] using this code: ISQLQuer...
The below MSSQL2005 query is very slow. I feel like their ought to be a way to speed it up, but am unsure how. Note that I editted the inner join to use select statements to make it more obvious (to people reading this question) what is going on, though this has no impact on speed (probably the Execution plan is the same either way). ...
I have 2 tables set up like this (irrelevant rows omitted): > product - id - user_id > thread - id - prod_id - user_id - due_date now I want to create a query that picks all rows from thread table that either: have a thread.user_id of (for example) "5" have a prod_id where the product.user_id is "5" I am assuming ther...
Hello, Do you think Microsoft's RAP program worth the money you pay ? Any suggestions ? Thanks ...
What is the best way to develop on SQL Server 2005 machine locally while you get your database design and application design smoothed out and then deploy to a shared host? In the MySQL / phpMyAdmin world you have an option to export the table as the DDL Statement with the data represented as a bunch of inserts following the DDL. Using t...
I am moving old project that used single table inheritance in to a new database, which is more structured. How would I write a SQL script to port this? Old structure I've simplified the SQL for legibility. CREATE TABLE customers ( id int(11), ... firstName varchar(50), surname varchar(50), address1 varchar(50), address2 v...