Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for...

ANSI 92 Recursive SQL Statement required

I am translating SQL Server SQL Statements into their ANSI generic equivalent at present, and am stuck with a recursive statement using a WITH statement. For the sake of concentrating on the...

Why isn't SQL ANSI-92 standard better adopted over ANSI-89?

At every company I have worked at, I have found that people are still writing their SQL queries in the ANSI-89 standard: select a.id, b.id, b.address_1 from person a, address b where a.id =...

Does Oracle 10g comply with the ANSI SQL-1992 substring function standard?

According to the ANSI SQL-1992 standard, page 132, this is the syntax for the ANSI substring function: <character substring function> ::= SUBSTRING <left paren> <character value expression>...

ANSI SQL 92: find last occurrence of character

I need a ANSI SQL 92 statement to change all characters following the last '/' character to lower case. On Sybase I would write: update table set col = left(col, len(col)-charindex('/',...

Comparisons with NULLs in SQL

ANSI-92 SQL mandates that comparisons with NULL evaluate to "falsy," eg: SELECT * FROM table WHERE field = NULL SELECT * FROM table WHERE field != NULL Will both return no rows because NULL...

Deleting old records while keeping a minimum number of records per parent

My question is similar to sql statement to delete records older than XXX as long as there are more than YY rows, but that question just deals with a single parent, I want to delete records for all...

Errors while trying to cast/convert VARCHAR to DATETIME in ANSI SQL

I have a column in a table where timestamps have been stored in VARCHAR format, but I need to compare these against a column of DATETIME values from another table to find time intervals, so I want...

Pattern matching SQL on first 5 characters

I'm thinking about a SQL query that returns me all entries from a column whose first 5 characters match. Any ideas? I'm thinking about entries where ANY first 5 characters match, not specific...

How to determine when multiple SQL queries are finished using PHP

I have a web app which is basically one page with a single form broken down into tabs, each containing data from separate database tables. The form action points to a save.php script which takes...

Excel table not importing data from properly functioning Access query

Alright, I have an Access DB which I am trying to import data from, into Excel. Specifically, I am attempting to import the results of a query which I wrote in Access. I wrote this query in Access...

Convert an Informix SQL query into ANSI-92 SQL?

What is the ANSI-92 equivalent of the following old Informix SQL query? select * from categories c, orders o, outer (employees e, person p) where c.categoryid = o.categoryid and...

Oracle - How to write a query with multiple outer joins and joins to dates in ansi sql

I am trying to write the following query in ansi (sql 92) sql with outer joins: select * from ota_delegate_bookings odb, ota_events oe, -- aka Class ...

Is N'some string here' ANSI SQL?

We know that in order to qualify a string literal as Unicode, we need to prefix it with N in Transact-SQL. My question is if this is part any standard such as ANSI SQL. Will other database...

SWITCH function doesn't work in access sql

With the code below, I'm trying to update table K_tables. The column ColumnType doesn't have zero or null values in it, but after I run this script I get a lot of null valued rows, and the number...

Access: How to alter column to counter with existing rows in table?

I know that it can't be easily solved by this statement: ALTER TABLE BLOCK ALTER COLUMN block_id COUNTER(5000,1) PRIMARY KEY; -- I get here error saying about wrong column type Because of 2...

How to pass cyrillic symbols in CMIS query?

I'm using CMIS query language to query information from my Alfresco server. Some of content's properties have Cyrillic names. When I try to insert this names in WHERE clause, I get an...

IN and ANY not working as expected in H2 (with examples)

EDIT: This appears to be a bug in H2 version 1.4.190. I have updated my post below. The problem does not happen in version 1.3.176 or 1.4.191. (Suggestions for optimizing the queries also...

Time difference returning integer ansi sql 92

I am trying to calculate the number of minutes between two datetimes using sql ansi-92. I'm not able to use timestampdiff() function because I get an error response. I tried date1-date2, but I...

Not getting ORA-25156 "old style outer join (+) cannot be used with ANSI join" when I should be

When converting some old-style joins to the SQL-92 style a (+) was accidentally left in the query. No error was raised when I expected the error ORA-25156: old style outer join (+) cannot be used...

Escaping colon in JMS Selector - Apache ActiveMQ Apollo

I am using Apache Apollo -1.1 as my JMS broker. Within that, I am trying to use a selector on a JMS Header, which contains colon in the value. The JMS header is as...

Update Table 1 memo field with values from table 2 in a one-to-many relationship

I have 2 tables I would like to update one column in table 1 with values from table 2 where id=id. However table 2 has many rows matching table 1 and all rows of table 2 would need to be updated...

Copying an entire, large OpenEdge table

I need to find the fastest way of reading a large OpenEdge table (100 million rows plus), preferably programmatically (in c#) and outside of ETL tools such as SSIS or staging formats such as text...

Comboxes suddenly don't autocomplete in Access 2013

-------------UPDATE------------- By coincidence I actually found an old post, posted a year ago, by myself with sort of the same problem (totally forgot about it). I suddenly remembered that ...

convert sql BETWEEN clause from ANSI 89 into ANSI 92

I am trying to convert an sql statement from ANSI 89 into ANSI 92 (meaning to transofrm the "(+)" into "OUTHER JOIN") Here is the code: select a.*, p.price from article a, prices p where...

Replace weird or special character in a string

I'm trying to convert a Excel file into a SQL query. My problem is that there are special characters in the file I was given. I cant replace them CTRL+H because they dont show at all in the Excel...

Use join column only once in SELECT *

Suppose: WITH t12 AS (SELECT * FROM t1 INNER JOIN t2 ON (t1.id = t2.id)) SELECT * FROM t12 This will fail with: The column 'id' was specified multiple times for 't12'. Now this is a large...

Grant All Defined Permisions for Database to User

This is the verbatim question: Grant all defined permissions for database cis605 to cis605_usr I read the the "GRANT ALL" syntax is deprecated and only grants ANSI-92 permissions applicable to the...

"#" LIKE pattern character does not work in SQL in Access 365? A new bug in Jet SQL engine?

From wildcard characters supported by Microsoft Access I cannot get the # working in SQL statement. A minimalized example: SELECT COUNT(*) FROM MSysObjects WHERE "123" LIKE "#*" return value for...

Microsoft Access Database Engine 2016 - issues with certain SQL queries that work fine with 2010

I have an app that's been working fine in 32 bit using the connection string: Driver={Microsoft Access Driver (*.mdb)}; Dbq=MyDatabase.mdb; using ADO in C++ on a PC with no Office...