How to create sql query results in columns from rows

oracle

From the following tables:

store(store_id, ...) {1 ..., 2 ...} two stores
film(film_id, ...)
inventory(inventory_id, film_id, store_id)
rental(rental_id, rental_date, inventory_id, return_date, ...)
category(category_id, name)
film_category(film_id, category_id)
film(film_id, rental_duration, rental_rate, ...)
customer(customer_id, store_id, ...)

Can I have a query result on a table just like the following?

CategoryName | "Store 1: available items" | "Store 1: unavaliable items" | "Store 2: avaliable items" | "Store 2: unavaliable items"

Notice that the store fields in the result table are in separate columns not in the same columns !!

I am not looking for the result query, I am looking for how to make them side by side store 1, store 2.

Best Answer

I don't have the energy to deal with all the joins through all your tables, but the general idea would be to join to the store table twice and check the results.

 SELECT f.film_id,
   CASE when store1.store_id is not null then 'Item exists in store 1' else 'Item missing from store 1' END as 'Store 1 Status'
 FROM film f
 LEFT OUTER JOIN store store1 on f.film_id = store1.film_id AND store1.store_id = 1
 LEFT OUTER JOIN store store2 on f.film_id = store2.film_id AND store2.store_id = 2

This doesn't scale well if you had 100 stores, but for two it wouldn't be evil.

Hope that helps.

Related Solutions

Oracle 11g R2 – same query sometimes fast, sometimes slow

The query is sometimes fast because it is in the database's cache.

Make a test:

alter system flush shared_pool; (as sysdba - NOT IN PRODUCTION !!)
execute the query and display execution plan;
execute the query and display execution plan (should be faster because the query is not parsed but only executed );
alter system flush shared_pool; (as sysdba)
execute the query and display execution plan (should be slower the query is first parsed then executed)

In a ideal world, a query is parsed once and bound/executed over and over.

Oracle – Converting XMLDB Nodes to Rows

I found the answer myself after reading through loads of documentation from Oracle. The answer comes in two parts.

First, my original question can be solved by using XMLTable:

SELECT 
  x.ID            AS "MEASUREMENT_ID", 
  x.GUID          AS "MEASUREMENT_GUID", 
  a.COLUMN_VALUE  AS "MESSAGE"
FROM SPERF_XML_RAW x,
  XMLTable('/PerfInfo/EventLog/EventRecord/@Provider'
            PASSING x.DATA_XML) a;

Note that COLUMN_VALUE is a pseudocolumn that can be used in conjunction with XMLTable

As additional information I would like to add the following. You can create multiple columns with the following syntax:

SELECT 
    x.ID,
    x.GUID,
    res."Provider", 
    res."TimeCreated"
FROM SPERF_XML_RAW x,
    XMLTABLE('/PerfInfo'
      PASSING x.DATA_XML
      COLUMNS 
         "Provider"     VARCHAR2(32 CHAR)   PATH '/PerfInfo/EventLog/EventRecord',
         "TimeCreated"  TIMESTAMP           PATH '/PerfInfo/EventLog/EventRecord') res;

But Oracle will throw ORA-19279: XPTY0004 when one or more columns return multiple nodes.

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence" 
*Cause:    The XQuery sequence passed in had more than one item.
*Action:   Correct the XQuery expression to return a single item sequence.

The solution to THAT problem can be found below:

SELECT
  x.ID              AS "MEASUREMENT_ID",
  x.GUID            AS "MEASUREMENT_GUID",
  res."TIMECREATED", 
  res."LOGNAME",
  res."LEVEL",
  res."ID",
  res."PROVIDER",
  res."MESSAGE"
FROM SPERF_XML_RAW x,
   XMLTable('/PerfInfo' 
      PASSING x.DATA_XML
      COLUMNS
        "EventRecords" XMLTYPE PATH 'EventLog') rec,
   XMLTABLE ('EventLog/EventRecord'
       PASSING rec."EventRecords"
       COLUMNS
        "LOGNAME"         VARCHAR(32 CHAR)      PATH '@LogName',
        "ID"              NUMBER                PATH '@ID',
        "LEVEL"           CHAR(1 CHAR)          PATH '@Level',
        "PROVIDER"        VARCHAR2(128 CHAR)    PATH '@Provider',
        "TIMECREATED"     VARCHAR2(32 CHAR)     PATH '@TimeCreated',
        "MESSAGE"         CLOB                  PATH 'text()') res;

This will probably fail once more as soon as your document has too many levels, and I have not included a document with namespaces, which will most likely cause this to explode as well.

All information was found in the XML DB Developers guide: https://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb03usg.htm#g1055369

Best Answer

Related Solutions

Oracle 11g R2 – same query sometimes fast, sometimes slow

Oracle – Converting XMLDB Nodes to Rows

Related Question