Scalar Functions
Scalar Functions for String handling (UPPER, LOWER, TRIM, SUBSTR, TRANSLATE, LOCATE)
Solving Problems:
Sorting Character Columns (case insensitive – UPPER/LOWER)
Determining rows with invalid numeric characters in a character field (TRANSLATE)
Converting or Replacing invalid characters (for example due to a wrong CCSID – TRANSLATE, REPLACE)
Left-/Right adjusting texts (TRIM, SPACE, REPEAT, RIGHT, LPAD, RPAD)
Splitting Columns (FirstName, LastName separated by a comma – SUBSTR, LOCATE)
Pitfalls when calculating with Integer Values/Columns (especially when dividing integer numbers)
Rounding Functions
ROUND – Rounding to decimal positions, rounding to 100, 1000 etc.
CEILING/FLOOR Rounding up or down to the next integer.
Example: Packing pallets – 20 Boxes on a pallet – for 102 Boxes 6 pallets and not 5 are needed
Converting between Data Types (CAST)
Date & Time Calculation
Date/Time/Timestamps 3 different data types
Converting numeric Date/Time values into real date (and vice versa)
How to convert a date like Monday 11-SEP-23 19:43 into a real timestamp?
Adding time units (Days, Hours, …) to Dates/Times/Timestamps
Adding/subtracting the same number of months may not return the start date
Subtracting two date/time values:
Why is the result ‘2023-09-10’ – ‘2023-08-08’ 102?
How to get the difference in Minutes or Days?
Date/Time Functions: DayName, Week_ISO, Last_Day, First_Day, Next_Day
Dimension Table Read More
Get empowered with IBM Power
