Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 23, 2024

Need Urget MySQL help. I am getting an error on my insert function but it doesnt show in the syntax. Here is my code. any

Need Urget MySQL help. I am getting an error on my insert function but it doesnt show in the syntax. Here is my code. any help would be appreciated.

SET @MOVESdefaultdb = (SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME LIKE 'movesdb%');

FLUSH TABLES;

SET @startYear = 2020;

SET @endYear = 2050;

SET @tableName = CONCAT('MY',@startYear,'_LifetimeEmissionFactors_totalexhaust'); -- |

SET @c = CONCAT('DROP TABLE IF EXISTS ',@tableName,';');

PREPARE stmt FROM @c;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET @c = CONCAT('CREATE TABLE `',@tableName,'` (

`modelYearID` smallint(5) unsigned default NULL,

`vehicleType` char(50) default NULL,

`pollutant` char(15) default NULL,

`',@startYear,'` double default NULL,

`',@startYear + 1,'` double default NULL,

`',@startYear + 2,'` double default NULL,

`',@startYear + 3,'` double default NULL,

`',@startYear + 4,'` double default NULL,

`',@startYear + 5,'` double default NULL,

`',@startYear + 6,'` double default NULL,

`',@startYear + 7,'` double default NULL,

`',@startYear + 8,'` double default NULL,

`',@startYear + 9,'` double default NULL,

`',@startYear + 10,'` double default NULL,

`',@startYear + 11,'` double default NULL,

`',@startYear + 12,'` double default NULL,

`',@startYear + 13,'` double default NULL,

`',@startYear + 14,'` double default NULL,

`',@startYear + 15,'` double default NULL,

`',@startYear + 16,'` double default NULL,

`',@startYear + 17,'` double default NULL,

`',@startYear + 18,'` double default NULL,

`',@startYear + 19,'` double default NULL,

`',@startYear + 20,'` double default NULL,

`',@startYear + 21,'` double default NULL,

`',@startYear + 22,'` double default NULL,

`',@startYear + 23,'` double default NULL,

`',@startYear + 24,'` double default NULL,

`',@startYear + 25,'` double default NULL,

`',@startYear + 26,'` double default NULL,

`',@startYear + 27,'` double default NULL,

`',@startYear + 28,'` double default NULL,

`',@startYear + 29,'` double default NULL,

`',@startYear + 30,'` double default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;'

);

-- We prepare a statement for execution from the concatenated string defined previously.

PREPARE stmt FROM @c;

-- Executes the statement

EXECUTE stmt;

-- Releases statement

DEALLOCATE PREPARE stmt;

-- INSERT INTO table command does not need to specify column names when inserting into ALL column names.

SET @c = CONCAT('INSERT INTO ',@tableName, (

SELECT subq.modelYearID, subq.vehicleType, subq.pollutant,

ROUND(subq.year1, 3), ROUND(subq.year2, 3), ROUND(subq.year3, 3), ROUND(subq.year4, 3),

ROUND(subq.year5, 3), ROUND(subq.year6, 3), ROUND(subq.year7, 3), ROUND(subq.year8, 3),

ROUND(subq.year9, 3), ROUND(subq.year10, 3), ROUND(subq.year11, 3), ROUND(subq.year12, 3),

ROUND(subq.year13, 3), ROUND(subq.year14, 3), ROUND(subq.year15, 3), ROUND(subq.year16, 3),

ROUND(subq.year17, 3), ROUND(subq.year18, 3), ROUND(subq.year19, 3), ROUND(subq.year20, 3),

ROUND(subq.year21, 3), ROUND(subq.year22, 3), ROUND(subq.year23, 3), ROUND(subq.year24, 3),

ROUND(subq.year25, 3), ROUND(subq.year26, 3), ROUND(subq.year27, 3), ROUND(subq.year28, 3),

ROUND(subq.year29, 3), ROUND(subq.year30, 3), ROUND(subq.year31, 3)

FROM(

SELECT a.modelYearID, a.fuelTypeID, a.sourceTypeID, a.pollutantID,

concat_ws(", ", sourceTypeName, fuelTypeDesc) vehicleType,

CASE WHEN pollutantName = "Carbon Monoxide (CO)" THEN "CO"

WHEN pollutantName = "Oxides of Nitrogen (NOx)" THEN "NOx"

WHEN pollutantName = "Primary Exhaust PM10 - Total" THEN "PM10" ELSE "PM2.5" END AS pollutant,

a.year1, a.year2, a.year3, a.year4, a.year5, a.year6,

a.year7, a.year8, a.year9, a.year10, a.year11, a.year12,

a.year13, a.year14, a.year15, a.year16, a.year17, a.year18,

a.year19, a.year20, a.year21, a.year22, a.year23, a.year24,

a.year25, a.year26, a.year27, a.year28, a.year29, a.year30,

a.year31

FROM(SELECT modelYearID, processType, fuelTypeID, sourceTypeID, pollutantID,

SUM(IF(yearID = @startYear, emissionRate, 0)) AS year1,

SUM(IF(yearID = @startYear + 1, emissionRate, 0)) AS year2,

SUM(IF(yearID = @startYear + 2, emissionRate, 0)) AS year3,

SUM(IF(yearID = @startYear + 3, emissionRate, 0)) AS year4,

SUM(IF(yearID = @startYear + 4, emissionRate, 0)) AS year5,

SUM(IF(yearID = @startYear + 5, emissionRate, 0)) AS year6,

SUM(IF(yearID = @startYear + 6, emissionRate, 0)) AS year7,

SUM(IF(yearID = @startYear + 7, emissionRate, 0)) AS year8,

SUM(IF(yearID = @startYear + 8, emissionRate, 0)) AS year9,

SUM(IF(yearID = @startYear + 9, emissionRate, 0)) AS year10,

SUM(IF(yearID = @startYear + 10, emissionRate, 0)) AS year11,

SUM(IF(yearID = @startYear + 11, emissionRate, 0)) AS year12,

SUM(IF(yearID = @startYear + 12, emissionRate, 0)) AS year13,

SUM(IF(yearID = @startYear + 13, emissionRate, 0)) AS year14,

SUM(IF(yearID = @startYear + 14, emissionRate, 0)) AS year15,

SUM(IF(yearID = @startYear + 15, emissionRate, 0)) AS year16,

SUM(IF(yearID = @startYear + 16, emissionRate, 0)) AS year17,

SUM(IF(yearID = @startYear + 17, emissionRate, 0)) AS year18,

SUM(IF(yearID = @startYear + 18, emissionRate, 0)) AS year19,

SUM(IF(yearID = @startYear + 19, emissionRate, 0)) AS year20,

SUM(IF(yearID = @startYear + 20, emissionRate, 0)) AS year21,

SUM(IF(yearID = @startYear + 21, emissionRate, 0)) AS year22,

SUM(IF(yearID = @startYear + 22, emissionRate, 0)) AS year23,

SUM(IF(yearID = @startYear + 23, emissionRate, 0)) AS year24,

SUM(IF(yearID = @startYear + 24, emissionRate, 0)) AS year25,

SUM(IF(yearID = @startYear + 25, emissionRate, 0)) AS year26,

SUM(IF(yearID = @startYear + 26, emissionRate, 0)) AS year27,

SUM(IF(yearID = @startYear + 27, emissionRate, 0)) AS year28,

SUM(IF(yearID = @startYear + 28, emissionRate, 0)) AS year29,

SUM(IF(yearID = @startYear + 29, emissionRate, 0)) AS year30,

SUM(IF(yearID = @startYear + 30, emissionRate, 0)) AS year31

FROM emissionratesbyprocesstypenationalscale_totalexhaust

-- modelYearID = @startYear to select emissions from the same model year vehicle as it ages

WHERE modelYearID = @startYear

GROUP BY fuelTypeID, sourceTypeID, pollutantID, processType) a,

movesdb20201105.pollutant c, movesdb20201105.sourceusetype d,

movesdb20201105.fueltype e

WHERE a.pollutantID = c.pollutantID

AND a.sourceTypeID = d.sourceTypeID AND a.fuelTypeID = e.fuelTypeID

AND processType = "Exhaust"

AND pollutantName IN ("Carbon Monoxide (CO)", "Oxides of Nitrogen (NOx)",

"Primary Exhaust PM10 - Total", "Primary Exhaust PM2.5 - Total")

GROUP BY vehicleType, pollutant

UNION