![]() 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
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |