I have an Oracle table as shown below
Orders
---------
ORDERID
DESCRIPTION
TOTALVALUE
ORDERSTATUS
I have the below mentioned query
select ORDERID,ORDERSTATUS
FROM ORDERS
WHERE ORDERID IN( 1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1020,
1030,104,1040,1090,789)
Some orderIDs mentioned above are not in orders table. In spite of that I want the orderIDs to appear in the resultset with status as null.
Appreciate your help.
What about this:
SELECT T.COLUMN_VALUE AS ORDERID, ORD.ORDERSTATUS
FROM TABLE(SYS.ODCINUMBERLIST(
1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1020,1030,104,1040,1090,789
)) T
LEFT JOIN ORDERS ORD ON ORD.ORDERID = T.COLUMN_VALUE;
You can also get it to work if the order IDs aren't fixed but a parameter. But the solution depends on whether you call the statement from PL/SQL or from another programming language such as C#, Java, PHP etc.
Update: SYS.ODCINUMBERLIST is just a table type that's already defined in Oracle. You could use your own type:
CREATE TYPE NUMBER_TABLE_T AS TABLE OF NUMBER;
You can use a CTE as table for the orderIds (or store them into a temporary table), and outer join your Orders:
With tmp As (
Select 1000 As orderId From dual
Union All
Select 1001 From dual
Union All
...
)
Select tmp.orderId, o.orderStatus
From tmp
Left Join orders o On ( o.orderId = tmp.orderId )
orderStatus is NULL, when no order is found.
You would have to do an outer join to accomplish something like this :
SELECT ORDERID, ORDERSTATUS
FROM (
SELECT 1000 AS ORDERID FROM dual UNION SELECT 1001 FROM dual -- etc
) tmpOrderid
LEFT OUTER JOIN ORDERS O
ON tmpOrderid.ORDERID = O.ORDERID;
I have never used Oracle, but there is most likely a function that can generate numbers (for exemple, generate_series(1000, 1010) in PostgreSQL).
there is one more trick in oracle.
SELECT LEVEL + 1000 dt FROM DUAL CONNECT BY LEVEL < (2000 - 1000)
it generates a recordset with 1000 rows which might be left joined with your table.
