10 x 10 Matrix using SQL

Hey Friends,

Try this small technique to display your own pattern using a SQL query output.

img01
(Scroll to the bottom of this post for other interesting patterns)

Below are the skeleton steps to achieve.

1. First we need 10×10 matrix.

2. Join two tables, one table with 10 columns and another table with 10 rows.
2a. Say Table with 10 rows as V(Vertical)
select * from (select 1 "1" from dual connect by level <= 10) v;
2b. Say Table with 10 columns as H(Horizontal). This can be easily created
using pivot.
select * from (select level "1" from dual connect by level <= 10)
pivot(sum("1") for "1" in (1,2,3,4,5,6,7,8,9,10)) h;

3.Cartesian join these tables
3a. select * from (select 1 "1" from dual connect by level <= 10) v,
(select level "1" from dual connect by level <= 10) pivot(sum("1")
for "1" in (1,2,3,4,5,6,7,8,9,10)) h;

3b. Now you get a beautiful grid as output as below
img02

4. Now our aim is to ON/OFF these each cell to get desired output
4a. Note that there are 100 cells, so create a display control with 100 bits
and cartesian join with above sql as shown below
select * from (select 1 "1" from dual connect by level <= 10) v,
(select level "1" from dual connect by level <= 10) pivot(sum("1") for
"1" in (1,2,3,4,5,6,7,8,9,10)) h,(select
'1111011110100001001011110100100001010110111101111000000001110001000000000100000000010000000001111000' "DispControl" from dual) Disp;

5. Now comes the part to real switch ON/OFF the cells based on display control
bits
5a. Use decode function to achieve this,
DECODE(SUBSTR(“DispControl”,((rownum-1)*10)+B.”1″,1),1,’|+|’,0,’ ‘) “A”
The above switch on cell with pattern |+| if the corresponding displaycontrol bit is set to
one else blank

6. Here is the final sql
SELECT DECODE(SUBSTR("DispControl",((rownum-1)*10)+h."1",1),1,'|+|',0,' ') "1",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."2",1),1,'|+|',0,' ') "2",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."3",1),1,'|+|',0,' ') "3",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."4",1),1,'|+|',0,' ') "4",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."5",1),1,'|+|',0,' ') "5",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."6",1),1,'|+|',0,' ') "6",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."7",1),1,'|+|',0,' ') "7",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."8",1),1,'|+|',0,' ') "8",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."9",1),1,'|+|',0,' ') "9",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."10",1),1,'|+|',0,' ') "10"
FROM
(SELECT 1 "1" FROM dual CONNECT BY level <= 10
) v,
(SELECT level "1" FROM dual CONNECT BY level <= 10
) pivot(SUM("1") FOR "1" IN (1,2,3,4,5,6,7,8,9,10)) h,
(SELECT '1111011110100001001011110100100001010110111101111000000001110001000000000100000000010000000001111000' "DispControl"
FROM dual
) Disp;

Additional Notes:

Replace DispControl bit with following to get new patterns
Pattern 1: UN
'1000100000100010000010001000001000100000111110000000000100010000011001000001010100000100110000010001'

img03.PNG

Pattern 2: Love
'0010000100010100101010001100011000000001100000000110000000010100000010001000010000010010000000110000'

capture

Note: This SQL works well in SQL Developer running on Oracle 11g database

Use http://www.tutorialspoint.com/oracle_terminal_online.php ; this is an online free oracle 11g terminal to test these sql. If you face any difficulty please share it through the comments.

 

Advertisements

Solution: Run-time error ‘5’: Invalid procedure or argument – PeopleSoft Excel to CI

  1. Go to View tab in Excel; Click on Macros > View Macros
  2. A dialog box appears, select “init” from the list of macro name and click on Run. Note: The data and template will get reset completely. Please have backup of configuration and data present in the excel before triggering “init”
  3. Now fill in connect information, create template, populate data and try again
  4. If error still persists, try opening excel in other compatibility mode(Right click on file > Click Properties >Compatibility tab)

 

Rank(), Dense_Rank() and Row_Number() SQL

Let’s see below example,

Consider our education department has below data and they wanted to find the ranks of students State wise-Z, School wise-Y and Class wise-X.

Wonder how to achieve this in single SQL.

Name School Class Marks Class Rank
X
School Rank
Y
State Rank
Z
Alice Central School A Division 456 1 1 2
Bob Central School A Division 345 2 4 7
Charlie Central School B Division 401 2 3 5
Eve Central School B Division 445 1 2 3
Mallet Liberty School A Division 323 2 4 8
Oscar Liberty School A Division 433 1 2 4
Peggy Liberty School B Division 401 2 3 5
Sybil Liberty School B Division 467 1 1 1

We can achieve this using Rank(), Group by and Partition by concept.

Assume table name as “Scores”

1. Let’s starts with State wise rank for each student

To find State rank we have to order the students marks in descending order and call rank()
query:
select s.*, rank() over (order by Marks desc) “State Rank” from Scores s;

Simple right!!!

2. Let’s see how to find School wise rank for each student

Since multiple school data is in the same table “Scores” we have to partition data first based on school and  then order the Marks in descending order
query:
select s.*, rank() over (partition by School order by Marks desc) “School Rank” from Scores s;

3. Let’s see how to find Class wise rank for each student

It’s very similar to above, here we have to classify data based on School&Class first then order Marks by descending order.
query:
select s.*, rank() over (partition by School, Class order by Marks desc) “Class Rank” from Scores s;

In single query:
select s.*, rank() over (order by Marks desc) “State Rank”, rank() over (partition by School order by Marks desc) “School Rank”, rank() over (partition by School, Class order by Marks desc) “Class Rank” from Scores s;

Note: Marks column should be a number or integer field, if not use to_number(Marks) method then apply order by

We have two common methods like rank(), they are dense_rank() and row_number()

a. dense_rank() – Have you noticed in above example Charlie and Peggy  have the same score and so share same State Rank(5th rank). Due to this 6th rank is skipped and Bob got (7th rank). If you do not want to skip 6th rank use dense_rank() instead of rank().

b. row_number() – In the above example we saw Charlie and Peggy have same score and shared same rank. If we use row_number() one may get 5th and other may get 6th based on the order in which they appear in the query output, since row number just assign the sequential value based on some alphabetical order. This should not be used if you want to find the correct rank.

 

 

 

Server name, Recurrence drop down grayed out in process scheduler; Solution:

This happens if the process profile associated with the user do not have the permission to do so.

Steps to solve ::

1. Navigate PeopleTools > Security > User profiles > User profiles. Search with the user id. You can find the “Process Profile” under “Permission Lists” group box. Copy that to clip board.

2. Now navigate PeopleTools > Security > Permissions & Roles > Permission Lists. Search with the copied value. And click on tab “Process”

3. Click on link “Process Profile Permissions” under “Process Permission” group box.

4. Now check the boxes “Override Server Parameters” for server name update and “Enable Recurrence Selection” to allow user to set Recurrences.

5. Save

If you find this post useful or require more information please feel free to comment. Thank You.