THEN my_pkg.get_resupply_date(p.part_nbr) Resupply date via the gateway otherwise, it returns the resupplyįollows: SELECT s.name supplier_name, p.name part_name, p.part_nbr part_number, If so, it calls the function to retrieve the The DECODE function checks if the supplier name is ' Acme When not needed: SELECT s.name supplier_name, p.name part_name, p.part_nbr part_number, Instead of always calling the function and discarding the results Is far more efficient to call the function only when necessary, Unfortunately, the server is forced to make 10,000 trips across the Report, sit back, and wait for the results. You then include logic in your reporting tool to use the My_pkg.get_resupply_date(p.part_nbr) acme_resupply_date P.inventory_qty in_stock, p.resupply_date resupply_date, Get_resupply_date to retrieve the resupply dateįor parts supplied by ACME, and include it in your query: SELECT s.name supplier_name, p.name part_name, p.part_nbr part_number There are 10,000 parts in yourĭatabase, but only 100 require information via the gateway. Trip from your database through the gateway to the external serverĪnd back takes 1.5 seconds on average. Gather information for parts supplied by Acme Industries. Most of the information resides in your local database, but a tripĪcross a gateway to an external, non-Oracle database is required to Week, with a column for each week, would quickly become tedious. Month works fine, but expanding the query to aggregate sales for each

Obviously, such transformations are only practical when the number of Of this query is as follows: SELECT SUM(CASE WHEN TO_CHAR(order_dt, 'DAY') = 'SUNDAY ' THEN sale_price ELSE 0 END) SUN, SUM(CASE WHEN TO_CHAR(order_dt, 'DAY') = 'MONDAY ' THEN sale_price ELSE 0 END) MON, SUM(CASE WHEN TO_CHAR(order_dt, 'DAY') = 'TUESDAY ' THEN sale_price ELSE 0 END) TUE, SUM(CASE WHEN TO_CHAR(order_dt, 'DAY') = 'WEDNESDAY' THEN sale_price ELSE 0 END) WED, SUM(CASE WHEN TO_CHAR(order_dt, 'DAY') = 'THURSDAY ' THEN sale_price ELSE 0 END) THU, SUM(CASE WHEN TO_CHAR(order_dt, 'DAY') = 'FRIDAY ' THEN sale_price ELSE 0 END) FRI, SUM(CASE WHEN TO_CHAR(order_dt, 'DAY') = 'SATURDAY ' THEN sale_price ELSE 0 END) SAT FROM cust_order WHERE sale_price IS NOT NULL Used for Monday, Tuesday, etc., to sum orders for each of the other Ignoring orders for all other days of the week. Total, which has the effect of summing all Sunday orders while When the valuesįrom all orders are summed, only Sunday orders are added to the Unless an order was booked on a Sunday, in which case the SUN column, for example, a value of 0 is returned That with DECODE: SELECT SUM(DECODE(TO_CHAR (order_dt, 'DAY'), 'SUNDAY ', sale_price, 0)) SUN, SUM(DECODE(TO_CHAR (order_dt, 'DAY'), 'MONDAY ', sale_price, 0)) MON, SUM(DECODE(TO_CHAR (order_dt, 'DAY'), 'TUESDAY ', sale_price, 0)) TUE, SUM(DECODE(TO_CHAR (order_dt, 'DAY'), 'WEDNESDAY', sale_price, 0)) WED, SUM(DECODE(TO_CHAR (order_dt, 'DAY'), 'THURSDAY ', sale_price, 0)) THU, SUM(DECODE(TO_CHAR (order_dt, 'DAY'), 'FRIDAY ', sale_price, 0)) FRI, SUM(DECODE(TO_CHAR (order_dt, 'DAY'), 'SATURDAY ', sale_price, 0)) SAT FROM cust_order WHERE sale_price IS NOT NULL Įach of the seven columns in the previous query are identical, exceptįor the day being checked by the DECODE function. Those records whose order date falls in the desired day. In order to transform this result set into a single row with sevenĬolumns (one for each day in the week), you will need to fabricate aĬolumn for each day of the week and, within each column, sum only The week or months of the year, but you want the result set toĬontain one row with N columns rather than N rows with two columns.Ĭonsider the following query, which aggregates sales data for eachĭay of the week: SELECT TO_CHAR(order_dt, 'DAY') day_of_week, SUM(sale_price) tot_sales FROM cust_order WHERE sale_price IS NOT NULL GROUP BY TO_CHAR(order_dt, 'DAY') ORDER BY 2 DESC Performing aggregations over a finite set of values, such as days of Where feasible we provide both DECODE and CASE versions of eachĮxample to help illustrate the differences between the twoĪpproaches. That you use the CASE expression rather than the DECODE function, Uses of conditional logic in SQL statements. The following sections present a variety of examples illustrating the

Oracle db decode