How to convert VARCHAR to TIMESTAMP in MySQL?

Need to convert MySQL query to Oracle

• Table #1 contains Things: an id (integer) and name (string) Table #2 contains Locks: an id of a Thing (Locks.id), a lock group (Locks.group) and a specific lock (Locks.lock). Each Thing may have 0 or more Locks associated with it. A user has a set of keys each consisting of a key group and a specific key. Given a user (and thus a set of keys) I want to build a SQL query which gives me all Things which the user has access to. A user has access to a Thing when for each group of locks in a lock group the user has at least one key. Example combinations of all Locks on a specific Thing and Keys available to a user are shown below (locks before the "/", keys after). Id's have been omitted here. In the database id's are only present to link Locks to Things. A lock like "foo:bar" means a lock with lock group "foo" and specific lock "bar". The same syntax is used for keys. user:1 user:2 brand:x / user:1 == no access, because there is no key to unlock the "brand" group. user:1 user:2 brand:x / user:1 brand:x == access, because there is a matching key for each lock group. user:1 user:2 brand:x / user:1 brand:x color:red == access, because there is a key for each lock group (and the extra color-key is just that: extra). user:1 user:2 brand:x / user:3 brand:x == access, because there is no matching key for the "user" lock group. The MySQL query given the 6 keys (user:john user:pete brand:ford brand:mazda color:red color:green) to do this looks like this: select distinct Things.* from Things left join locks as L1 on (Things.id = L1.id and l1.group = 'user') left join locks as L2 on (Things.id = L2.id and l2.group = 'brand') left join locks as L3 on (Things.id = L3.id and l3.group = 'color') where ifnull(L1.lock in ('john', 'pete'),1) and ifnull(L2.lock in ('ford', 'mazda'),1) and ifnull(L3.lock in ('red', 'green'),1); Locks for Things "unlocked" with this key set are for example: user:john brand:ford user:pete color:red brand:mazda But the following lock sets are NOT unlocked with said set of 6 keys: user:john brand:kia color:red (because we don't have a matching "brand" key) user:john type:specialty (bacause we don't have a "type" key) Note that when a Thing has no locks, the user always has access to that object (this follows directly from the rules given above). More info as well as sample inputs are available via e-mail.

• Answer:

  Hi, raymond99-ga: You posted a "MySQL query" as follows: SELECT DISTINCT Things.* FROM Things left join Locks as L1 on (Things.id = L1.id AND L1.group = 'user') left join Locks as L2 on (Things.id = L2.id AND L2.group = 'brand') left join Locks as L3 on (Things.id = L3.id AND L3.group = 'color') WHERE IfNull(L1.lock in ('john','pete'),1) AND IfNull(L2.lock in ('ford','mazda'),1) AND IfNull(L3.lock in ('red','green'),1); The first part of this SELECT statement will actually work fine in Oracle, but we run into trouble with the phrasing of the WHERE condition. Most obvious is the IfNull function, which doesn't exist in Oracle. What does the IfNull function do in MySQL? Well, it tests whether the first argument is Null or not. If the first argument is Null, then the function returns the value of the second argument. However if the first argument is not Null, then that first argument is the value returned by this function. See here: * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * [MySQL IfNull] http://www.mysql.com/doc/en/Control_flow_functions.html IFNULL(expr1,expr2) "If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2." [MySQL IN Operator] http://www.mysql.com/doc/en/Comparison_Operators.html (about halfway down the page) expr IN (value,...) "Returns 1 if expr is any of the values in the IN list, else returns 0. If all values are constants, then all values are evaluated according to the type of expr and sorted. The search for the item is then done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. If expr is a case-sensitive string expression, the string comparison is performed in case-sensitive fashion: mysql> SELECT 2 IN (0,3,5,'wefwf'); -> 0 mysql> SELECT 'wefwf' IN (0,3,5,'wefwf'); -> 1 "The number of values in the IN list is only limited by the max_allowed_packet value. "From 4.1 (to comply with the SQL-99 standard), IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL." [MySQL Working with NULL] http://www.mysql.com/doc/en/Working_with_NULL.html "Note that in MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1." * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Exactly the same functionality as MySQL's IfNull is provided in Oracle by the NVL function. Again, NVL takes two arguments and depending on whether the first of these is Null or not, the function returns the second argument or the first argument, respectively: * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * [Oracle 9i NVL] http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions88a.htm#91654 NVL ( expr1 , expr2 ) "If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1." * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * But it's not quite enough if we just replace the IfNull function calls by NVL function calls, because the original query is taking advantage of the MySQL treatment of expressions as being Boolean "true" if they are nonzero. Oracle SQL per se has no Boolean datatype (although there is one in PL/SQL), and the Boolean value of "control" expressions is always an implicit matter. In particular there is no implied equivalence of numeric 1 with Boolean "true" in Oracle SQL. We need to replace the IfNull functions that work so well in MySQL with a compound logical expression in Oracle, one which tests the alternatives of some "missing" Locks.lock value being Null (because of the outer join condition) versus an actual value that does appear in the lists of literal strings. Therefore the minimum amount of conversion that would need to be done to your query is this: SELECT DISTINCT Things.* FROM Things left join Locks as L1 on (Things.id = L1.id AND L1.group = 'user') left join Locks as L2 on (Things.id = L2.id AND L2.group = 'brand') left join Locks as L3 on (Things.id = L3.id AND L3.group = 'color') WHERE ( L1.lock Is Null OR L1.lock in ('john','pete') ) AND ( L2.lock Is Null OR L2.lock in ('ford','mazda') ) AND ( L3.lock Is Null OR L3.lock in ('red','green') ); Below are more supporting Web references to the Oracle syntax documentation. If you should require further clarification of this answer, please use the button provided to request it. In particular the syntax given in this simplified example "hard codes" the lock groups and specific "keys". If you like, I can make some suggestions about ways to structure the code when the "keys" held by a user are table-driven. regards, mathtalk-ga [Oracle 9i DISTINCT Clause of SELECT] http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2065826 DISTINCT | UNIQUE Specify DISTINCT or UNIQUE if you want Oracle to return only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list. [Oracle 9i PL/SQL Boolean Literals] http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/02_funds.htm#2924 Boolean Literals Boolean literals are the predefined values TRUE, FALSE, and NULL (which stands for a missing, unknown, or inapplicable value). Remember, Boolean literals are values, not strings. For example, TRUE is no less a value than the number 25. [Oracle 9i IS NULL] http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems20.htm#34133 "IS NULL" "This comparison operator returns the Boolean value TRUE if its operand is null, or FALSE if its operand is not null." [Oracle 9i IN] http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/13_elems20.htm#34124 "IN" "This comparison operator tests set membership. It means "equal to any member of." The set can contain nulls, but they are ignored. Also, expressions of the form value NOT IN set yield FALSE if the set contains a null." [Oracle 9i NVL] http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions88a.htm#91654 NVL ( expr1 , expr2 ) Purpose "NVL lets you replace a null (blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle converts expr2 to the datatype of expr1 before comparing them. "The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data, in which case the return value's datatype is VARCHAR2 and is in the character set of expr1." [Oracle 9i Left Outer Join Example] http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2107297 "The following example uses a left outer join to return the names of all departments in the sample schema hr, even if no employees have been assigned to the departments: SELECT d.department_id, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id ORDER BY d.department_id; [Oracle 9i Nulls in Conditions] http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements5a.htm#4030 [Oracle 9i NVL2] http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions89a.htm#84586 NVL2 ( expr1 , expr2 , expr3 ) Purpose "NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG. "If the datatypes of expr2 and expr3 are different, then Oracle converts expr3 to the datatype of expr2 before comparing them unless expr3 is a null constant. In that case, a datatype conversion is not necessary. "The datatype of the return value is always the same as the datatype of expr2, unless expr2 is character data, in which case the return value's datatype is VARCHAR2." [Oracle 9i NULLIF] http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions85a.htm#SQLRF00681 "NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1. "The NULLIF function is logically equivalent to the following CASE expression: CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END"