SQL Query tips

Multiple row generation from DUAL

select rownum i from dual connect by level <= 16
Above query works only in 10g
select rownum from dual a, dual b connect by level <>
This query works on both 9i and 10g

Desc query

In oracle, you would have used desc to describe the table. Here is how to generate DESC output through sql query.

SELECT column_name || ' ' || decode(nullable, 'N', 'not null', ' ') || ' ' || data_type ||
decode(data_type, 'NUMBER', decode('' || nvl(data_precision, -1), '-1', '', '(' || data_precision || ',' || data_scale || ')'),
'(' || data_length || ')') fields
FROM all_tab_columns
WHERE TABLE_NAME = 'PO_VENDORS';

How to convert row values into columns. or how to perform multi dimensional query?

For Eg
Dept
Year
Amount
A
2005
100
B
2005
500
C
2005
344
A
2006
400
B
2006
122

Here you need to display the report in following format. That is comparative sales analysis of each department between 2005 and 2006.

Dept
2005
2006
A
100
400
B
500
122
C
344
0

The challenge is to bring the row values into columns. And here is the solution.

SELECT dept, sum(decode(year,'2005',amount,0)) "2005",
sum(decode(year,'2006',amount,0)) "2006"
from temp_temp
group by dept;

How can one dump/ examine the exact content of a database column?

SELECT DUMP (col1) FROM tab1 WHERE cond1 = val1;

DUMP (COL1)
----------------
Typ=96 Len=4: 65,66,67,32


For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.

Find out foreign constraints referring your table

select table_name, constraint_name from user_constraints
where r_constraint_name IN (select constraint_name from user_constraints where table_name = 'XML_DOCUMENT' and constraint_type = 'P')
and constraint_type = 'R' ;

ADF EL expressions reference

ADF EL expressions

Security:

Checking if the user has the role assigned
#{securityContext.userInRole['ORDERS-EDIT-ROLE']}

Styling:

Conditionally setting in-line styles.
Eg: Changing background of the tree table rows based on a condition.

#{node.level ==1 ? 'background-color:#FFAD52' :  ( node.level == 2 ?  'background-color:#3173C4;color:white;' : 'background-color:#A5C6FF;'  )  }

Text Values:

EL expressions used along with text values
text="Score Card: (#{bindings.ScoreValue.inputValue})"

Passing Arguments in EL expression

Eg: this function get differrent values for outputtext based on the passed input values.

value="#{backingBeanScope.BackingBean.getDateLabel(pageFlowScope.pbb.startDate,pageFlowScope.pbb.endDate)}"

public String getDateLabel(Object from, Object to) {
        if (from == null && to != null) {
            return "TO:";
        } else if (from != null && to == null) {
            return "FROM:";
        }
        return null;
    }