Cannot correctly get expected Union output for SQL

Question

Troubles:

Did not Order By properly and have not laid out code/or used conventions as expected.

Question:

The Union operator needs to be used here and to that end, find vendor_id's specified in line three of the code used and a quantity of less than what is specified in the final line of code. The code will need to be sorted.

Code used:

SELECT job_id, po_id, 'Vendor ' || vendor_id as 'Reason'
FROM pos
WHERE vendor_id IS 'SOS'
UNION
SELECT job_id, po_id, 'Quantity < ' || quantity as 'Reason'
FROM pos
WHERE quantity < 10;

Meant to get:

job_id      po_id       Reason
----------  ----------  ------------
002         AAA         Quantiy < 10
004         CCC         Quantiy < 10
004         CCC         Vendor SOS
005         EEE         Vendor SOS
006         GGG         Quantiy < 10

Received (from CodeRunner):

Runtime error
Program does not use the expected ORDER BY clause or is badly laid out.

See database schema for more details (Primary Keys are in bold).

Unknown DBMS used. Behaves like PostgreSQL and not mysql.

job_id 004 and 005 both have a vendor_id of SOS. job_id 002, 004, 006 have 2, 4, 2 as their quantity respectively. Quantity is spelled as Quantiy for some reason. — Floating Tiki Hut, May 06 '18 at 04:05
The error is a mystery because you have no `ORDER BY` in the statement. — Gordon Linoff, May 06 '18 at 12:11

score 1 · Accepted Answer · answered May 06 '18 at 03:15

1

To sort by job_id follow by po_id, just add ORDER BY clause at the end of query. By default the sort order ASC (ascending), you may add DESC if you want to sort in descending order.

SELECT
    job_id, 
    po_id, 
    'Vendor ' || vendor_id as 'Reason'
FROM pos
WHERE vendor_id = 'SOS'

UNION

SELECT 
    job_id,
    po_id, 
    'Quantity < ' || quantity as 'Reason'
FROM pos
WHERE quantity < 10

ORDER BY 
    job_id,
    po_id;

answered May 06 '18 at 03:15

Shuwn Yuan Tee

5,578
6
28
42

Thank you Shuwn, everything is great, I just need to make it so the quantities do not appear, all I need the table to say is 'Quantity < 10' rather than saying 'Quantity < 2' when the quantity = 2 etc. I have managed to fix this however, with minor tweaks. – Floating Tiki Hut May 06 '18 at 03:35
It worked! I have placed another question to StackOverflow. – Floating Tiki Hut May 06 '18 at 04:22
1

If my answer helps, why not mark it as answer then. – Shuwn Yuan Tee May 06 '18 at 05:01
Apologies, I have just assigned this to your post. Thank you! – Floating Tiki Hut May 08 '18 at 00:12

Cannot correctly get expected Union output for SQL

1 Answers1