Below is temp_data table in Oracle database:
LETTER is the foreign key field, which points to temp_ref table’s LETTER field, whose order is specified by LETTER_SEQ field in the same table.
Task: Group temp_data table by the year and LETTER field, count records in each group, and align records to the cross product of the year and the LETTER; record CNT as null if no count value exists. Arrange years in the chronological order and the LETTER according to LETTER_SEQ. Below is the expected result:
Write the following SPL code:
A1, A2: Run the simple SQL to group and summarize temp_data table; and retrieve values of LETTER field in the specified order.
A3: Compute cross product of the year and the letter, and then associate the result with A1 through the foreign key. #1 is the 1st field.
Top comments (0)