1

I need to populate this fact table using a PL/SQL stored procedure:

CREATE TABLE SALES_FACTS
(saleDay        DATE,
vehicleCode         INT,
planID          INT,
dealerID        INT,
vehiclesSold        INT,
grossSalesAmt   NUMBER(10),
CONSTRAINT SALE_DAY_FK FOREIGN KEY (saleDay) REFERENCES TIMES(saleDay) ON DELETE CASCADE,
CONSTRAINT VEHICLE_CODE_FK FOREIGN KEY (vehicleCode) REFERENCES VEHICLES(vehicleCode) ON DELETE CASCADE,
CONSTRAINT PLAN_ID_FK FOREIGN KEY (planID) REFERENCES FINANCING_PLANS(planID) ON DELETE CASCADE,
CONSTRAINT DEALER_FK FOREIGN KEY (dealerID) REFERENCES DEALERSHIPS(dealerID) ON DELETE CASCADE,
CONSTRAINT SALES_FACTS_PK PRIMARY KEY (saleDay, vehicleCode, planID, dealerID));

I have been asked to do this by using four nested cursor loops to get every possible combination of the dimension tables' primary keys, along with the total vehicles sold and gross sales amount for each combination.

Also, if the values for vehiclesSold and grossSalesAmount are zero, then a row SHOULD NOT be inserted into the SALES_FACTS table.

Only rows for combinations of the four foreign key columns where there were some vehicles sold should be inserted.

I have created the following code that I hoped would accomplish this:

CURSOR factData IS

SELECT vehicleVin,saleDate,sf.planID,sp.dealerID
    COUNT (*) AS vehiclesSold
    SUM (s.grossSalePrice) AS grossSalesAmount
FROM SALES s, SALES_FINANCINGS sf, SALESPERSONS sp
WHERE s.saleID = sf.saleID
AND s.salespersonID = sp.salespersonID
GROUP BY vehicleVIN, saleDate, sf.planID, sp.dealerID
HAVING COUNT(*) > 0;
BEGIN
FOR record IN factData
LOOP
    INSERT INTO SALES_FACTS (saleDay,vehicleCode,planID,dealerID,vehiclesSold, grossSalesAmount
    VALUES (record.saleDate,record.vehicleVin,record.planID,record.dealerID,record.vehiclesSold,record.grossSalesAmount);
END LOOP;
END;
/

However the code executes fine, but I do not get any results when I run a 

SELECT COUNT(*) FROM SALES_FACTS;

I have created an SQL Fiddle link here http://sqlfiddle.com/#!4/9708d6/1 since the code for the tables and table population was too much to post on this question. Keep in mind that I only INSERTed about 2-3 rows of data for each table to keep the code somewhat short, however the data that has been inserted should suffice to get this working.

Please let me know where I'm going wrong and what the best way to fix it is! Thanks in advance!

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
StevenC
  • 109
  • 1
  • 20
  • 1
    Why do you need a loop? This can just be done with a insert .. select. – OldProgrammer Feb 21 '18 at 17:39
  • 1) Fix the syntax of your query in cursor (missing commas after dealerID and vehiclesSold). 2) you may remove `COUNT(*) > 0` as teh group must have at least one row. 3) **Adjust the table structure with the cursor definition**, e.g. vehicleCode is INT, but cursor returns `147258HHE91K3RT`. 4) add COMMIT – Marmite Bomber Feb 21 '18 at 17:56
  • @OldProgrammer you are correct. I just chose the loop approach. Open to doing an INSERT but not exactly clear on how to assemble it for this particular case. – StevenC Feb 21 '18 at 17:59
  • @MarmiteBomber thanks for catching those. I've been so many rounds with this that I forgot to make changes where they were needed. Made the changes, but still not working. – StevenC Feb 21 '18 at 18:09
  • @StevenC : Did you read my comment above? – Kaushik Nayak Feb 21 '18 at 18:11
  • @KaushikNayak I actually did but it didn't stick at first. My apologies. So it doesn't throw an error or anything? It just successfully compiles but doesn't work? – StevenC Feb 21 '18 at 18:17
  • The fiddle you linked to is missing a `DECLARE`, which oddly is just ignored. So the code you wrote is *not* being executed... [This version](http://sqlfiddle.com/#!4/9708d6/53) fixes that and some errors it threw up - the missing commas, and also a missing closing parenthesis - so it does seem to run the block OK in the right-hand pane. But I don't know how you resolved the data type discrepancy, so it is currently only getting to an ORA-01722: invalid number error, because of the mismatch pointed out already. – Alex Poole Feb 21 '18 at 18:24
  • 1
    @StevenC : I suggest you use livesql ( or sqlplus , sql developer if u have ) to verify PL/SQL blocks. I don't know whether it works or not in sqlfiddle, but as far as I have tried, it never works as expected. But, first fix all your syntax errors. – Kaushik Nayak Feb 21 '18 at 18:37
  • @AlexPoole I resolved by changing the vehicleCode datatype to VARCHAR for both the PK and FK. Thanks for cleaning up my code. I'll definitely fess up to the fact that it was a mess - very raw. Still isn't working though. Although it executes, it's not populating the SALES_FACTS table. – StevenC Feb 21 '18 at 18:46
  • We can't see what else you're changing. Even with that changed it errors on missing keys, because you aren't populating your vehicles or times tables. With those given nominal values from the other tables [it seems to work](http://sqlfiddle.com/#!4/6f6ca/2). So no idea what else you are doing differently. The main problem here seems to be the missing `declare`, followed by various syntax errors, then missing reference data. – Alex Poole Feb 21 '18 at 19:10
  • @AlexPoole I'm getting the error: `ERROR at line 1: ORA-02291: integrity constraint (CM420A01.VEHICLE_CODE_FK) violated - parent key not found ORA-06512: at line 15` When I run this against my DB. In my DB, the `VEHICLES` attribute `vehicleCode` is populated as a sequence, not the actual `vehicleVin`. I'm in a pickle here - I'm totally lost as to how I can make this work. Obviously querying against `vehicleVin` to populate the `vehicleCode` column isn't going to work since the `vehicleVin` and `vehicleCode` are two totally different keys....UGH! – StevenC Feb 22 '18 at 00:04

1 Answers1

0

This Ended up doing the trick. Thanks for all of the help to those who commented.

DECLARE
CURSOR sales_data
IS
 SELECT vehicleVIN, saleDate, SF.planID, SP.dealerID, 
 COUNT(*) AS vehiclesSold, SUM(S.grossSalePrice) AS grossSalesAmt
 FROM SALES S, SALES_FINANCINGS SF, SALESPERSONS SP, VEHICLES V
 WHERE S.saleID = SF.saleID AND S.vehicleVIN = V.vehicleCode AND S.salespersonID = SP.salespersonID
 GROUP BY vehicleVIN, saleDate, SF.planID, SP.dealerID
 HAVING COUNT(*) > 0;

BEGIN
FOR record IN sales_data
LOOP
    INSERT INTO SALES_FACTS (saleDay,vehicleCode,planID,dealerID,vehiclesSold, grossSalesAmt)
    VALUES (record.saleDate,record.vehicleVIN,record.planID,record.dealerID,record.vehiclesSold,record.grossSalesAmt);
END LOOP;
END;
/
StevenC
  • 109
  • 1
  • 20