Inserting in SQL with specific select list-CodePudding

Hi I am trying to insert a row that looks similar to this table

To insert the table I use this code

Insert Into Fixture_Data (Fixture_NAME, Date_Logged,  Calendar_Week, Calendar_Year)

    Select ACTIVE_FIXTURES.Fixture_NAME,
   Fixture_Rows_For_Year.Start_Date as Date_Logged,
   
   Fixture_Rows_For_Year.Calendar_Week,
   Fixture_Rows_For_Year.Calendar_Year    

From ACTIVE_FIXTURES

Cross Join Fixture_Rows_For_Year;

However when I want to insert this for one row specifically for one fixture name I get an error. Fixture name I want to insert for comes from another table where I keep a list of all the active fixtures but I get a SQL command not ended properly or not all variables properly bound error depending on where I put my where statement. Here is the error code:

Insert Into Fixture_Data (Fixture_NAME, Date_Logged,  Calendar_Week, Calendar_Year)

    Select ACTIVE_FIXTURES.Fixture_NAME,
   Fixture_Rows_For_Year.Start_Date as Date_Logged,
   
   Fixture_Rows_For_Year.Calendar_Week,
   Fixture_Rows_For_Year.Calendar_Year    

From ACTIVE_FIXTURES
Cross Join Fixture_Rows_For_Year
where Fixture_Name =: P6_NEW;

Any help is greatly appreciated.

CodePudding user response：

P6_NEW is an item located on Page 6 (you could have named it a little bit smarter, "new" isn't very descriptive).

When you reference page items, you precede their names with a colon (:) sign. It means exactly that - precede item name. No spaces in between.

No :     where   Fixture_Name =: P6_NEW

Yes:     where a.fixture_name = :P6_NEW
                              ----
                         note the difference

Also, get used to use table aliases; they make code easier to read.

insert into fixture_data 
  (fixture_name, 
   date_logged,  
   calendar_week, 
   calendar_year)
select a.fixture_name,
       f.start_date as date_logged,
       f.calendar_week,
       f.calendar_year    
from active_fixtures a cross join fixture_rows_for_year f
where a.fixture_name = :P6_NEW;