How to cross join two tables in sql

user_15972232 0
Viewed 89

So I need to cross join 2 tables but I cannot find a way to do so without receiving the error

ORA-01427: single-row subquery returns more than one row ORA-06512: at "SYS.DBMS_SQL", line 1721

Here is my code I am using but I am unaware where my error is coming from, I am new to sql so any help is appreciated:

INSERT INTO Valid_Safety (Area, Course_ID, Course_Name, Personnel_Number, ESA_Name, Section_Leader, Valid, Expire_Date)

Select Safety_Map.Area,
       Course_ID,
       Course_Name,
       _ESA.Personnel_Number,
       _ESA.ESA_Name,
       _ESA.Section_Leader,
    (Select
    COUNT(*) Valid
     From
    SAP_Data
    Where Personnel_Number = TX3_ESA.Personnel_Number
    AND Duplicate1 = 'Valid'
    AND Course_Num = Safety_Map.Course_Num) As Valid,
    
     (Select 
     Expire_Date
     From 
      SAP_Data
    Where Personnel_Number = _ESA.Personnel_Number
    AND Duplicate1 = 'Valid'
    AND Course_Num = Safety_Map.Course_Num) As Expire_Date
   
From Safety_Map CROSS JOIN _ESA;
oracle-apex oracle sql
2 Answers

(Select Expire_Date From SAP_Data Where Personnel_Number = _ESA.Personnel_Number AND Duplicate1 = 'Valid' AND Course_Num = Safety_Map.Course_Num)

Is returning more than one rows. If you just need top one then you can use rownum or fetch first 10 rows only

Select Safety_Map.Area,
       Course_ID,
       Course_Name,
       _ESA.Personnel_Number,
       _ESA.ESA_Name,
       _ESA.Section_Leader,
    (Select
    COUNT(*) Valid
     From
    SAP_Data
    Where Personnel_Number = TX3_ESA.Personnel_Number
    AND Duplicate1 = 'Valid'
    AND Course_Num = Safety_Map.Course_Num) As Valid,
    
     (Select 
     Expire_Date
     From 
      SAP_Data
    Where Personnel_Number = _ESA.Personnel_Number
    AND Duplicate1 = 'Valid'
    AND Course_Num = Safety_Map.Course_Num
    and rownum=1 ) As Expire_Date
   
From Safety_Map CROSS JOIN _ESA;

or you can use aggregation (max() or min()):

(Select 
     max(Expire_Date)
     From 
      SAP_Data
    Where Personnel_Number = _ESA.Personnel_Number
    AND Duplicate1 = 'Valid'
    AND Course_Num = Safety_Map.Course_Num
    and rownum=1 ) As Expire_Date
user_86516010

well , seems like subquery for Expire_Date , returns more than one row :

so the solution can be vary depends on the logic but here is one way :

...
    (
        Select max(Expire_Date)
        From SAP_Data
        Where Personnel_Number = _ESA.Personnel_Number
        AND Duplicate1 = 'Valid'
        AND Course_Num = Safety_Map.Course_Num
    ) As Expire_Date
From Safety_Map CROSS JOIN _ESA;
user_13674540
Related