Recent

Author Topic: SQL UNION problem  (Read 1462 times)

daveinhull

  • Full Member
  • ***
  • Posts: 249
  • 1 divided by nothing must still be 1!
SQL UNION problem
« on: December 16, 2018, 07:39:28 pm »
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]
  1. SELECT Airport.ID, Airport.APCity, Airport.APName, Country.Country, Airport.APCode, Airport.APLat, Airport.APLong
  2. FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
  3. UNION SELECT 0,"Add new",0,0,0,0,0 FROM Airport
  4. ORDER BY Country.Country, Airport.APCity;

But When I use it from in Lazars SQL as
Code: SQL  [Select]
  1. SELECT * FROM <query above>
I get an error about there being too few parameters.

Can anyone see where I'm going wrong

Thanks
Dave
« Last Edit: December 31, 2018, 01:45:03 am by daveinhull »
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

BSaidus

  • Sr. Member
  • ****
  • Posts: 276
  • lazarus 1.8.4 Win8.1 / cross FreeBSD
Re: SQL UNION problem
« Reply #1 on: December 16, 2018, 08:07:11 pm »
try this


Code: SQL  [Select]
  1. SELECT * FROM (
  2.     SELECT  Airport.ID,  Airport.APCity,  Airport.APName,  Country.Country,  Airport.APCode,  Airport.APLat,  Airport.APLong
  3.         FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
  4.     UNION SELECT 0 ID ,"Add new" APCity,0 APName,0 Country ,0 APCode ,0 APLat ,0 APLong FROM Airport
  5. )ORDER BY Country, APCity;
  6.  
« Last Edit: December 16, 2018, 08:24:33 pm by BSaidus »
lazarus 1.8.4 Win8.1 / cross FreeBSD
dhukmucmur vernadh!

daveinhull

  • Full Member
  • ***
  • Posts: 249
  • 1 divided by nothing must still be 1!
Re: SQL UNION problem
« Reply #2 on: December 16, 2018, 10:28:45 pm »
Hi BSaidus, Thanks, but try and got an error

Syntax error (missing operator) in query expression '0 iD'.

Thoughts?
Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

daveinhull

  • Full Member
  • ***
  • Posts: 249
  • 1 divided by nothing must still be 1!
Re: SQL UNION problem
« Reply #3 on: December 16, 2018, 10:33:30 pm »
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]
  1. SELECT *
  2. FROM (SELECT  Airport.ID,  Airport.APCity,  Airport.APName,  Country.Country,  Airport.APCode,  Airport.APLat,  Airport.APLong
  3.         FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
  4.     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
  5. )  AS [%$##@_Alias]
  6. 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
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

daveinhull

  • Full Member
  • ***
  • Posts: 249
  • 1 divided by nothing must still be 1!
Re: SQL UNION problem
« Reply #4 on: December 16, 2018, 10:41:40 pm »
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]
  1. SELECT Airport.ID, Airport.APCity, Airport.APName, Country.Country, Airport.APCode, Airport.APLat, Airport.APLong
  2. FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
  3. UNION SELECT 0 AS ID, 'Add new' AS APCity,'' AS APName, '' AS Country, '' AS APCode, '' AS Lat, '' AS PLong
  4. FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
  5. ORDER BY Country.Country, Airport.APCity;
  6.  

It worked, not so elegant, but it might help someone.

Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

dsiders

  • Full Member
  • ***
  • Posts: 208
Re: SQL UNION problem
« Reply #5 on: December 16, 2018, 11:22:17 pm »
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]
  1. SELECT Airport.ID, Airport.APCity, Airport.APName, Country.Country, Airport.APCode, Airport.APLat, Airport.APLong
  2. FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
  3. UNION SELECT 0 AS ID, 'Add new' AS APCity,'' AS APName, '' AS Country, '' AS APCode, '' AS Lat, '' AS PLong
  4. FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
  5. ORDER BY Country.Country, Airport.APCity;
  6.  

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?
Lazarus 2.0.2 / FPC 3.0.4 / Windows 8.1 64-bit

daveinhull

  • Full Member
  • ***
  • Posts: 249
  • 1 divided by nothing must still be 1!
Re: SQL UNION problem
« Reply #6 on: December 16, 2018, 11:52:34 pm »
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
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

Zvoni

  • Full Member
  • ***
  • Posts: 197
Re: SQL UNION problem
« Reply #7 on: December 17, 2018, 07:57:36 am »
A bit of nitpicking:
What's that supposed to do?
Code: SQL  [Select]
  1. INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
Same condition twice with an AND?
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

daveinhull

  • Full Member
  • ***
  • Posts: 249
  • 1 divided by nothing must still be 1!
Re: SQL UNION problem
« Reply #8 on: December 17, 2018, 11:03:11 am »
Good point, didn't see that as I initially created the SQL using MS Access designer, guess I can remove that!!!
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

madref

  • Hero Member
  • *****
  • Posts: 661
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: SQL UNION problem
« Reply #9 on: December 27, 2018, 01:36:00 pm »
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]
  1. SELECT Airport.ID, Airport.APCity, Airport.APName, Country.Country, Airport.APCode, Airport.APLat, Airport.APLong
  2. FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
  3. UNION SELECT 0,"Add new",0,0,0,0,0
  4. ORDER BY Country.Country, Airport.APCity;


Hope it helped
« Last Edit: December 27, 2018, 01:43:12 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.2 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

sfeinst

  • Full Member
  • ***
  • Posts: 184
Re: SQL UNION problem
« Reply #10 on: December 27, 2018, 03:58:00 pm »
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]
  1. SELECT Airport.ID, Airport.APCity, Airport.APName, Country.Country, Airport.APCode, Airport.APLat, Airport.APLong
  2. FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
  3. UNION SELECT 0 AS ID, 'Add new' AS APCity,'' AS APName, '' AS Country, '' AS APCode, '' AS Lat, '' AS PLong
  4. FROM Country INNER JOIN Airport ON (Country.ID = Airport.APCountry) AND (Country.ID = Airport.APCountry)
  5. ORDER BY Country.Country, Airport.APCity;
  6.  

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

  • Full Member
  • ***
  • Posts: 249
  • 1 divided by nothing must still be 1!
Re: SQL UNION problem
« Reply #11 on: December 31, 2018, 01:44:57 am »
Hi sfeinst,

Yes I appreciate this! And sorted that bit for the use from Pascal to Acess. Thanks Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64