SQL request for single object parameters

Joachim Suehlo · ‎2009-01-04

Is it possible to make a SQL request for a single parameter of a GDL object?
The following request gives back only the whole column of the parameters names:
"SELECT XX.PARAMETERS.NAME FROM FLAT(OBJECTS, PARAMETERS) AS XX"

Jochen Suehlo . AC12-27 . MAC OSX 14.4 . WIN11
GDL object creation: b-prisma.de

Laszlo Nagy · ‎2009-01-04

Check the section on SQL on Page 52 in the 04 AC 12 Calculation Guide.pdf file in the Documentation folder within the ArchiCAD folder.
It will give you some additional info that may help.

Loving Archicad since 1995 - Find Archicad Tips at x.com/laszlonagy
AMD Ryzen9 5900X CPU, 64 GB RAM 3600 MHz, Nvidia GTX 1060 6GB, 500 GB NVMe SSD
2x28" (2560x1440), Windows 10 PRO ENG, Ac20-Ac27

Karl Ottenstein · ‎2009-01-04

Hi Joachim,

I was going crazy trying to get this to work ... and it turns out that the ArchiCAD SQL interpretter only allows single quote for text strings... I was using double quotes for half an hour!

The attached is an example that works to display the value of the 'A' parameter for all placed instances of a particular chair:

select flc.parameters.name, flc.parameters.value  from flat(objects,parameters) as flc
where (library_part_name='Armchair 01 12') and (flc.parameters.variable_name = 'A' )

I tried using the parameter value ('value')in the where clause, but it is of type 'gdlvariant' (permitting text, integer, floating point, etc) - and the only way to get a valid comparison is to use a type coercion function ... and I could not find any that were accepted by the interpreter. If you happen to figure that out, please post.

Cheers,

Karl

One of the forum moderators
AC 27 USA and earlier • macOS Ventura 13.6.6, MacBook Pro M2 Max 12CPU/30GPU cores, 32GB

Karl Ottenstein · ‎2009-01-04

laszlonagy wrote:
Check the section on SQL on Page 52 in the 04 AC 12 Calculation Guide.pdf file in the Documentation folder within the ArchiCAD folder.
It will give you some additional info that may help.

From his example that calls the flattened table 'XX', it is likely that Joachim was working from those pages...

But, thanks, Laszlo for pointing out this reference! The 5 pages there are an excellent tutorial for the SQL feature which was introduced in ArchiCAD 8.0 with no documentation at that time. I did not realize that something had finally been written up since then...and I see these same pages in the AC 10 and AC 11 Calculation Guide. So, good news! 🙂

Cheers,
Karl

One of the forum moderators
AC 27 USA and earlier • macOS Ventura 13.6.6, MacBook Pro M2 Max 12CPU/30GPU cores, 32GB

Karl Ottenstein · ‎2009-01-04

Attached is the GS SQL reference, extracted from the AC 12 ODBC help chm as a PDF. No time to review myself just yet, but when ODBC and SQL arrived with 8.0, the syntax for each was slightly different, so may still be so. But, the pages that Laszlo pointed us to further point to the ODBC documentation for the Graphisoft SQL guide, so hopefully this will help.

(It is a little odd that the ODBC help, including this syntax, is in a Microsoft Windows CHM format help file, that is not accessible to Mac users - even though the SQL Query window does work on mac. It is only ODBC that is Windows-only.)

Cheers,
Karl

One of the forum moderators
AC 27 USA and earlier • macOS Ventura 13.6.6, MacBook Pro M2 Max 12CPU/30GPU cores, 32GB

Joachim Suehlo · ‎2009-01-04

Thanks Laszlo, Thanks Karl.
Yes, I used the Calculation Guide. And as you write it, Karl, I remember the SQL Guide, that I downloaded some years ago.
I still have acess to Windows, so it wil be no problem for me, to read it.
I hope it will help and I will post if I find out news.
Thank You again!

Jochen Suehlo . AC12-27 . MAC OSX 14.4 . WIN11
GDL object creation: b-prisma.de

Laszlo Nagy · ‎2009-01-04

Karl wrote:
laszlonagy wrote:
Check the section on SQL on Page 52 in the 04 AC 12 Calculation Guide.pdf file in the Documentation folder within the ArchiCAD folder.
It will give you some additional info that may help.
From his example that calls the flattened table 'XX', it is likely that Joachim was working from those pages...

But, thanks, Laszlo for pointing out this reference! The 5 pages there are an excellent tutorial for the SQL feature which was introduced in ArchiCAD 8.0 with no documentation at that time. I did not realize that something had finally been written up since then...and I see these same pages in the AC 10 and AC 11 Calculation Guide. So, good news! 🙂

Cheers,
Karl

I originally wrote this short introduction for Graphisoft for AC9 and it has not changed since. As stated there, there is a lot more that can be done with SQL than is described in those few pages.
But thanks for chiming in Karl, I think you are probably the one who knows the most of SQL in practice.

Loving Archicad since 1995 - Find Archicad Tips at x.com/laszlonagy
AMD Ryzen9 5900X CPU, 64 GB RAM 3600 MHz, Nvidia GTX 1060 6GB, 500 GB NVMe SSD
2x28" (2560x1440), Windows 10 PRO ENG, Ac20-Ac27

Karl Ottenstein · ‎2009-01-04

laszlonagy wrote:
I originally wrote this short introduction for Graphisoft for AC9 and it has not changed since.

I'm glad that they had you do that! (And, sorry I had not noticed it! I was so used to the Calculate Guide getting no improvements, that I stopped looking closely at it!)

Thanks!

Karl

One of the forum moderators
AC 27 USA and earlier • macOS Ventura 13.6.6, MacBook Pro M2 Max 12CPU/30GPU cores, 32GB

Anonymous · ‎2009-01-05

Karl wrote:
I tried using the parameter value ('value')in the where clause, but it is of type 'gdlvariant' (permitting text, integer, floating point, etc) - and the only way to get a valid comparison is to use a type coercion function ... and I could not find any that were accepted by the interpreter. If you happen to figure that out, please post.

ODBC manual (odbc.chm) describes GDLVARIANT as totally unconvertable (Graphisoft SQL Language Reference->Data Type Conversion chapter). May be you can use "GDL Helper Object" trick as a workaround.

Create 'SQL_Tst_1' object with 'Tst_1' variable as a parameter. Define this variable with absolutely the same type, as the type of the field you want to search for (pen number in my case). And set it to the value you want to search for. Place the object and execute this instruction:

SELECT *
FROM
flat(objects,parameters) as T_1,
flat(objects,parameters) as T_2
WHERE
(T_1.library_part_name = 'Built-in Elevation Marker')
and
(T_1.parameters.variable_name = 'AC_TextPen_1')
and
(T_2.parameters.variable_name = 'Tst_1')
and
(T_1.parameters.value = T_2.parameters.value)

It is very important to write WHERE part carefully. We have to select rows with the same GDL type of comparable parameters. Otherwise SQL reports a bug.

Karl Ottenstein · ‎2009-01-05

Nice trick, Leo. 😉 After I posted the PDF, I saw that chart with the lack of coercion functions for variants (unlike in most languages). Unfortunate to have to create an object like yours to perform the search, but great lateral thinking! 😉

Cheers,
Karl

One of the forum moderators
AC 27 USA and earlier • macOS Ventura 13.6.6, MacBook Pro M2 Max 12CPU/30GPU cores, 32GB

SQL request for single object parameters

Still looking?

Browse more topics

See latest solutions

Start a new discussion!