MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for June, 2011

MySQL Empty Set Answer

with one comment

Somebody was complaining that you couldn’t just get a Yes/No answer from a query. Yes when rows are found and No when rows aren’t found, like an “In-stock” or “Out-of-stock” message combo from a query. He didn’t like having to handle an Empty set by writing logic in PHP to provide that “Out-of-stock” message.

I told him he was wrong, you can get a a Yes/No answer from a query. You just write it differently, instead of a query like this, which get the “In-stock” message but forces you to handle the “Out-of-stock” message in the PHP code base on no records found in the query.

SELECT  'In-stock'
FROM    item
WHERE   item_title = 'Star Wars II'
LIMIT 1;

It’s simpler to write it like the one below. You gets a Yes/No answer from a query whether a row matches the query condition or not:

SELECT IF('Star Wars VII' IN (SELECT item_title FROM item)
         ,'In-stock','Out-of-stock') AS yes_no_answer;

You can also write it this more generic way, which works in Oracle and MySQL:

SELECT CASE
         WHEN 'Star Wars VII' IN (SELECT item_title FROM item)
         THEN 'In-stock'
         ELSE 'Out-of-stock'
       END AS yes_no_answer
FROM   dual;

There’s no Star Wars VII yet, but this returns the desired result when it’s not found in the data set. It also works when you find Star Wars II in the data set. Never, say never … 🙂

A more useful and complete approach with this technique is shown below with data fabrication.

SELECT inline.query_string
,      CASE
         WHEN inline.query_string IN (SELECT item_title FROM item)
         THEN 'In-stock'
         ELSE 'Out-of-stock'
       END AS yes_no_answer
FROM  (SELECT 'Star Wars II' AS query_string FROM dual
       UNION ALL
       SELECT 'Star Wars VII' AS query_string FROM dual) inline;

The query runs in an Oracle or MySQL database and returns the following result set:

+---------------+---------------+
| query_string  | yes_no_answer |
+---------------+---------------+
| Star Wars II  | In-stock      |
| Star Wars VII | Out-of-stock  |
+---------------+---------------+

Hope this helps somebody else too.

Written by maclochlainn

June 23rd, 2011 at 1:25 am

Posted in MySQL,sql

SQL 1st Day of Month

with 7 comments

A couple years ago I showed how to get the last day of the month and count past leap year. Somebody asked how to get the first day of the current month.

The two easiest ways I know how to do that in Oracle is to combine the ADD_MONTHS and LAST_DAY functions, like:

SELECT
  TO_CHAR(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1),'DD-MON-YY HH24:MI') AS "One Way"
, TO_CHAR(TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE)+1,-1)),'DD-MON-YY HH24:MI') AS "Another Way"
FROM  dual

It gives you a truncated datetime value, as you can see:

One Way         Another Way
--------------- ---------------
01-JUN-11 00:00 01-JUN-11 00:00

As the comments point out, the following is best for getting the first day of the month:

SELECT TO_CHAR(TRUNC(SYSDATE,'MM'),'DD-MON-YYYY HH24:MI') FROM dual;

You can also return the first day of the year like this:

SELECT TO_CHAR(TRUNC(SYSDATE,'YY'),'DD-MON-YYYY HH24:MI') FROM dual;

If you’re planning to do this in an application and Oracle Database 11g, you may want to write it as a deterministic function, like this:

1
2
3
4
5
6

CREATE OR REPLACE FUNCTION first_day
( date_in DATE ) RETURN DATE DETERMINISTIC IS
BEGIN
  RETURN TRUNC(date_in,'MM');
END;
/

Naturally, you can also do this with the EXTRACT function but it would get very complex quickly. Hope this answers the question.

Written by maclochlainn

June 7th, 2011 at 11:45 pm

Posted in Oracle,sql

Delete removes rows, eh?

with 5 comments

It started as a smart remark, “The DELETE FROM statement always removes one or more rows from a table when any are found, except when the table isn’t a table.” That exception is true when the table isn’t a schema-level table but a nested table in an Oracle database. Then, the DELETE FROM statement may remove a row from the table or a row from collection held by a row.

Naturally, everybody would like an example. It took more than a couple steps to work one up and the Oracle documentation isn’t very helpful on the topic.

  1. Create a SQL nested table collection type of 30-character variable length strings:
1
2

CREATE TYPE street_list IS TABLE OF VARCHAR2(30);
/
  1. Create a SQL user-defined object type for addresses:
1
2
3
4
5
6
7

CREATE OR REPLACE TYPE address_type AS OBJECT
( address_id     NUMBER
, street_address STREET_LIST
, city           VARCHAR2(30)
, state          VARCHAR2(2)
, postal_code    VARCHAR2(10));
/
  1. Create a SQL nested table of the user-defined object type for addresses:
1
2

CREATE OR REPLACE TYPE address_list AS TABLE OF address_type;
/
  1. Create a schema-level table that uses the user-defined nested table of a user-defined object type, and a SEQUENCE for the table:
1
2
3
4
5
6
7
8
9
10

CREATE TABLE employee
( employee_id    NUMBER
, first_name     VARCHAR2(20)
, middle_name    VARCHAR2(20)
, last_name      VARCHAR2(20)
, home_address   ADDRESS_LIST)
  NESTED TABLE home_address STORE AS address_table
   (NESTED TABLE street_address STORE AS street_table);
 
CREATE SEQUENCE employee_s1;
  1. Insert data into the table:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

INSERT INTO employee
( employee_id
, first_name
, last_name
, home_address )
VALUES
( employee_s1.NEXTVAL
,'Sam'
,'Yosemite'
, address_list(
    address_type( 1
                , street_list('1111 Broadway','Suite 322')
                ,'Oakland'
                ,'CA'
                ,'94612')
  , address_type( 2
                , street_list('1111 Broadway','Suite 525')
                ,'Oakland'
                ,'CA'
                ,'94612')));
 
INSERT INTO employee
( employee_id
, first_name
, last_name
, home_address )
VALUES
( employee_s1.NEXTVAL
,'Bugs'
,'Bunny'
, address_list(
    address_type( 1
                , street_list('21 Rodeo Drive','Suite 1000')
                ,'Beverly Hills'
                ,'CA'
                ,'90210')
  , address_type( 2
                , street_list('21 Rodeo Drive','Suite 2010')
                ,'Beverly Hills'
                ,'CA'
                ,'90210')));
  1. Create a SQL view to explode nesting into a meaningful data set:
1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE VIEW explode_employee AS
SELECT   e.employee_id
,        e.first_name || ' ' || e.last_name AS full_name
,        st.address_id
,        sa.column_value AS st_address
,        st.city
,        st.state
,        st.postal_code
FROM     employee e CROSS JOIN
         TABLE(e.home_address) st CROSS JOIN
         TABLE(street_address) sa
ORDER BY 1, 2, 3;
  1. Format output and query view:
1
2
3
4
5
6
7
8
9
10
11

-- SQL*Plus formattting
COLUMN employee_id FORMAT 999 HEADING "ID|EMP"
COLUMN full_name   FORMAT A16 HEADING "Full Name"
COLUMN address_id  FORMAT 999 HEADING "ID|UDT"
COLUMN st_address  FORMAT A16 HEADING "Street Address"
COLUMN city        FORMAT A14 HEADING "City"
COLUMN state       FORMAT A5  HEADING "State"
COLUMN postal_code FORMAT A5  HEADING "Zip|Code"
 
-- Query all from view.
SELECT * FROM explode_employee;

Displays the following (2 base rows times two nested table rows times two scalar nested table rows or 8 rows):

  ID                    ID                                       Zip
 EMP Full Name         UDT Street Address   City           State Code
---- ---------------- ---- ---------------- -------------- ----- -----
   1 Sam Yosemite        1 1111 Broadway    Oakland        CA    94612
   1 Sam Yosemite        1 Suite 322        Oakland        CA    94612
   1 Sam Yosemite        2 1111 Broadway    Oakland        CA    94612
   1 Sam Yosemite        2 Suite 525        Oakland        CA    94612
   2 Bugs Bunny          1 21 Rodeo Drive   Beverly Hills  CA    90210
   2 Bugs Bunny          1 Suite 1000       Beverly Hills  CA    90210
   2 Bugs Bunny          2 21 Rodeo Drive   Beverly Hills  CA    90210
   2 Bugs Bunny          2 Suite 2010       Beverly Hills  CA    90210
  1. UPDATE statement against a nested table:
1
2
3
4
5

UPDATE TABLE (SELECT e.home_address
              FROM   employee e
              WHERE  e.employee_id = 1) e
SET   e.city = 'Fremont'
WHERE e.address_id = 1;
  1. UPDATE statement against a nested table and scalar nested table within it:
1
2
3
4
5
6

UPDATE TABLE (SELECT e.home_address
              FROM   employee e
              WHERE  e.employee_id = 1) e
SET   e.street_address = street_list('1111 Broadway','Suite 522')
,     e.city = 'Oakland'
WHERE e.address_id = 1;
  1. DELETE statement against a nested table, which only removes the nested row:
1
2
3
4

DELETE FROM TABLE (SELECT e.home_address
                   FROM   employee e
                   WHERE  e.employee_id = 1) ha
WHERE  ha.address_id = 1;

As always, hope this helps somebody looking for syntax or approach.

Written by maclochlainn

June 5th, 2011 at 11:49 pm

Posted in Oracle,sql