SQL Command not properly ended where I am trying to Use UNION function

Question

this is the code I wrote, and I have been getting the error.

What I am trying to do is that Client have 2 child classes "CASUAL_JOB and CONTRACT_JOB" but these 2 classes have Client No (primary key of Client Table) under different Heading. So my target is to find total cost (info in Invoice Table) which is integrated with JobID (present in Invoice, Contract_Job, Contract_job Tables) and retrieve Client Info from Client Table

(+) is for left Outer Joint

SELECT 
    C.ClientNo, 
    C.ClientName, 
    NVL(TotalExpenses,0)
FROM
     CLIENT C,
     (SELECT 
          C.ClientNo, 
          C.ClientName, 
          NVL(SUM(I.Amount),0) AS TotalExpenses
      FROM 
          CLIENT C, 
          CASUAL_JOB CA, 
          INVOICE I
      WHERE 
          C.ClientNo = CA.IndividualClientNo 
        AND 
          I.JobID = CA.JobID
     ) R1
     UNION
     (SELECT 
          C.ClientNo, 
          C.ClientName, 
          NVL(SUM(I.Amount),0) AS TotalExpenses
      FROM 
          CLIENT C, 
          CONTRACT_JOB CO, 
          INVOICE I
      WHERE 
          I.JobID = CO.JobID 
        AND 
          CO.CorporateClientNo = C.ClientNo
     ) R2
WHERE 
    R2.CLientNo = R1.ClientNo (+);

The sample data is the one below.

----DUMMY DATA INSERTION COMMANDS-----------------
-- CLIENT (ClientNo, ClientName, ClientAddress, ClientPhone, ClientEmail)
INSERT INTO CLIENT VALUES('1','Rachel Green', '63 Rosebery Avenue, Preston', '0540677455', 'rachel.green@gmail.com');
INSERT INTO CLIENT VALUES('2','Kazi Gellar', '57 Rosebery Rd, Preston', '0450677456', 'kazi.gellar@gmail.com');
INSERT INTO CLIENT VALUES('3','Syed Ahmed', '123 Waratah St, Thomastown', '0450672446', 'syed.ahmed@gmail.com');
INSERT INTO CLIENT VALUES('4','Esrar Mahbub', '3 Kingsburry Dr, Bundoora', '044576823', 'esrar.mahbub@gmail.com');
INSERT INTO CLIENT VALUES('5','Samira Sadmin', '1053 Kingsburry Dr, Bundoora', '0411077455', 'samira.sadmin@gmail.com');
INSERT INTO CLIENT VALUES('6','Adam Lever', '11 Cheddar Rd, Reservoir', '0440677455', 'adam.lever@gmail.com');
INSERT INTO CLIENT VALUES('7','Kazi Nipu', '12 Belah St, Thomastown', '0123477455', 'kazi.nipu@gmail.com');
INSERT INTO CLIENT VALUES('8','Arefin Islam', '32 Grace St, Laverton', '0390677455', 'arefin.islam@gmail.com');
INSERT INTO CLIENT VALUES('9','Roger Federer', '11 Alps Rd, Melbourne', '0455577455', 'roger.federer@gmail.com');
INSERT INTO CLIENT VALUES('10','Andy Marray', '13 Kings Rd, Melbourne', '0555577456', 'andy.marray@gmail.com');
INSERT INTO CLIENT VALUES('11','Joe Santrini', '56 Dunne St, Bundoora', '0540677467', 'joe.santrini@gmail.com');
INSERT INTO CLIENT VALUES('12','David Packman', '11 St Kilda Rd, Melbourne', '0540677411', 'david.packman@gmail.com');
INSERT INTO CLIENT VALUES('13','Cristopher Nolan', '15 Lonsdale St, Melbourne', '0540677477', 'c.nolan@gmail.com');
INSERT INTO CLIENT VALUES('14','Bruse Wayne', '10 Batcave Aveneu, Epping', '0111111455', 'bruce.wayne@gmail.com');
INSERT INTO CLIENT VALUES('15','Chandler Bing', '2/80 Friends Rd, Altona', '0540677222', 'chandler.bing@gmail.com');
INSERT INTO CLIENT VALUES('16','Ross Geller', '33 Waratah St, Altona', '0540444455', 'ross.geller@gmail.com');
INSERT INTO CLIENT VALUES('17','Joe Tribiany', '3/80 Friends Rd, Altona', '0540677221', 'joe.tribiany@gmail.com');
INSERT INTO CLIENT VALUES('18','Jack Roland', '41 Alexander Avenue, Thomastown', '0540677999', 'jack.roland@gmail.com');
INSERT INTO CLIENT VALUES('19','Henry Steven', '2 Queens St, Melbourne', '0450872449', 'henry.steven@gmail.com');
INSERT INTO CLIENT VALUES('20','Ragibul Hasan', '13 Swanston St, Melbourne', '0540687878', 'ragibul.hasan@gmail.com');

--CORPORATE_CLIENT (ClientNo, CorporationName, BusinessAddress)
INSERT INTO CORPORATE_CLIENT VALUES('1', 'Apple Inc', '10 Southbank, Melbourne');
INSERT INTO CORPORATE_CLIENT VALUES('2', 'Microsoft', '15 Southbank, Melbourne');
INSERT INTO CORPORATE_CLIENT VALUES('3', 'Dell', '23 High St, Preston');
INSERT INTO CORPORATE_CLIENT VALUES('4', 'IBM', '628 Victoria St, Epping');
INSERT INTO CORPORATE_CLIENT VALUES('5', 'Grocon', '180 Swanston St, Melbourne');
INSERT INTO CORPORATE_CLIENT VALUES('6', 'La Trobe University', 'Plenty Road and Kingsburry Drive, Bundoora');
INSERT INTO CORPORATE_CLIENT VALUES('7', 'Public Transport Victoria', '750 Collins St, Melbourne');
INSERT INTO CORPORATE_CLIENT VALUES('8', 'RACV', '105 Grimshaw St, Greensborough');
INSERT INTO CORPORATE_CLIENT VALUES('9', 'Woolworths', '1 Bell St, Preston');
INSERT INTO CORPORATE_CLIENT VALUES('10', 'Coles', '71 May Rd, Lalor');

--INDIVIDUAL_CLIENT (ClientNo, PropertyOwner)
INSERT INTO INDIVIDUAL_CLIENT VALUES('11', 'Y');
INSERT INTO INDIVIDUAL_CLIENT VALUES('12', 'Y');
INSERT INTO INDIVIDUAL_CLIENT VALUES('13', 'N');
INSERT INTO INDIVIDUAL_CLIENT VALUES('14', 'N');
INSERT INTO INDIVIDUAL_CLIENT VALUES('15', 'Y');
INSERT INTO INDIVIDUAL_CLIENT VALUES('16', 'N');
INSERT INTO INDIVIDUAL_CLIENT VALUES('17', 'Y');
INSERT INTO INDIVIDUAL_CLIENT VALUES('18', 'N');
INSERT INTO INDIVIDUAL_CLIENT VALUES('19', 'N');
INSERT INTO INDIVIDUAL_CLIENT VALUES('20', 'N');

--BUSINESS (ABNNumber, BusinessName, ContactName, ContactNumber, ContactEmail, BusinessAddress, BusinessPostcode)
INSERT INTO BUSINESS VALUES('12345678911', 'James Mowers and Landscape', 'James Handerson', '038122354', 'james@yahoo.com', '2 Davidson st, Epping', 3076);
INSERT INTO BUSINESS VALUES('12345678912', 'Consolidated Proerty Services', 'Monica Rashid', '039342354', 'hrashid@cps.com', 'Cr Lonsdale St and Russel St, Melbourne', 3000);
INSERT INTO BUSINESS VALUES('12345678913', 'Kohlan Movers', 'Vicky Kohlan', '039342355', 'moversk@gmail.com', '4/3 Grace St, Laverton', 3028);
INSERT INTO BUSINESS VALUES('12345678914', 'Pumbers Delivered', 'Harry Tradil', '039342344', 'harryt@hotmail.com', '3 Station St, Reservoir', 3073);
INSERT INTO BUSINESS VALUES('12345678915', 'Anytime Plumbers', 'Joseph Green', '039342322', 'jgreen@hotmail.com', '11 Station St, Reservoir', 3073);
INSERT INTO BUSINESS VALUES('12345678916', 'Change the Fuse Electricals', 'Brian Adams', '039342311', 'adamsb@yahoo.com.au', '12 Laimar St, Sunbury', 3429);
INSERT INTO BUSINESS VALUES('12345678917', 'Vicky Electricals', 'Vicky Hasan', '039342300', 'vickyh@gmail.com', '12 Wood St, North Melbourne', 3051);
INSERT INTO BUSINESS VALUES('12345678918', 'Leak and Roof Repair', 'David Bongiorno', '039342400', 'davidb@yahoo.com', '101 Swanston St, Melbourne', 3000);
INSERT INTO BUSINESS VALUES('12345678919', 'Construction Solutions', 'Garry Hobart', '039342399', 'garryh@hotmail.com', '21 Albundy Gr, St Kilda', 3182);
INSERT INTO BUSINESS VALUES('12345678920', 'Build a House', 'Jason Staham', '039342388', 'jasons@hotmail.com', '1055 Plenty Rd, Bundoora', 3083);
INSERT INTO BUSINESS VALUES('12345678921', 'Hire a Wire', 'David Sully', '039341188', 'davids@yourmail.com', '67 Cramer St, Preston', 3072);
INSERT INTO BUSINESS VALUES('12345678922', 'Joe''s Rubbish Removal', 'Joseph Mahmud', '039342001', 'joseph@openmail.com', '108 Bell St, Preston', 3072);
INSERT INTO BUSINESS VALUES('12345678923', 'Do it Right Mowers', 'Abdullah Mamun', '038122333', 'mamun@yahoo.com', '31 Stephen St, Epping', 3076);
INSERT INTO BUSINESS VALUES('12345678924', 'Shine Cleaning', 'Cyn Jones', '038128989', 'jonesc@gmail.com', '17 Husband St, Eltham', 3095);
INSERT INTO BUSINESS VALUES('12345678925', 'Moveit', 'Sunny Joe', '038000033', 'sunny@hotmail.com', '90 Russel St, Melbourne', 3000);
INSERT INTO BUSINESS VALUES('12345678926', 'Home Cleaning Services', 'Omid Rostami', '038002333', 'omid@hotmail.com', '40 Statino St, North Melbourne', 3051);
INSERT INTO BUSINESS VALUES('12345678927', '24X7 Plumbing Service', 'Hayden Jones', '045002333', 'hjones@yahoo.com', '12 Rathdown Rd, Melbourne', 3000);
INSERT INTO BUSINESS VALUES('12345678928', 'Muscle Rubbish Removals', 'Alex Sanders', '045067633', 'alex@mrr.com.au', '57 Leeds St, Thomastown', 3074);

--CORPORATE_BUSINESS (ABNNumber)
INSERT INTO CORPORATE_BUSINESS VALUES('12345678911');
INSERT INTO CORPORATE_BUSINESS VALUES('12345678912');
INSERT INTO CORPORATE_BUSINESS VALUES('12345678914');
INSERT INTO CORPORATE_BUSINESS VALUES('12345678915');
INSERT INTO CORPORATE_BUSINESS VALUES('12345678916');
INSERT INTO CORPORATE_BUSINESS VALUES('12345678918');
INSERT INTO CORPORATE_BUSINESS VALUES('12345678919');
INSERT INTO CORPORATE_BUSINESS VALUES('12345678920');
INSERT INTO CORPORATE_BUSINESS VALUES('12345678921');
INSERT INTO CORPORATE_BUSINESS VALUES('12345678928');

--ELITE_MEMBER (EliteMemberID)
INSERT INTO ELITE_MEMBER VALUES('1');
INSERT INTO ELITE_MEMBER VALUES('2');
INSERT INTO ELITE_MEMBER VALUES('3');
INSERT INTO ELITE_MEMBER VALUES('4');
INSERT INTO ELITE_MEMBER VALUES('5');
INSERT INTO ELITE_MEMBER VALUES('6');
INSERT INTO ELITE_MEMBER VALUES('7');
INSERT INTO ELITE_MEMBER VALUES('8');
INSERT INTO ELITE_MEMBER VALUES('9');
INSERT INTO ELITE_MEMBER VALUES('10');
INSERT INTO ELITE_MEMBER VALUES('11');
INSERT INTO ELITE_MEMBER VALUES('12');
INSERT INTO ELITE_MEMBER VALUES('13');

--FREELANCER_BUSINESS (ABNNumber, EliteMemberID)
INSERT INTO FREELANCER_BUSINESS VALUES('12345678913', '1');
INSERT INTO FREELANCER_BUSINESS VALUES('12345678917', '2');
INSERT INTO FREELANCER_BUSINESS VALUES('12345678922', '3');
INSERT INTO FREELANCER_BUSINESS VALUES('12345678923', '4');
INSERT INTO FREELANCER_BUSINESS VALUES('12345678924', '5');
INSERT INTO FREELANCER_BUSINESS VALUES('12345678925', null);
INSERT INTO FREELANCER_BUSINESS VALUES('12345678926', '13');
INSERT INTO FREELANCER_BUSINESS VALUES('12345678927', '6');

--SUBURB (Postcode, SuburbName)
INSERT INTO SUBURB VALUES(3083, 'Bundoora');
INSERT INTO SUBURB VALUES(3000, 'Melbourne');
INSERT INTO SUBURB VALUES(3076, 'Epping');
INSERT INTO SUBURB VALUES(3028, 'Laverton');
INSERT INTO SUBURB VALUES(3073, 'Reservoir');
INSERT INTO SUBURB VALUES(3074, 'Thomastown');
INSERT INTO SUBURB VALUES(3429, 'Sunbury');
INSERT INTO SUBURB VALUES(3182, 'St Kilda');
INSERT INTO SUBURB VALUES(3051, 'North Melbourne');
INSERT INTO SUBURB VALUES(3072, 'Preston');
INSERT INTO SUBURB VALUES(3095, 'Eltham');
INSERT INTO SUBURB VALUES(3018, 'Altona');
INSERT INTO SUBURB VALUES(3088, 'Greensborough');
INSERT INTO SUBURB VALUES(3075, 'Lalor');

--ADJACENT_SUBURB (PostCode, AdjacentPostCode)
INSERT INTO ADJACENT_SUBURB VALUES(3083, 3072);
INSERT INTO ADJACENT_SUBURB VALUES(3072, 3083);
INSERT INTO ADJACENT_SUBURB VALUES(3072, 3074);
INSERT INTO ADJACENT_SUBURB VALUES(3074, 3072);
INSERT INTO ADJACENT_SUBURB VALUES(3072, 3073);
INSERT INTO ADJACENT_SUBURB VALUES(3073, 3072);
INSERT INTO ADJACENT_SUBURB VALUES(3083, 3074);
INSERT INTO ADJACENT_SUBURB VALUES(3074, 3083);
INSERT INTO ADJACENT_SUBURB VALUES(3073, 3083);
INSERT INTO ADJACENT_SUBURB VALUES(3083, 3073);
INSERT INTO ADJACENT_SUBURB VALUES(3028, 3018);
INSERT INTO ADJACENT_SUBURB VALUES(3018, 3028);
INSERT INTO ADJACENT_SUBURB VALUES(3075, 3074);
INSERT INTO ADJACENT_SUBURB VALUES(3074, 3075);
INSERT INTO ADJACENT_SUBURB VALUES(3072, 3075);
INSERT INTO ADJACENT_SUBURB VALUES(3075, 3072);
INSERT INTO ADJACENT_SUBURB VALUES(3083, 3088);
INSERT INTO ADJACENT_SUBURB VALUES(3088, 3083);
INSERT INTO ADJACENT_SUBURB VALUES(3095, 3088);
INSERT INTO ADJACENT_SUBURB VALUES(3088, 3095);
INSERT INTO ADJACENT_SUBURB VALUES(3182, 3000);
INSERT INTO ADJACENT_SUBURB VALUES(3000, 3182);
INSERT INTO ADJACENT_SUBURB VALUES(3000, 3051);
INSERT INTO ADJACENT_SUBURB VALUES(3051, 3000);
INSERT INTO ADJACENT_SUBURB VALUES(3083, 3076);
INSERT INTO ADJACENT_SUBURB VALUES(3076, 3083);
INSERT INTO ADJACENT_SUBURB VALUES(3072, 3076);
INSERT INTO ADJACENT_SUBURB VALUES(3076, 3072);
INSERT INTO ADJACENT_SUBURB VALUES(3076, 3075);
INSERT INTO ADJACENT_SUBURB VALUES(3075, 3076);




   --TRADE_UNION (UnionID, UnionTitle, UnionContactName, UnionContactNumber, UnionEmail, UnionAddress, EliteMemberID)
    INSERT INTO TRADE_UNION VALUES('1', 'Mowers'' Union', 'John Doe', '0460782331','mowers.union@gmail.com', '15 Flinders St, Melbourne', '7');
    INSERT INTO TRADE_UNION VALUES('2', 'Cleaners'' Union', 'Jane Joe', '0460782332','cleaners.union@gmail.com', '11 Bourke St, Melbourne', '8');
    INSERT INTO TRADE_UNION VALUES('3', 'Movers'' Union', 'Jack Hammer', '0460782333','movers.union@gmail.com', '9 La Trobe St, Melbourne','9');
    INSERT INTO TRADE_UNION VALUES('4', 'Electricians'' Union', 'John Board', '0460782334','electricians.union@gmail.com', '19 Collins St, Melbourne', '10');
    INSERT INTO TRADE_UNION VALUES('5', 'Plumbers'' Union', 'Loren Daniels', '0460782335','plumbers.union@gmail.com', '2/34 Little Lonsdale St, Melbourne', '11');
    INSERT INTO TRADE_UNION VALUES('6', 'Construction Workers'' Union', 'Harvey Dent', '0460782336','constructionworkers.union@gmail.com', 'Lonsdale St and Russel St, Melbourne', '12');

--INDUSTRY (IndustryID, IndustryTitle, UnionID)
INSERT INTO INDUSTRY VALUES('1', 'Cleaning', '2');
INSERT INTO INDUSTRY VALUES('2', 'Heavy Goods Transport', '3');
INSERT INTO INDUSTRY VALUES('3', 'Landscape', '1');
INSERT INTO INDUSTRY VALUES('4', 'Lawn and Backyard Mowing', '1');
INSERT INTO INDUSTRY VALUES('5', 'Plumbing', '5');
INSERT INTO INDUSTRY VALUES('6', 'Electrical Works', '4');
INSERT INTO INDUSTRY VALUES('7', 'Roof Restoration', '6');
INSERT INTO INDUSTRY VALUES('8', 'General Construction', '6');
INSERT INTO INDUSTRY VALUES('9', 'Demolition and Rubbish Removal', '6');


--JOB (JobID, JobDescription, UrgencyLevel, JobAddress, Postcode, IndustryID, SelectedBusinessABNNumber)
INSERT INTO JOB VALUES('1', 'I need my lawn mowed', 'Immediate', '56 Dunne St, Bundoora', 3083, '4', NULL);
INSERT INTO JOB VALUES('2', 'Backyard mowing job', 'Normal', '11 St Kilda Rd, Melbourne', 3000, '4', NULL);
INSERT INTO JOB VALUES('3', 'Need help to move to a new house', 'Immediate', '15 Lonsdale St, Melbourne', 3000, '2', NULL);
INSERT INTO JOB VALUES('4', 'Movers needed to change house', 'Immediate', '10 Batcave Aveneu, Epping', 3076, '2', NULL);
INSERT INTO JOB VALUES('5', 'Leake in the roof needs fixing', 'Immediate', '2/80 Friends Rd, Altona', 3018, '7', NULL);
INSERT INTO JOB VALUES('6', 'Roof needs a paint', 'Low', '33 Waratah St, Altona', 3018, '7', NULL);
INSERT INTO JOB VALUES('7', 'Need my house cleaned after a party', 'Normal', '3/80 Friends Rd, Altona', 3018, '1', NULL);
INSERT INTO JOB VALUES('8', 'Need a new landscape design', 'Normal', '41 Alexander Avenue, Thomastown', 3074, '3', NULL);
INSERT INTO JOB VALUES('9', 'Backyard garbage needs to be removed', 'Immediate', '2 Queens St, Melbourne', 3000, '9', NULL);
INSERT INTO JOB VALUES('10', 'Leak in the basin bottom', 'Immediate', '13 Swanston St, Melbourne', 3000, '5', NULL);
INSERT INTO JOB VALUES('11', 'Need a contractual cleaner', 'Low', '10 Southbank, Melbourne', 3000, '1', NULL);
INSERT INTO JOB VALUES('12', 'Contractual electrical wiring job', 'Immediate', '15 Southbank, Melbourne', 3000, '6', NULL);
INSERT INTO JOB VALUES('13', 'Electrician needed for 3 months period', 'Normal', '23 High St, Preston', 3072, '6', NULL);
INSERT INTO JOB VALUES('14', 'New office space extension', 'Low', '628 Victoria St, Epping', 3076, '8', NULL);
INSERT INTO JOB VALUES('15', 'Plumbing line inspection and maintenance', 'Immediate', '180 Swanston St, Melbourne', 3000, '5', NULL);
INSERT INTO JOB VALUES('16', 'New courtyard construction: Thomas Cherry Building', 'Normal', 'Plenty Road and Kingsburry Drive', 3083, '8', NULL);
INSERT INTO JOB VALUES('17', 'Structural crack repair', 'Immediate', '750 Collins St, Melbourne', 3000, '8', NULL);
INSERT INTO JOB VALUES('18', 'Carpark construction', 'Low', '105 Grimshaw St, Greensborough', 3088, '8', NULL);
INSERT INTO JOB VALUES('19', 'Commercial cleaners needed', 'Immediate', '1 Bell St, Preston', 3072, '1', NULL);
INSERT INTO JOB VALUES('20', 'Nightly stock-rubbish removal', 'Immediate', '71 May Rd, Lalor', 3075, '9', NULL);




--CONTRACT_JOB (JobID, StartDate, EndDate, CorporateClientNo)
INSERT INTO CONTRACT_JOB VALUES('11', NULL, NULL, '1');
INSERT INTO CONTRACT_JOB VALUES('12', TO_DATE('03-MAR-2020', 'DD-MON-YYYY'), TO_DATE('02-MAR-2021', 'DD-MON-YYYY'), '2');
INSERT INTO CONTRACT_JOB VALUES('13', TO_DATE('27-FEB-2020', 'DD-MON-YYYY'), TO_DATE('26-AUG-2020', 'DD-MON-YYYY'), '3');
INSERT INTO CONTRACT_JOB VALUES('14', TO_DATE('15-MAR-2020', 'DD-MON-YYYY'), TO_DATE('14-MAR-2022', 'DD-MON-YYYY'), '4');
INSERT INTO CONTRACT_JOB VALUES('15', NULL, NULL, '5');
INSERT INTO CONTRACT_JOB VALUES('16', TO_DATE('25-FEB-2015', 'DD-MON-YYYY'), TO_DATE('24-FEB-2017', 'DD-MON-YYYY'), '6');
INSERT INTO CONTRACT_JOB VALUES('17', TO_DATE('01-JAN-2018', 'DD-MON-YYYY'), TO_DATE('31-DEC-2020', 'DD-MON-YYYY'), '7');
INSERT INTO CONTRACT_JOB VALUES('18', TO_DATE('06-JUN-2018', 'DD-MON-YYYY'), TO_DATE('05-DEC-2018', 'DD-MON-YYYY'), '8');
INSERT INTO CONTRACT_JOB VALUES('19', TO_DATE('02-MAR-2019', 'DD-MON-YYYY'), TO_DATE('01-MAY-2019', 'DD-MON-YYYY'), '9');
INSERT INTO CONTRACT_JOB VALUES('20', NULL, NULL, '10');

--CASUAL_JOB (JobID, IndividualClientNo)
INSERT INTO CASUAL_JOB VALUES('1', '11');
INSERT INTO CASUAL_JOB VALUES('2', '12');
INSERT INTO CASUAL_JOB VALUES('3', '13');
INSERT INTO CASUAL_JOB VALUES('4', '14');
INSERT INTO CASUAL_JOB VALUES('5', '15');
INSERT INTO CASUAL_JOB VALUES('6', '16');
INSERT INTO CASUAL_JOB VALUES('7', '17');
INSERT INTO CASUAL_JOB VALUES('8', '18');
INSERT INTO CASUAL_JOB VALUES('9', '19');
INSERT INTO CASUAL_JOB VALUES('10', '20');

--QUOTATION (JobID, ABNNumber, QuoteAmount)
INSERT INTO QUOTATION VALUES('1', '12345678923', 100.00);
INSERT INTO QUOTATION VALUES('2', '12345678923', 80.00);
INSERT INTO QUOTATION VALUES('3', '12345678913', 300.00);
INSERT INTO QUOTATION VALUES('3', '12345678925', 280.00);
INSERT INTO QUOTATION VALUES('4', '12345678913', 250.00);
INSERT INTO QUOTATION VALUES('7', '12345678926', 100.00);
INSERT INTO QUOTATION VALUES('8', '12345678923', 400.00);
INSERT INTO QUOTATION VALUES('9', '12345678922', 150.00);
INSERT INTO QUOTATION VALUES('10', '12345678927', 700.00);
INSERT INTO QUOTATION VALUES('11', '12345678912', 3000.00);
INSERT INTO QUOTATION VALUES('12', '12345678916', 10000.00);
INSERT INTO QUOTATION VALUES('12', '12345678921', 12000.00);
INSERT INTO QUOTATION VALUES('13', '12345678916', 15000.00);
INSERT INTO QUOTATION VALUES('14', '12345678919', 7000.00);
INSERT INTO QUOTATION VALUES('14', '12345678920', 7500.00);
INSERT INTO QUOTATION VALUES('15', '12345678914', 20000.00);
INSERT INTO QUOTATION VALUES('15', '12345678915', 22500.00);
INSERT INTO QUOTATION VALUES('16', '12345678919', 12000.00);
INSERT INTO QUOTATION VALUES('16', '12345678920', 13000.00);
INSERT INTO QUOTATION VALUES('17', '12345678919', 33000.00);
INSERT INTO QUOTATION VALUES('17', '12345678920', 31500.00);
INSERT INTO QUOTATION VALUES('18', '12345678919', 7800.00);
INSERT INTO QUOTATION VALUES('19', '12345678912', 2800.00);
INSERT INTO QUOTATION VALUES('20', '12345678928', 4500.00);


UPDATE JOB SET SelectedBusinessABNNumber = '12345678923' WHERE JobID = 1;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678925' WHERE JobID = 3;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678926' WHERE JobID = 7;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678923' WHERE JobID = 8;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678927' WHERE JobID = 10;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678921' WHERE JobID = 12;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678916' WHERE JobID = 13;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678919' WHERE JobID = 14;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678914' WHERE JobID = 15;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678920' WHERE JobID = 16;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678920' WHERE JobID = 17;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678919' WHERE JobID = 18;
UPDATE JOB SET SelectedBusinessABNNumber = '12345678912' WHERE JobID = 19;

--BUSINESS_INDUSTRY (ABNNumber, IndustryID)
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678911', '4');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678912', '1');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678913', '2');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678914', '5');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678915', '5');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678916', '6');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678917', '6');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678918', '7');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678919', '8');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678920', '8');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678921', '6');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678922', '9');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678923', '4');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678924', '1');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678925', '2');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678926', '1');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678927', '5');
INSERT INTO BUSINESS_INDUSTRY VALUES('12345678928', '9');

----INVOICE (InvoiceNo, Amount, JobID)
INSERT INTO INVOICE VALUES('1', 100, '1');
INSERT INTO INVOICE VALUES('2', 280, '3');
INSERT INTO INVOICE VALUES('3', 100, '7');
INSERT INTO INVOICE VALUES('4', 400, '8');
INSERT INTO INVOICE VALUES('5', 700, '10');
INSERT INTO INVOICE VALUES('6', 1000, '12');
INSERT INTO INVOICE VALUES('7', 2000, '12');
INSERT INTO INVOICE VALUES('8', 5000, '13');
INSERT INTO INVOICE VALUES('9', 1500, '14');
INSERT INTO INVOICE VALUES('10', 2000, '15');
INSERT INTO INVOICE VALUES('11', 3000, '15');
INSERT INTO INVOICE VALUES('12', 1000, '16');
INSERT INTO INVOICE VALUES('13', 3000, '16');
INSERT INTO INVOICE VALUES('14', 4800, '17');
INSERT INTO INVOICE VALUES('15', 800, '19');

--SEMINAR (SeminarID, SeminarTitle, SeminarDataTime, SeminarVenue)
INSERT INTO SEMINAR VALUES('1', 'Career Development for Construction Workers.', TO_DATE('12-AUG-2020 10:00', 'DD-MON-YYYY HH24:MI'), 'Convention Hall, Bundoora, 3083');
INSERT INTO SEMINAR VALUES('2', 'Ray White Career Seminar for Construction Workers', TO_DATE('28-SEP-2020 11:00', 'DD-MON-YYYY HH24:MI'), 'Convention Hall, Bundoora, 3083');
INSERT INTO SEMINAR VALUES('3', 'Career Fair for Plumbing Workers', TO_DATE('05-AUG-2020 14:00', 'DD-MON-YYYY HH24:MI'), 'Local Counsil Office, Epping, 3076');
INSERT INTO SEMINAR VALUES('4', 'Cleaners Counsil Seminar', TO_DATE('15-SEP-2020 13:00', 'DD-MON-YYYY HH24:MI'), 'CLT, Melbourne Polytechnic, Preston, 3072');

--SEMINAR_ATTENDIES (EliteMemberID, SeminarID)
INSERT INTO SEMINAR_ATTENDIES VALUES('12','1');
INSERT INTO SEMINAR_ATTENDIES VALUES('12','2');
INSERT INTO SEMINAR_ATTENDIES VALUES('11','3');
INSERT INTO SEMINAR_ATTENDIES VALUES('6','3');
INSERT INTO SEMINAR_ATTENDIES VALUES('5','4');
INSERT INTO SEMINAR_ATTENDIES VALUES('13','4');
INSERT INTO SEMINAR_ATTENDIES VALUES('8','4');

anyone may use this database for further practise. Thank you This is the structure of the Table

CLIENT (ClientNo, ClientName, ClientAddress, ClientPhone, ClientEmail) CORPORATE_CLIENT (ClientNo, CorporationName, BusinessAddress) INDIVIDUAL_CLIENT (ClientNo, PropertyOwner) BUSINESS (ABNNumber, BusinessName, ContactName, ContactNumber, ContactEmail, BusinessAddress, BusinessPostcode) FREELANCER_BUSINESS (ABNNumber, EliteMemberID) CORPORATE_BUSINESS (ABNNumber) JOB (JobID, JobDescription, UrgencyLevel, JobAddress, Postcode, SelectedBusinessABNNumber, IndustryID) CONTRACT_JOB (JobID, StartDate, EndDate, CorporateClientNo) CASUAL_JOB (JobID, IndividualClientNo) INDUSTRY (IndustryID, IndustryTitle, UnionID) TRADE_UNION (UnionID, UnionTitle, UnionContactName, UnionContactNumber, UnionEmail, UnionAddress, EliteMemberID) SUBURB (Postcode, SuburbName) INVOICE (InvoiceNo, Amount, JobID) SEMINAR (SeminarID, SeminarTitle, SeminarDataTime, SeminarVenue) QUOTATION (JobID, ABNNumber, QuoteAmount) BUSINESS_INDUSTRY (ABNNumber, IndustryID) ADJACENT_SUBURB (PostCode, AdjacentPostCode) ELITE_MEMBER (EliteMemberID) SEMINAR_ATTENDIES (EliteMemberID, SeminarID)

Hope you guys find it helpful.

Please read https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks — P.Salmon, Oct 11 '20 at 09:12
Mysql and oracle are two different database products. Removed the conflicting product tags. Pls add the one back that you actually use. — Shadow, Oct 11 '20 at 09:13
@P.Salmon does this help? https://stackoverflow.com/questions/4217293/what-does-a-sign-mean-in-an-oracle-sql-where-clause — Abra, Oct 11 '20 at 09:18
and what are you trying to do? In your question I miss the intention, like "I want to list employees and get a sum of the invoices of casual jobs and a sum of invoices of their contract jobs". — EasterBunnyBugSmasher, Oct 11 '20 at 10:03

EasterBunnyBugSmasher · Accepted Answer · 2020-10-11T11:00:36.823

I formatted your code so it's readable. And by indenting it, you can see where the problem is. It's in line 6/7 in the "FROM" part. Oracle can't really figure out what you're trying to do with that statement and when the parser reaches the end of the query at "(+)", he tells you that there is something missing/awkward. That's why he tells you it's the "(+)". But the problem is earlier. It's the way your build your "FROM" part. It's a bit confusing, so here is just a pointers:

when using "union", you don't give an alias to each of the two queries. The result set of both queries is "unioned", so you can't reference either of them in the "WHERE" part.

You should rewrite your query and instead of joining Client with a Union, you should Union two separate queries: one joining Client with casual_jobs, one joining Client with contrac_job. Following example is done without testing it in an SQL tool:

SELECT
    c.clientNo,
    c.clientName,
    nvl(sum(i.amount),0) as totalExpenses
FROM
    client c,
    casual_job ca,
    invoice i
WHERE
    c.clientNo = ca.individualClientNo
  AND
    i.jobId = ca.jobId
GROUP BY
    c.clientNo,
    c.clientName
UNION
SELECT
    c.clientNo,
    c.clientName,
    nvl(sum(i.amount),0) as totalExpenses
FROM
    client c,
    contract_job co,
    invoice i
WHERE
    c.clientNo = co.corporateClientNo
  AND
    i.jobId = co.jobId
GROUP BY
    c.clientNo,
    c.clientName
;

OOps I'm Sorry, cos I tried to add table structure. What I am trying to do is that Client have 2 child classes "CASUAL_JOB and CONTRACT_JOB" but these 2 classes have Client No (primary key of Client Table) under different Heading. So my target is to find total cost (info in Invoice Table) which is integrated with JobID (present in Invoice, Contract_Job, Contract_job Tables) and retrieve Client Info from Client Table. — Jason, Oct 11 '20 at 10:21

score 0 · Answer 2 · answered Oct 11 '20 at 12:24

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

Your query should look like this:

SELECT C.ClientNo, C.ClientName, 
       COALESCE(TotalExpenses, 0)
FROM CLIENT C LEFT JOIN
     ((SELECT C.IndividualClientNo as ClientNo, SUM(I.Amount) as TotalExpenses
       FROM CASUAL_JOB C JOIN
            INVOICE I
            ON I.JobID = C.JobID
       GROUP BY C.IndividualClientNo
      ) UNION ALL
      (SELECT C.CorporateClientNo as ClientNo, SUM(I.Amount) as TotalExpenses
       FROM CONTRACT_JOB C JOIN
            INVOICE I
            ON I.JobID = C.JobID
       GROUP BY CA.CorporateClientNo
      )
     ) cj
    ON c.ClientNo = cj.ClientNo
GROUP BY C.ClientNo, C.ClientName;

Note the other changes to the query as well:

This is an aggregation query so it has a GROUP BY.
The UNION is now UNION ALL. After all, you don't want to eliminate duplicats.
The JOIN to client is unnecessary in the subquery.

SQL Command not properly ended where I am trying to Use UNION function

2 Answers2