Monday, August 27, 2007

Preserve Leading Zeros in ApEx CSV Export

I located a method of preserving leading zeros on values when exporting a report region to CSV in HTML DB (ApEx).

When Excel opens up a CSV file it defaults all cells to "General" formatting and attempts to "help the user" by removing leading zeros on cell values and converting them from strings to numbers. That can be a problem for values like product codes (SKUs) that begin with a zero.

The trick is to SELECT the value such that Excel will see it as a string and not a number. So prefix an equals sign and double quote on to the value and post fix a double quote. So

SELECT your_column
FROM your_table

becomes

SELECT '="'||your_column||'"'
FROM your_table

That is great except the users now see the additional characters in the report region. To get a round this use the following SELECT statement:

SELECT your_column, '="'||your_column||'"' quoted_column
FROM your_table

and put a "PL/SQL Express" condition (based on Sergio's blog entry) on each column. The conditional display express for YOUR_COLUMN would be:

INSTR(NVL(:REQUEST,'FOO'),'FLOW_EXCEL') = 0

The conditional display express for QUOTED_COLUMN would be:

INSTR(NVL(:REQUEST,'FOO'),'FLOW_EXCEL') != 0

To get even more customized CSV output look at Scott's blog entry.

Friday, August 24, 2007

SQL Developer's "SQL History"

I love the SQL History button (F8) in SQL Developer. Let me tell you what happened to me yesterday. I using SQL Developer to work on a difficult SELECT statement when the electricity at work went out on me. Poof my SELECT statement was gone, right? Not in SQL Developer. When everything came up I started SQL Developer, opened a SQL Worksheet, and clicked on the SQL History button . There was my SELECT statement.

Thanks Oracle for the button.

Thursday, August 23, 2007

Table Rows to One String in SQL

I came across of very cool feature yesterday that I had not seen before. It was the ability to take values of a column in multiple rows and concatenate them into one string. This is useful for building a TO distribution list for an email. Here is an example of concatenating all the names of the employees in the EMP table:

SQL> SELECT *
2 FROM emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------------- ---- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL>
SQL> SELECT LTRIM(SYS_CONNECT_BY_PATH(myvalues, ','),',') catvalues
2 FROM (SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
3 FROM (SELECT ename myvalues
4 FROM emp
5 )
6 ) data
7 WHERE rn = cnt
8 START WITH rn = 1
9 CONNECT BY PRIOR rn = rn-1;

CATVALUES
-----------------------------------------------------------------------------------
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

Here is the SELECT statement:

SELECT LTRIM(SYS_CONNECT_BY_PATH(myvalues, ','),',') catvalues
FROM (SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
FROM (SELECT ename myvalues
FROM emp
)
) data
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR rn = rn-1;

To figure this out let us look at the inner SELECT:

SQL>
SQL> SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
2 FROM (SELECT ename myvalues
3 FROM emp
4 );

MYVALUES RN CNT
---------- ---------- ----------
ADAMS 1 14
ALLEN 2 14
BLAKE 3 14
CLARK 4 14
FORD 5 14
JAMES 6 14
JONES 7 14
KING 8 14
MARTIN 9 14
MILLER 10 14
SCOTT 11 14
SMITH 12 14
TURNER 13 14
WARD 14 14

14 rows selected.


Oracle starts at the top where
rn = 1
which is ADAMS. It connects each node of the tree to the prior element where the prior's RN value is one less
rn = rn -1
until WARD. Now the SYS_CONNECT_BY_PATH function in a CONNECT BY query takes the tree and concatenates the values together putting a comma ',' between the nodes in the tree.

Tuesday, August 21, 2007

Updated GUI Intensive ApEx Application post

I will keep adding issues to my GUI intensive ApEx Application blog as I encounter them. This will keep them all together in one place.