Query to delete duplicate rows…

First: I didn’t come up with this technique. I found it on the AskTom (http://asktom.oracle.com/) web site. That being said, I use it all the time and refer others to the technique often. Link to where I got it from: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:15258974323143

Let us say you have a table that has duplicates across a couple (or all) columns, and you need to get rid of the dups.  Assuming table “T” with columns “COL1, COL2″… this is the script that would kill the dups.

DELETE FROM T
WHERE ROWID IN 
(SELECT RID FROM 
  (SELECT ROWID RID, 
          ROW_NUMBER() OVER (PARTITION BY COL1, COL2 ORDER BY ROWID) RN 
   FROM T) 
 WHERE RN<>1
);

If you have more columns, just add them into the partition area of the query.

Advertisements

DECODE, CASE… Does it Matter?

If I had a nickel for every time I get asked that question… well, I wouldn’t have much money; but judging by the applications I have worked on, quite a few programmers are 50/50 on the topic. Their code reflects not knowing which to use.

When I discovered CASE many moons ago, I never looked back. It was infinitely more readable than DECODE and handled complex evaluations with simpler code. All things being close to equal, readability/simple is important to me. While I am working on something, I can remember day to day what I was thinking. When an app is in maintenance mode, deciphering nested DECODEs is a pain.

Why is DECODE so rampant in queries?
There are a couple contributing factors that play to the answer here. First… if you want a simple if/then/else, DECODE is less typing. Secondly… Before Oracle 8.1, DECODE was the only game in town for this type of logic in the query… outside of writing your own function. If you have been around for a while, you probably started with DECODE and it comes as naturally as using CFQUERY.

Before we get to the differences between DECODE and CASE, for those not familiar with one or the other, let’s see an equivalent example of both.

/*	WHAT IS IN THE TABLE?	*/

SQL> SELECT * FROM EMP;

    EMP_ID EMP_NAME   SUPERVISOR GENDER
---------- ---------- ---------- -
        10 Larry      Chris      M
        15 Tim        Chris      M
        20 Monika     Eric       F
        25 Jon Connor Angela     M

SQL> SELECT     DECODE(
  2                     SUPERVISOR,'Chris','Works for Chris',
  3                     'Does not work for Chris'
  4             ) AS DECODE_RESULT,
  5             (       CASE    WHEN SUPERVISOR = 'Chris' THEN 'Works for Chris'
  6                             ELSE 'Does not work for Chris'
  7                     END
  8             ) AS CASE_RESULT
  9  from       EMP;

DECODE_RESULT           CASE_RESULT
----------------------- -----------------------
Works for Chris         Works for Chris
Works for Chris         Works for Chris
Does not work for Chris Does not work for Chris
Does not work for Chris Does not work for Chris

Both are easy to read and both return the same results.

Let’s try nesting a bit and see how it looks.

SQL> SELECT     DECODE(
  2                     SUPERVISOR,'Chris','Works for Chris',
  3                     DECODE(
  4                             SUPERVISOR,'Eric','Works for Eric',
  5                             'No idea'
  6                     )
  7             ) as DECODE_RESULT1,
  8             DECODE(
  9                     SUPERVISOR,'Chris','Works for Chris','Eric','Works for Eric','No Idea'
 10             ) as DECODE_RESULT2,
 11             (       CASE    WHEN SUPERVISOR = 'Chris' THEN 'Works for Chris'
 12                             WHEN SUPERVISOR = 'Eric' THEN 'Works for Eric'
 13                             ELSE 'No idea'
 14                     END
 15             ) AS CASE_RESULT
 16  FROM       EMP;

DECODE_RESULT1  DECODE_RESULT2  CASE_RESULT
--------------- --------------- ---------------
Works for Chris Works for Chris Works for Chris
Works for Chris Works for Chris Works for Chris
Works for Eric  Works for Eric  Works for Eric
No idea         No Idea         No idea

In the above queries, there are are two different styles for the DECODE. DECODE_RESULT2 is probably the easier of the two to read. I still think the CASE is the more readable.

If you aren’t careful, you can end up with DECODE(…,DECODE(…,(DECODE(…,)))). And if any of those DECODES have multiple tests, then good luck debugging. (Good code formatting will help, but still can get hairy when trying to debug).

So what are some of the differences?

1) How they handle nulls is an important distinction.

SQL> SELECT     DECODE(
  2                     NULL,NULL,'I AM NULL',
  3                     'I AM NOT NULL'
  4             ) AS DECODE_RESULT
  5  FROM       DUAL;

DECODE_RE
---------
I AM NULL

SQL> SELECT     (       CASE    NULL
  2                             WHEN NULL THEN 'I AM NULL'
  3                             ELSE 'I AM NOT NULL'
  4                     END
  5             ) AS CASE_RESULT1
  6  FROM       DUAL;

CASE_RESULT1
-------------
I AM NOT NULL

SQL> SELECT     (       CASE    WHEN NULL IS NULL THEN 'I AM NULL'
  2                             ELSE 'I AM NOT NULL'
  3                     END
  4             ) AS CASE_RESULT1
  5  FROM       DUAL;

CASE_RESULT1
-------------
I AM NULL

If you aren’t careful, the CASE can throw you off when testing for nulls.

2) CASE can work with logical operators other than equals. (You can also use ranges in CASE statements)

SQL> SELECT     DECODE(EMP_ID,<100,'Early Hire','New Hire') as DECODE_RESULT
  2  FROM       EMP;

SELECT     DECODE(EMP_ID,<100,'Early Hire','New Hire') as DECODE_RESULT
                         *
ERROR at line 1:
ORA-00936: missing expression

SQL> SELECT     (       CASE    WHEN EMP_ID<100 THEN 'Early Hire'
  2                             ELSE 'New Hire'
  3                     END
  4             ) AS CASE_RESULT
  5  FROM       EMP;

CASE_RESUL
----------
Early Hire
Early Hire
Early Hire
Early Hire

3) CASE can use subqueries.

SQL> SELECT     (       CASE    WHEN SUPERVISOR = 'Chris' THEN 'Good supervisor'
  2                             WHEN EMP_ID NOT IN 
                                    (    SELECT EMP_ID 
                                         FROM   EMP 
                                         WHERE  SUPERVISOR = 'Chris'
                                    ) THEN 'Questionable leadership'
  3                             ELSE    NULL
  4                     END
  5             ) AS CASE_RESULT
  6  FROM       EMP;

CASE_RESULT
-----------------------
Good supervisor
Good supervisor
Questionable leadership
Questionable leadership

4) CASE can be used as a PL/SQL construct. I won’t demonstrate that here, but it can be.

5) CASE can be used as a parameter to a procedure.

SQL> CREATE PROCEDURE STOOPID_PROC
  2  (  P_VAL IN VARCHAR2       )
  3  AS
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE(P_VAL);
  6  END STOOPID_PROC;
  7  /

Procedure created.

SQL> EXEC STOOPID_PROC(DECODE(NULL,'NULL','NOT NULL'));

BEGIN STOOPID_PROC(DECODE(NULL,'NULL','NOT NULL')); END;

                   *
ERROR at line 1:
ORA-06550: line 1, column 20:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL
statement only
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> EXEC STOOPID_PROC((CASE WHEN NULL IS NULL THEN 'NULL' ELSE 'NOT NULL' END));
NULL

PL/SQL procedure successfully completed.

6) CASE is ANSI. DECODE is not.

7) Speed. There is a difference, but I never seem to find a good answer on which way this one goes. Mostly, it appears CASE is faster, but I have never done a full on test to see. At least not yet.

*Note: You don’t need all the parenthesis around the CASE statements. I like them.

Coalesce as an alternative to NVL

While this seems database specific, I think it hits on a query technique I see all the time in ColdFusion code.

How many times have you written a NVL statement in your (Oracle) query like this:
NVL(NVL(NVL(COL1,COL2),COL3)COL4)

Basically in English this says… If COL1 is null then Col2… If COL2 is null then COL3… If COL3 is null then COL4.

It isn’t very readable and depending on the number of columns you are working with, the nesting can get “challenging” to debug/edit. Also… NVL is Oracle specific where COALESCE is ANSI. This post however, will be looking at how Oracle handles things.

There are some things you should be aware of when using COALESCE, but first let’s see it in action.

SQL>  CREATE TABLE CHRIS_IS_NEAT
2     (  COL1    VARCHAR2(10),
3        COL2    VARCHAR2(10),
4        COL3    VARCHAR2(10),
5        COL4    VARCHAR2(10)
6  );
Table created.

SQL> INSERT INTO CHRIS_IS_NEAT VALUES (NULL,NULL,'BLUE','YELLOW');
1 row created.
SQL> INSERT INTO CHRIS_IS_NEAT VALUES ('RED','BLUE','BROWN','YELLOW');
1 row created.
SQL> INSERT INTO CHRIS_IS_NEAT VALUES (NULL,NULL,NULL,'BLACK');
1 row created.
SQL> COMMIT;
Commit complete.

SQL>
SQL> SELECT     NVL(NVL(NVL(COL1,COL2),COL3),COL4),
2               COALESCE(COL1,COL2,COL3,COL4)
3    FROM       CHRIS_IS_NEAT;

NVL(NVL(NV COALESCE(C
---------- ----------
BLUE       BLUE
RED        RED
BLACK      BLACK

SQL> DROP TABLE CHRIS_IS_NEAT;
Table dropped.
SQL> PURGE RECYCLEBIN;
Recyclebin purged.

The COALESCE function returns the first non-NULL value in the list. I don’t know about you, but that is a lot easier to follow than the NVL syntax.

Important things to remember about COALESCE

  • Datatypes… Try to make them the same. If you do that, skip the rest of the mumbo-jumbo on this bullet. NVL will do an implicit conversion of the datatype to the first parameter if it can. COALESCE… If you don’t have consistent datatypes, you will get a “datatype error”. If the columns are different types, make sure you convert them on the way into the function. Caveat… Different numeric types will implicitly be converted to the argument with the highest numeric precedence. Also, if you have a non-numeric datatype than can be implicitly converted to a number, Oracle will try.
  • NVL evaluates both columns represented. COALESCE stops evaluating as soon as it finds a non-NULL value. Oracle calls this a “short-circuit evaluation*” in the docs. This can provide a speed bump as well over NVL. Most of the time it will probably be too small to notice. (There is great disagreement on this point (speed bump), but limited testing shows COALESCE “can” be faster depending on how it is being used.)
  • If all columns passed in are null, COALESCE returns null.
  • My example above uses column names, but they could also be functions or constant values.

*Other examples of short-circuit functionality are DECODE and CASE.

Aggregating your data in the query

How the data is stored rarely matches how we want it displayed. How often you need to aggregate data will normally hinge on the degree of normalization your data architect has chosen.

As with quite a few of the items I have written about or plan to write about, there are multiple ways to accomplish what is being described. Chances are that if you are a ColdFusion developer, you already perform this type of aggregation in the middle tier. That way is no less valid, especially if you are dealing with a shared query and messing with it (technical term there) might cause other developers or applications problems. If given the opportunity though, I always try to let the database do what the database does well. Deal with the data.

Oracle 11g introduced some new functionality that easily allows you to aggregate data directly in your query. The function is called listagg(). Prior to Oracle 11g, to accomplish the same goal you needed to create custom datatypes and write your own functions. While the solution was genius, it was often more complex than people wanted to take on. If you are on an older version of Oracle, I recommend checking out the AskTom (http://asktom.oracle.com/) site and search for “stragg”. I could write a post about the function, but I don’t think I could add more than Tom’s site provides.

I am going to demonstrate the listagg() function in SQLPlus; obviously the query demonstrated can go in your cfquery tag.

/*	CREATE SOME TEMP TABLES	*/
SQL> CREATE TABLE CCH_PERSON
  2  (  PERSON_PK               NUMBER,
  3     PERSON_NAME             VARCHAR2(30),
  4     IS_ACTIVE               NUMBER(1) DEFAULT 1 NOT NULL,
  5     PROJECT_FK              NUMBER,
  6     CONSTRAINT PK_CCH_PERSON PRIMARY KEY(PERSON_PK)
  7  );

Table created.

SQL>
SQL> CREATE TABLE CCH_PROJECTS
  2  (  PROJECT_PK      NUMBER,
  3     PROJECT_NAME    VARCHAR2(30),
  4     CONSTRAINT PK_CCH_PROJECTS PRIMARY KEY(PROJECT_PK)
  5  );

Table created.

SQL>
SQL> CREATE TABLE CCH_PERSON_PROJECTS_J
  2  (  PERSON_FK       NUMBER  NOT NULL,
  3     PROJECT_FK      NUMBER  NOT NULL
  4  );

Table created.

SQL> set feedback off;
SQL>
SQL> /* CREATE SOME PEOPLE      */
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('10','Chris');
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('11','Anthony');
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('12','Tim');
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('13','John');
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('14','Fred');
SQL> INSERT INTO CCH_PERSON (PERSON_PK, PERSON_NAME     ) VALUES ('15','Sam');
SQL> commit;
SQL>
SQL> /* CREATE SOME PROJECTS    */
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('1','Big Blog');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('2','Project Tracking');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('3','Property DB');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('4','Employee Relations');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('5','Billing System');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('6','World Domination');
SQL> INSERT INTO CCH_PROJECTS(PROJECT_PK, PROJECT_NAME) VALUES ('7','Pet Project');
SQL> commit;
SQL>
SQL> /* MAKE PEOPLE WORK ON PROJECTS    */
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('10','1');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('10','2');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('10','3');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('11','4');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('11','5');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('12','6');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('13','7');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('15','5');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('15','1');
SQL> INSERT INTO CCH_PERSON_PROJECTS_J (PERSON_FK, PROJECT_FK) VALUES ('15','6');
SQL> COMMIT;
SQL> set feedback on;

--	RUN A QUERY TO FIND OUT WHAT PEOPLE ARE WORKING ON
SQL> SELECT     A.PERSON_NAME,
  2             C.PROJECT_NAME
  3  FROM       CCH_PERSON A,
  4             CCH_PERSON_PROJECTS_J B,
  5             CCH_PROJECTS C
  6  WHERE      A.PERSON_PK     = B.PERSON_FK(+)
  7     AND     B.PROJECT_FK    = C.PROJECT_PK(+)
  8  ORDER BY A.PERSON_NAME;

PERSON_NAM PROJECT_NAME
---------- ------------------------------
Anthony    Employee Relations
Anthony    Billing System
Chris      Big Blog
Chris      Project Tracking
Chris      Property DB
Fred
John       Pet Project
Sam        World Domination
Sam        Big Blog
Sam        Billing System
Tim        World Domination

11 rows selected.

SQL> -- ADD SOME COLUMN FORMATTING SO THE DISPLAY IS NICE
SQL> column PERSON_NAME format a10
SQL> column PROJECTS format a46
SQL>
SQL> -- AGGREGATE THE DATA
SQL> SELECT     A.PERSON_NAME,
  2             LISTAGG(C.PROJECT_NAME,', ') WITHIN GROUP (ORDER BY A.PERSON_NAME) AS PROJECTS
  3  FROM       CCH_PERSON A,
  4             CCH_PERSON_PROJECTS_J B,
  5             CCH_PROJECTS C
  6  WHERE      A.PERSON_PK = B.PERSON_FK(+)
  7     AND     B.PROJECT_FK = C.PROJECT_PK(+)
  8  GROUP BY A.PERSON_NAME
  9  ORDER BY A.PERSON_NAME;

PERSON_NAM PROJECTS
---------- ----------------------------------------------
Anthony    Billing System, Employee Relations
Chris      Big Blog, Project Tracking, Property DB
Fred
John       Pet Project
Sam        Big Blog, Billing System, World Domination
Tim        World Domination

6 rows selected.

SQL> -- CLEAN UP AFTER YOURSELF
SQL> DROP TABLE CCH_PERSON;
Table dropped.

SQL> DROP TABLE CCH_PERSON_PROJECTS_J;
Table dropped.

SQL> DROP TABLE CCH_PROJECTS;
Table dropped.

SQL> PURGE RECYCLEBIN;
Recyclebin purged.

Ok… So what is happening here? You are running a query to collect two columns. Person and Project. Person is the value that you group on and Project is the data you want to aggregate. ListAgg() has two input parameters; column to aggregate, and delimeter. Then the ‘WITHIN GROUP (…’ tells Oracle which group to aggregate over.

The above example is using Listagg() as a group-set aggregate. It has some other options as well such as an analytical function, but that would be a considerably longer post. Maybe down the road.

Listagg() is a easy but powerful function to implement… As soon as you use it once, you will want to implement it everywhere.

Insert or update? Upsert!

For years, when saving data to the database I have always run the logic “Does the record exist? If so, update it. If not, insert a new record.”

Difficult? No. Do I get tired of writing it? Yeah.

While there are many ways to deal with this, below is the method I like to use.

I have the same example below in both SQL Plus and ColdFusion.

SQL Plus Example

CREATE TABLE UPSERT_TAB
(	UPSERT_TAB_PK	NUMBER(1),
	UPSERT_VALUE 	VARCHAR2(30)
);

GRANT SELECT ON UPSERT_TAB TO SNWACF;

INSERT INTO UPSERT_TAB VALUES (1,'Dog');
INSERT INTO UPSERT_TAB VALUES (2,'Cat');
COMMIT;

/*	TRY TO UPDATE A RECORD THAT DOES NOT EXIST	*/
begin
	UPDATE	UPSERT_TAB
	SET 	UPSERT_TAB_PK = 3,
	        UPSERT_VALUE = 'Big Foot'
	WHERE 	UPSERT_TAB_PK = 3;
	--
	IF ( sql%rowcount = 0 )
	    THEN
	    INSERT INTO UPSERT_TAB
	        VALUES (3, 'Big Foot');
	END IF;
end;
/

SQL> begin
  2     UPDATE  UPSERT_TAB
  3     SET     UPSERT_TAB_PK = 3,
  4             UPSERT_VALUE = 'Big Foot'
  5     WHERE   UPSERT_TAB_PK = 3;
  6     --
  7     IF ( sql%rowcount = 0 )
  8         THEN
  9         INSERT INTO UPSERT_TAB
 10             VALUES (3, 'Big Foot');
 11     END IF;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM UPSERT_TAB;

UPSERT_TAB_PK UPSERT_VALUE
------------- ------------------------------
            1 Dog
            2 Cat
            3 Big Foot

/*	Now try an update	*/

SQL> begin
  2     UPDATE  UPSERT_TAB
  3     SET     UPSERT_TAB_PK = 2,
  4             UPSERT_VALUE = 'Cats are weird'
  5     WHERE   UPSERT_TAB_PK = 2;
  6     --
  7     IF ( sql%rowcount = 0 )
  8         THEN
  9         INSERT INTO UPSERT_TAB
 10             VALUES (2, 'Cats are weird');
 11     END IF;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM UPSERT_TAB;

UPSERT_TAB_PK UPSERT_VALUE
------------- ------------------------------
            1 Dog
            2 Cats are weird
            3 Big Foot

/*	rollback the data and try it in CF	*/
DELETE FROM UPSERT_TAB WHERE UPSERT_TAB_PK = 3;
UPDATE UPSERT_TAB SET UPSERT_VALUE = 'Cat' WHERE UPSERT_TAB_PK = 2;
COMMIT;

Now try the same thing in ColdFusion

<b>Fresh table of data before we start messin' with it.</b><BR>

<cfquery name="getFreshTable" datasource="#application.strDatasource#" >
	SELECT * FROM UPSERT_TAB
</cfquery>

<cfdump var="#getFreshTable#" metainfo="false" label="getFreshTable">
<!----------------------------------------------------------------------->

<br><br>
<b>Run the first upsert and then look at the table again.</b><br>

<cfquery name="Upsert1" datasource="#application.strDatasource#">
	begin
		UPDATE  UPSERT_TAB
		SET     UPSERT_TAB_PK	= <cfqueryparam cfsqltype="cf_sql_numeric" value="3" >,
				UPSERT_VALUE 	= <Cfqueryparam cfsqltype="cf_sql_varchar" value="Big Foot">
		WHERE   UPSERT_TAB_PK 	= <cfqueryparam cfsqltype="cf_sql_numeric" value="3" >;
		--
		IF ( sql%rowcount = 0 )
		THEN
			INSERT INTO UPSERT_TAB
				VALUES (
					<cfqueryparam cfsqltype="cf_sql_numeric" value="3" >, 
					<Cfqueryparam cfsqltype="cf_sql_varchar" value="Big Foot">
				);
		END IF;
	end;
</cfquery>

<cfquery name="getUpsertResult1" datasource="#application.strDatasource#" >
	SELECT * FROM UPSERT_TAB
</cfquery>

<cfdump var="#getUpsertResult1#" metainfo="false" label="getUpsertResult1">
<!-------------------------------------------------------------------->

<br><Br>
<b>Run the second upsert and then look at the table yet again.</b>

<cfquery name="upsert2" datasource="#application.strDatasource#">
	begin
		UPDATE  UPSERT_TAB
		SET     UPSERT_TAB_PK = <cfqueryparam cfsqltype="cf_sql_numeric" value="2" >,
				UPSERT_VALUE = <Cfqueryparam cfsqltype="cf_sql_varchar" value="Cats are weird">
		WHERE   UPSERT_TAB_PK = <cfqueryparam cfsqltype="cf_sql_numeric" value="2" >;
		--
		IF ( sql%rowcount = 0 )
		THEN
			INSERT INTO UPSERT_TAB
				VALUES (
					<cfqueryparam cfsqltype="cf_sql_numeric" value="2" >, 
					<Cfqueryparam cfsqltype="cf_sql_varchar" value="Cats are weird">
				);
		END IF;
	end;
</cfquery>

<cfquery name="getUpsertResult2" datasource="#application.strDatasource#" >
	SELECT * FROM UPSERT_TAB
</cfquery>

<cfdump var="#getUpsertResult2#" metainfo="false" label="getUpsertResult2">

Screenshot:
upsert

Generate an artificial list of dates in a query

If you are running a query against a data set that is sparsely populated (with regards to the dates), but your resulting recordset needs all dates represented… One option in the past has been to create a “dummy” table containing dates that you could then join to.  These dummy tables will result in somebody years down the road saying “What h*** is this?!”  I say this often and with great enthusiasm.

Instead of using a dummy table, you could also use a query as your join for dates.  This example returns all date for one year from today.  The same logic could be applied to a sequence of numbers:

select    trunc(sysdate)+level as adate
from      dual
connect   by level <=365;

ADATE
---------
30-OCT-13
31-OCT-13
01-NOV-13
02-NOV-13
03-NOV-13
04-NOV-13
05-NOV-13
06-NOV-13
07-NOV-13
etc.

To do basically the same thing between two arbitrary dates…

SELECT    TRUNC(to_date('01-JAN-2011'))+LEVEL AS ADATE
FROM      DUAL
CONNECT   BY LEVEL <=to_date('01-JAN-2011') - to_date('01-JAN-2010');

ADATE
---------
02-JAN-11
03-JAN-11
04-JAN-11
05-JAN-11
06-JAN-11
07-JAN-11
08-JAN-11
09-JAN-11
10-JAN-11
etc.

Pivot your query

*Note: Syntax below is for Oracle.

Similar functionality can easily be implemented in ColdFusion, but I lean towards putting this type of thing in the originating query instead of massaging the data after retrieval from the database.  Massaging data can be fun, but like many things in life, not always a good idea.

Every time I hear the word “pivot”, I can’t help but think of Ross and Joey trying to carry a sofa. Get the reference? No? Ok… moving on…

In the past, when you wanted to “pivot” rows of data into columns, you could use the “max(decode(…))” method to accomplish the task (If anybody wants me to explain that, hit me up in the comments).  In Oracle 11g, the “pivot” and “unpivot” keywords have been added that will allow pivoting your data from rows to columns as well as from columns to rows.  I will demonstrate the “pivot” method.  This is a technique that should be in the library for anybody wishing to analyze data.

First, let’s set up some test data to work with.

Note: I wouldn’t design tables this way, but for the sake of illustration I will keep it simple.

CREATE TABLE WORKER
( WORKER_ID         NUMBER,
WORKER_NAME         VARCHAR2(30),
WORKER_DEPT_NO      NUMBER,
WORKER_JOB          VARCHAR2(30),
WORKER_SALARY       NUMBER(6),
WORKER_FUNCTION     VARCHAR2(30)
);

INSERT INTO WORKER VALUES (100,'Chris',20,'Managerial',100000,'MANAGE');
INSERT INTO WORKER VALUES (110,'Tim',20,'Programmer',60000,'WORK');
INSERT INTO WORKER VALUES (120,'Larry',30,'Programmer',75000,'WORK');
INSERT INTO WORKER VALUES (130,'Mike',30,'Managerial',130000,'MANAGE');
INSERT INTO WORKER VALUES (140,'Lisa',30,'Programmer',65000,'WORK');
INSERT INTO WORKER VALUES (150,'Jan',30,'Programmer',55000,'WORK');
INSERT INTO WORKER VALUES (160,'Akash',30,'Intern',25000,'WORK');
INSERT INTO WORKER VALUES (170,'Summer',20,'Intern',22000,'WORK');
INSERT INTO WORKER VALUES (200,'Ian',20,'Intern',23000,'WORK');
INSERT INTO WORKER VALUES (180,'Josh',40,'Managerial',110000,'MANAGE');
INSERT INTO WORKER VALUES (190,'Monika',40,'Programmer',95000,'WORK');
COMMIT;

Get a count of staff by department number.

SELECT    WORKER_DEPT_NO, COUNT(WORKER_DEPT_NO) AS WORKER_COUNT
FROM      WORKER
GROUP BY  WORKER_DEPT_NO;

WORKER_DEPT_NO WORKER_COUNT
-------------- ------------
 30                5
 40                2
 20                4

The query returns three rows; one for each department with a count of staff for that department. Neat. Ok… not really.

What if you wanted the department numbers as columns? Pivot is the answer.

WITH QRY_WORKER AS
 ( SELECT WORKER_DEPT_NO
   FROM   WORKER
 )
SELECT *
FROM   QRY_WORKER
PIVOT ( COUNT(WORKER_DEPT_NO) FOR (WORKER_DEPT_NO) IN (20,30,40) );

20     30     40
----------------------------
 4     5      2

So that is a little more neat. If you are unfamiliar with the “with” above, go read about it! You will be glad you did. (Maybe I will write a future post about that.) Now we have department numbers as columns and the counts represented in the data. Note that the values for the department numbers are explicitly listed.

Let’s try something similar finding min/max salaries for each of the non-managerial positions by department.

/* MAKE THE OUTPUT PRETTY IN SQLPLUS. PRETTY IS A SUBJECTIVE TERM */
SET LINESIZE 100;
WITH QRY_WORKER AS
 ( SELECT WORKER_DEPT_NO, WORKER_JOB, WORKER_SALARY
   FROM   WORKER
 )
SELECT *
FROM   QRY_WORKER
PIVOT ( MIN(WORKER_SALARY) AS MINSAL,
 MAX(WORKER_SALARY) as MAXSAL
 FOR (WORKER_JOB)
 IN ('Intern' AS INTERN,
 'Programmer' as PROGRAMMER
 )
 )
ORDER BY WORKER_DEPT_NO;

WORKER_DEPT_NO INTERN_MINSAL INTERN_MAXSAL PROGRAMMER_MINSAL PROGRAMMER_MAXSAL
-------------- ------------- ------------- ----------------- -----------------
 20            22000         23000         60000             60000
 30            25000         25000         55000             75000
 40            95000         95000

More data would make this more interesting; a small data set means there isn’t a big variation in the data represented by min/max… but you get the idea.

In the query above three rows are selected. The WORKER_JOB is the pivot, the salaries are aggregated and the departments are returned as distinct rows. Note the different values for the pivot are explicitly listed.

The inline view QRY_WORKER contains three columns, the salary is aggregated, the job is transposed into multiple columns, and the last column is used for grouping. Department, which contains three distinct values, returns one row each.

Go clean up after yourself.

SQL> DROP TABLE WORKER;
Table dropped.
SQL> PURGE RECYCLEBIN;
Recyclebin purged.

Returning records to CF from an Oracle Package

I realize this is old-hat to most… But if someone hadn’t helped me figure it out back in the day; Well… I probably would have figured it out, but somebody helped save me a lot of time.

Running queries against tables and views in ColdFusion is a piece of cake, but then sometimes you might want to call a stored procedure (or package) and let Oracle do the heavy lifting.  Below is the method for doing just that.  This example will show how to create the package in Oracle (all test code included), and then call it from ColdFusion with the package returning a record set.

Start by creating the base objects in Oracle we will test against.

SQL> CREATE TABLE MY_TAB
2  (  COL1    NUMBER,
3     COL2    VARCHAR2(20),
4     COL3    DATE
5  );

Table created.

SQL> INSERT INTO MY_TAB (COL1, COL2, COL3) VALUES ('1','ABC',SYSDATE);
1 row created.

SQL> INSERT INTO MY_TAB (COL1, COL2, COL3) VALUES ('2','DEF',SYSDATE+1);
1 row created.

SQL> INSERT INTO MY_TAB (COL1, COL2, COL3) VALUES ('3','GHI',SYSDATE+2);
1 row created.

SQL> INSERT INTO MY_TAB (COL1, COL2, COL3) VALUES ('4','JKL',SYSDATE+3);
1 row created.

SQL> INSERT INTO MY_TAB (COL1, COL2, COL3) VALUES ('5','MNO',SYSDATE+4);
1 row created.

SQL> COMMIT;

Commit complete.

Create the package we are going to call.

SQL> CREATE OR REPLACE PACKAGE MY_PACK
2  AS
3     ----------------------CURSORS---------------------
4     TYPE O_CUR IS REF CURSOR;
5     --------------------------------------------------
6     PROCEDURE       MY_PROC
7     ( O_REX  OUT O_CUR );
8     --------------------------------------------------
9  END MY_PACK;
10 /
Package created.

SQL> CREATE OR REPLACE PACKAGE BODY MY_PACK
2  AS
3  PROCEDURE       MY_PROC
4  ( O_REX  OUT O_CUR )
5  IS
6  BEGIN
7   OPEN O_REX FOR
8    SELECT COL1, COL2, COL3
9    FROM  MY_TAB
10    ORDER BY COL1;
11  END MY_PROC;
12 END MY_PACK;
13 /
Package body created.

Create a CF template to hit the package and output the data.  

(Note… I only included code needed for the example)

<CFSTOREDPROC PROCEDURE="MY_PACK.MY_PROC" DATASOURCE="myDsn">
  <!--- OUTS --->
  <CFPROCRESULT NAME=O_REX>
</CFSTOREDPROC>

<CFDUMP VAR="#O_REX#">

Results would look like this.

cfprocdump

Notes:

  • There is a proper time and place for different connections to the database and this is just one option.
  • If you want to return multiple recordsets back, add resultset = “2”, resultset = “3” etc. to the CFPROCRESULT tags.
  • Regarding the Oracle code… I kept it simple for the example.  Please no lectures on how I am using a cursor.
  • As a rule, I always (almost always) put procedures inside packages.  I don’t believe this is a requirement.

An Oracle “roles and privs” script

As I mentioned in my “An Oracle ‘who’ script” (https://hagoodc.wordpress.com/2013/10/26/8/) post, I have several scripts I always drop into the oracle bin directory when I start working somewhere.

Next on the list… @rolesprivs.sql

This script quickly displays the roles and privs granted to an Oracle user. The script below displays both normal roles as well as system privs. Nothing special going here, but running @rolesprivs is quicker than running each query seperately.

</pre>
REM ########################################################################
REM ## Author : Chris Hagood
REM ##
REM ## This script will display role/priv information for the username
REM ## provided at the prompt
REM ##
REM #######################################################################

ACCEPT v_username PROMPT 'Enter USERNAME> '
set pages 100 lines 100 verify off feedback off;

col grantee heading 'USERNAME' format a15;
col granted_role heading 'Role' format a35;
col admin_option heading 'Admin|Option?' format a7
col default_role heading 'Default|Role?' format a7

break on grantee;

select grantee, granted_role, admin_option, default_role
from dba_role_privs
WHERE GRANTEE = UPPER('&v_username') or GRANTEE LIKE UPPER('%&v_username%')
order by grantee,granted_role;

COL grantee heading 'USERNAME' format a15
col privilege heading 'PRIVILEGE' format a35
col admin_option heading 'Admin|Option?' format a7

select grantee, privilege, admin_option
from dba_sys_privs
WHERE GRANTEE = UPPER('&v_username') or GRANTEE LIKE UPPER('%&v_username%')
order by grantee, privilege;

clear columns;
clear breaks;
set verify on;
set feedback on;

A couple of other notes:

  • The script doesn’t have to go in your bin directory.  You can tell SQL Plus where to look.  I just use the bin directory.
  • I have been using this script for years.  When I started, I grabbed it from somebody and have modified it for my use.  I would like to give credit to who started it… But I have no idea who they are.

Download: rolesprivs.sql

Screenshot:

rolesprivs screenshot

CFClient is getting a bad rap

As I was sitting in the Adobe Cold Fusion Summit 2013 keynote, one of the topics was a new feature called CFClient. Since I live in the middle tier/back end of the CF development world I was only mildly interested in the new feature. While listening I was a bit surprised by the nearly immediate bashing of the feature. The CFClient portion of the keynote wasn’t even over before everybody had already decided it was a horrible choice by Adobe to even develop.

I am not a mobile app developer, but I suspect that existing developers are not the target audience for CFClient. I remember back in the early days of HTML, I scoffed when applications like PageMill were released. I thought “If you want a web site, you should hire somebody who knows how to build them!!!” Admittedly, that was a very self-serving opinion to have. PageMill, without an understanding of what was happening under the hood, suddenly gave people the ability to create sites with absolutely dreadful code powering them. This wasn’t a positive result, but it also gave people an avenue to learn. Suddenly the world-wide-web wasn’t quite as intimidating. I would say the majority of people created a simple site and walked away… But there were some that looked under the hood. Tried to understand what was happening, and then shed the shackles of the WYSIWYG editor to write there own code.

I think CFClient is headed down the same road. Will the apps be pristine? Doubtful. Will the code meet the standards of “true mobile app” developers? I don’t know, but I don’t think so. Will new people finally decide to give mobile app development a try? Yes! Many will build something akin to “Hello World”, but I think several years down the road, the CF community will have new power developers due to trying out this new CFClient technology.

One of the stats I remember hearing in Ben Forta’s keynote was something like “25% of companies have mobile apps, but 85% want them”. I am roughly paraphrasing here. I think CFClient will bring those numbers together. Sure… I expect to see some really awful apps, but I also expect to see some gems as well. From Adobe’s perspective, I think this only has an upside. There will be sales decided by the managers being marketed to. Some companies don’t have mobile developers and they will pull the trigger on ColdFusion specifically because of this feature. (My opinion here). Adobe sales will increase and the development community will grow. I can’t see that as a bad thing.

To the advanced developers out there saying “I wish Adobe would focus on my needs instead of marketing to managers!”. Remember, that managers are the ones who decide to pony up the money for the CF Enterprise license. I don’t love the idea of bowing to one group of people, but this is an important group that needs some attention. I encourage existing mobile app developers to welcome new developers to the community and to help them understand how to make their apps better. If we immediately dismiss them simply because they started by using CFClient, we are going to discourage people from embracing CF.

Will CFClient survive for years to come? Or will the 2017 CFSummit keynote include this as a slide describing CFClient as a failure? I don’t know, but I look forward to finding out.

If you would like some information from a great source, Ram’s Blog (http://ramkulkarni.com/blog/cfsummit2013-day1-and-cfclient/) has an outstanding entry on the topic.