SQL UNION problem lazarus / 16-Databases ---------------------------------------------------------------------- daveinhull · 2018-12-16 18:39 Hi, I'm trying to add a static text line to a drop down query with Access as the DB. I'm using this SQL which works fine in Access Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT Airport.ID, Airport.APCity, Airport.APName, Country.Country, Airport.APCode, Airport.APLat, Airport.APLongFROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)UNION SELECT 0,"Add new",0,0,0,0,0 FROM AirportORDER BY Country.Country, Airport.APCity; But When I use it from in Lazars SQL as Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT * FROM I get an error about there being too few parameters. Can anyone see where I'm going wrong Thanks Dave ---------------------------------------------------------------------- BSaidus · 2018-12-16 19:07 try this Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT * FROM (    SELECT  Airport.ID,  Airport.APCity,  Airport.APName,  Country.Country,  Airport.APCode,  Airport.APLat,  Airport.APLong      FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)    UNION SELECT 0 ID ,"Add new" APCity,0 APName,0 Country ,0 APCode ,0 APLat ,0 APLong FROM Airport)ORDER BY Country, APCity; ---------------------------------------------------------------------- daveinhull · 2018-12-16 21:28 Hi BSaidus, Thanks, but try and got an error Syntax error (missing operator) in query expression '0 iD'. Thoughts? Dave ---------------------------------------------------------------------- daveinhull · 2018-12-16 21:33 Hi, Ok, so I changed it to add 'as' in between the value and the label, so 0 as ID, "Add new" as APCity etc. This worked, and Access changed the SQL to Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT *FROM (SELECT  Airport.ID,  Airport.APCity,  Airport.APName,  Country.Country,  Airport.APCode,  Airport.APLat,  Airport.APLong      FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)    UNION SELECT 0 AS ID ,"Add new" AS APCity,0 AS APName,0 AS Country ,0 AS APCode ,0 AS APLat ,0 AS APLong FROM Airport)  AS [%$##@_Alias]ORDER BY Country, APCity; But when I try accessing it from my Pascal SQL statement it again came back with an error saying too few parameters, expected 1. Not sure what to do now. Dave ---------------------------------------------------------------------- daveinhull · 2018-12-16 21:41 Hi again, OK, so I had another think and tried simply duplicating the two selects but setting the second to what I wanted as in Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT Airport.ID, Airport.APCity, Airport.APName, Country.Country, Airport.APCode, Airport.APLat, Airport.APLongFROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)UNION SELECT 0 AS ID, 'Add new' AS APCity,'' AS APName, '' AS Country, '' AS APCode, '' AS Lat, '' AS PLongFROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)ORDER BY Country.Country, Airport.APCity;  It worked, not so elegant, but it might help someone. Dave ---------------------------------------------------------------------- dsiders · 2018-12-16 22:22 Quote from: daveinhull on December 16, 2018, 10:41:40 pmOK, so I had another think and tried simply duplicating the two selects but setting the second to what I wanted as in Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT Airport.ID, Airport.APCity, Airport.APName, Country.Country, Airport.APCode, Airport.APLat, Airport.APLongFROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)UNION SELECT 0 AS ID, 'Add new' AS APCity,'' AS APName, '' AS Country, '' AS APCode, '' AS Lat, '' AS PLongFROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)ORDER BY Country.Country, Airport.APCity;  It worked, not so elegant, but it might help someone. Without knowing the definition of the Airport table, we're all guessing. So I'll guess that the original select statement for "Add New" was casting one of its values to a data types that did not match the schema for Airport. BTW the above statement does not have the same column alias for APLat and APLong. Typo? ---------------------------------------------------------------------- daveinhull · 2018-12-16 22:52 Yes, I should have given a few more details about the Airport table and yes it was a typo. In case there is a neater answer, the Airport table is: ID: Autonumber APCity: Text APName: Text Country: Number (pointer to a country table) APCode: Text APLat: Number (Double) APLong: Number (Double) The Coutry table has just got an ID (autonumber) and a Text field I realize the I'm setting APLat and APLong to "" in the second SELECT, but this is because the second SELECT is just adding the option to add a new Airport and I don't want any other information presented, including 0.0's; it seems to work. Thanks Dave ---------------------------------------------------------------------- Zvoni · 2018-12-17 06:57 A bit of nitpicking: What's that supposed to do? Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)Same condition twice with an AND? ---------------------------------------------------------------------- daveinhull · 2018-12-17 10:03 Good point, didn't see that as I initially created the SQL using MS Access designer, guess I can remove that!!! ---------------------------------------------------------------------- madref · 2018-12-27 12:36 Leave the 'FROM Airport' part away from the union then it should work. If you use a union to add a record you don't need the from part. Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT Airport.ID, Airport.APCity, Airport.APName, Country.Country, Airport.APCode, Airport.APLat, Airport.APLongFROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)UNION SELECT 0,"Add new",0,0,0,0,0 ORDER BY Country.Country, Airport.APCity; Hope it helped ---------------------------------------------------------------------- sfeinst · 2018-12-27 14:58 Quote from: daveinhull on December 16, 2018, 10:41:40 pmHi again, OK, so I had another think and tried simply duplicating the two selects but setting the second to what I wanted as in Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT Airport.ID, Airport.APCity, Airport.APName, Country.Country, Airport.APCode, Airport.APLat, Airport.APLongFROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)UNION SELECT 0 AS ID, 'Add new' AS APCity,'' AS APName, '' AS Country, '' AS APCode, '' AS Lat, '' AS PLongFROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)ORDER BY Country.Country, Airport.APCity;  It worked, not so elegant, but it might help someone. Dave You didn't say how you were using this in Pascal.  But... you will notice another difference is the use of single quotes around Add New.  Double quotes is strictly and Access thing when using strings.  Standard SQL is single quotes.  The only way the original SQL would have had a chance to work is if it was pass-through without any interpretation by the Pascal code.  I'm guessing you are using ODBC, so you should use standard SQL. ---------------------------------------------------------------------- daveinhull · 2018-12-31 00:44 Hi sfeinst, Yes I appreciate this! And sorted that bit for the use from Pascal to Acess. Thanks Dave