Oracle SQL replace a return column with a null value

Question

I'd like to modify the following query, so that instead of the ORDER.S_ID column, it fetches the value of the corresponding NAME column from the table (called STORES) S_ID refers to.

However, in some cases, S_ID is null, in which case there is no corresponding name column. In this case, there are still records from the ORDER and ORDERLIST tables that I want COUNT(*)-ed and thus displayed by the query, so that the result set's second column is null

SELECT 
COUNT(*), ORDER.S_ID 
FROM 
ORDER, ORDERLIST 
WHERE 
ORDERLIST.O_ID = ORDER.ID AND 
ORDER.DATE = '13-APR.  -09 19.42.05,259000000' 
GROUP BY S_ID;

sample data:

ORDER table

ID  DATE                            S_ID
----------------------------------------              
1   11-APR.  -09 19.41.39,187000000 null
2   12-APR.  -09 19.42.05,259000000 null
3   13-APR.  -09 19.42.41,688000000 null
11  16-APR.  -09 22.06.11,169000000 3

ORDERLIST table

O_ID    B_ID    PRICE
---------------------
3       6       7999
2       2       2000
2       6       7999
1       5       5000
1       1       1000
11      4       4000
11      6       7999

STORES table

ID  NAME
----------
1   Store1
2   Store2
3   Store3

What I'd like to see returned if a user queries for a date where the S_ID value is NOT null: (this specific case is when queried the date 16-APR. -09 22.06.11,169000000 )

COUNT(*) STORENAME
2        Store3

What I want returned if it IS null: (querying for the date 13-APR. -09 19.42.41,688000000)

COUNT(*) STORENAME
1        null

Alex Poole · Accepted Answer · 2016-04-10 17:04:44Z

1

As Gordon said, you need an outer join, and it's much better to use proper join syntax - particularly with outer joins, but all the time, whatever your tutor is using.

Since you are starting from the order and may have a matching store, you need the join to the stores table to be the outer join:

SELECT o.s_id, s.name, count(*)
FROM "ORDER" o
JOIN orderlist ol ON ol.o_id = o.id
LEFT JOIN stores s ON s.id = o.s_id
--WHERE o."DATE" = TO_TIMESTAMP('13-APR.  -09 19.42.05,259000000', 'DD-MON".  "-RR HH24.MI.SS,FF9')
GROUP BY o.s_id, s.name;

      S_ID NAME     COUNT(*)
---------- ------ ----------
                           5
         3 Store3          2

Since 'order' and 'date' are reserved words I've had to use quoted identifiers for those, but they may not be the real names. Don't use quoted identifiers if you can possibly avoid them. I've used table aliases to make that slightly less painful. The 'date' also a timestamp rather than a date, since you have fractional seconds, making the name even more confusing.

I've commented out the date/timestamp restriction because it didn't match any of your sample data, and if changed to April 12th still only matched one, which wasn't very helpful as a demo. Your NLS_TIMESTAMP_FORMAT is... unusual, but that's a separate issue. For comparison it's simpler to use a timestamp literal:

WHERE o."DATE" = TIMESTAMP '2009-04-12 19:42:05.259000000'

Notice that has colons and a period as the decimal fraction separator for the seconds value, unlike your NLS_TIMESTAMP_FORMAT.

If your tutor really insists on the old, Oracle-specific join syntax then you can arrange it as:

SELECT o.s_id, s.name, count(*)
FROM "ORDER" o, orderlist ol, stores s
WHERE ol.o_id = o.id
AND s.id (+) = o.s_id
--AND o."DATE" = TIMESTAMP '2009-04-12 19:42:05.259000000'
GROUP BY o.s_id, s.name;

... but it's much harder to figure out what's happening, and much easier to make a mistake in the join conditions (or leave one out completely). Please use the proper ANSI join syntax...

answered Apr 10, 2016 at 17:04

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

aurell b Over a year ago

Thank you, your solution worked flawlessly, exactly what I needed! If I recall correctly, SQL developer displays timestamps in a format that is extracted from the user's operating system and it's localization. Mine are displayed like the ones I pasted shrug

MT0 Over a year ago

@aurellb If you want to change the date format in SQL Developer then go to Tools > Preferences... then expand the Database > NLSoptions and change the date or timestamp format in there (to something like YYYY-MM-DD HH24:MI:SS for a date or YYYY-MM-DD HH24:MI:SSXFF for timestamp).

Alex Poole Over a year ago

You can override that in the preferences (there's an NLS section), or with alter session. Purely out of curiosity,which locale produces that? I'd guess French except it would be Avr. rather than Apr. I think. The spaces in the month abbreviation can also be surpressed by changing the default format.

aurell b Over a year ago

It's the Hungarian locale

Gordon Linoff · Accepted Answer · 2016-04-10 23:12:55Z

0

Use left join. In fact, always use explicit join syntax. Never use commas in the from clause:

SELECT s.id, s.name, count(ol.o_id)
FROM stores s left join
     order o
     on s.id = o.s_id left join
     orderlist ol
     on ol.o_id = o.id and
        o.DATE = '13-APR.  -09 19.42.05,259000000' 
GROUP BY s.id, s.name;

The use of commas in the from clause has been out-of-date for about two decades. You should also learn about outer joins.

edited Apr 10, 2016 at 23:12

answered Apr 10, 2016 at 15:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

aurell b Over a year ago

You forgot to close the parenthesis of the first count() :o Thank you for your suggestion, I'll try it out right away! Sadly, they teach this outdated syntax to us (I'm still a student)

aurell b Over a year ago

This query gives an error while executing in SQL Developer, claiming invalid number of arguments for the first count() clause.

Collectives™ on Stack Overflow

Oracle SQL replace a return column with a null value

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related