SQL Developer - F8 SQL History

F8 is the keyboard shortcut to get History of SQL Statements executed.

SQL Developer - SQL Editor File

If you are using SQL Developer and by mistake you have closed window without saving your SQL Editor File?

Don't worry you can get your SQL Statements in unsaved SQL Editor File. The following screen shots will help you to get the unsaved files.

1. Go to the similar path in your machine:

2. To get the recent file, sort the folders by recently changed date time (as shown in the screen shoot) and open the first folder.

FYI: Number of folders in ‘.history’ folder represents number of SQL Editor Files you have opened in your machine.

3. To get the latest file, sort the file by recently changed date time (as shown in the screen shoot) and open the first file. That is the SQL Editor File that you have recently changed and not saved.

Some Tricky SQLs - Leave Days Count

I have following Requirement:

I have to calculate number of 'No Pay Leaves' consumed by an employee in a given period(suppose in the month of Aug 2010). Can i get the desired count in one SQL?

Solution:

In HCM System there is a Dates Attributes Table with name: 'PS_TL_DATES_TBL' (Time and Labor Module). This record will simplify our requirement as shown in the following SQL.

SELECT COUNT(A.THE_DATE)

FROM PS_TL_DATES_TBL A,PS_GP_ABS_EVENT B

WHERE B.PIN_TAKE_NUM = (SELECT PIN_NUM FROM PS_GP_PIN

WHERE PIN_NM LIKE 'TP NPL LVE')

AND B.EMPLID = '000052'

AND B.EMPL_RCD = 0

AND A.THE_DATE BETWEEN B.BGN_DT AND B.END_DT

AND A.THE_DATE BETWEEN '26-AUG-2010' AND '28-AUG-2010' ;

Some Tricky SQLs FTE

I have a requirement where in I have to get starting date and ending date of an FTE Value. Sample Employee data is as follows. Can we create a View which will give my desired rages as shown in the following format.

Employee Data:

EMPLID EMPL_RCD EFFDT FTE

000051 0 01-jan-2008 1

000051 0 10-feb-2008 1

000051 0 10-mar-208 0.5

000051 0 10-apr-2008 0.5

000051 0 10-may-2008 1

000051 0 10-jun-2008 1

000051 0 10-jul-2008 0.5

Required output format:

EMPLID EMPL_RCD FTE FTE_BGN_DT FTE_END_DT

000051 0 1 01-jan-2008 09-mar-2008

000051 0 0.5 10-mar-2008 09-may-2008

000051 0 1 10-may-2008 09-Jul-2008

000051 0 0.5 10-jul-2008 null

If the above tables are not clear enough to understand, Below image will give clear picture of the table structure.

Some Tricky SQLs

Let’s suppose, In a Table, we have the following kind of data. I want to change the SEQUENCE_NUMBER field value to a running number, numbering starting with 1000. Can we write a single SQL statement to get desired result?

Table Name: TABLE1

EMPLID NAME SEQUENCE_NUMBER
000071 Same Name 1
000071 Same Name 1
000071 Same Name 1
000071 Same Name 1

After executing SQL the data should be like this:

EMPLID NAME SEQUENCE_NUMBER
000071 Same Name 1001
000071 Same Name 1002
000071 Same Name 1003
000071 Same Name 1004

The tricky part is that, we can not select single row with any Where condition. Can you suggest any solution?

Answer: Is simple

UPDATE TABLE1 SET SEQUENCE_NUMBER = ROWNUM + 1000;

Subquery

SELECT (SELECT DEPENDENT_BENEF FROM PS_DEP_BENEF_VW WHERE EMPLID = A.EMPLID AND DEPENDENT_BENEF = '01') DEPENDENT_BENEF FROM (SELECT EMPLID,EMPL_RCD FROM PS_JOB WHERE EMPLID = '000072') A;

This should work in HCM Instance. Query before From should select only one field and one row.
If we select more than one field, we will get the following error: ‘ORA-00913: too many values’
If subquery selects more than one row, we will get the following error: ‘ORA-01427: single-row subquery returns more than one row’

Dynamic Dropdown Values

&FLD = GetRecord(Record.HM_EMP_BEN_PLAN).GetField(Field.HM_BEN_PLAN_TYPE);
&FLD.ClearDropDownList();
&Xlat = CreateRowset(Record.PSXLATITEM);
&Xlat.Fill("WHERE FILL.FIELDNAME = 'HM_BEN_PLAN_TYPE' AND FILL.FIELDVALUE IN ('10','01') AND FILL.EFFDT = (SELECT MAX(EFFDT) FROM PSXLATITEM B WHERE B.FIELDNAME = 'HM_BEN_PLAN_TYPE' AND B.FIELDVALUE = FILL.FIELDVALUE AND EFFDT <= SYSDATE)");
&Xlat_cnt = &Xlat.ActiveRowCount;

For &I = 1 To &Xlat_cnt
&CodeIn = &Xlat.GetRow(&I).GetRecord(1).FIELDVALUE.Value;
&DescIn = &Xlat.GetRow(&I).GetRecord(1).XLATLONGNAME.Value;
&FLD.AddDropDownItem(&CodeIn, &DescIn);
End-For;

Syntax for Some Object Oriented Programming Language in PeopleSoft

&record.field.Enabled = False is equivalent to UnGray(RecordName.FieldName)
&record.field.Enabled = True is equivalent to Gray(RecordName.FieldName)

in Gray() and UnGray() functions we can not use Variable in place of record name.
Example:
case 1: This will not work
&RECORD = &LEVEL2_ROW.HM_EMP_BEN_D_VW;
Gray(&RECORD.HM_INCURRED_BY);

Case 2: This will work
&RECORD = &LEVEL2_ROW.HM_EMP_BEN_D_VW;
&RECORD.HM_INCURRED_BY.enabled = False;
This is very useful in Row Traversing where we can not use RecordName.FieldName.

Some more OOPS Syntax in PS:
&record.field.IsChanged;