COUNT in SQL Query

[Danny Sparks-Cousins]

Hi All,

I am trying to do some quick checking of objects using an SQL Query within ArchiCAD. I can successfully query down to custom GDL variable names and values (CASTing parameters.value from type GDLVARIANT to type CHAR is working now), but I would like to just get a COUNT of the objects where the conditions are met.

This code works;

select flc.parameters.name, flc.parameters.value from flat(objects,parameters) as flc
where (library_part_name='Built-in Cooktops 15')
and (flc.parameters.variable_name = 'CooktopType' )
and (CAST ( flc.parameters.value AS CHAR ) ='Type 7' )

but when I try to do a SELECT COUNT, the interpreter errors. Is there no way to get a COUNT using the inbuilt SQL interpreter?

Thanks,
Danny

[Anonymous]

Don't know. You're using AC17? Great news about CAST!! How did you discover this? Without that, the ability to work with additional params is hamstrung, although a clever work around was posted some years ago in these forums.

COUNT still seems to be "function undefined" through the user interface. Clueless why. It's been missing since forever, but requested. Other standard SQL commands have been requested too.

[Danny Sparks-Cousins]

I have tested CASTing the type in V15, V16 & V17 and they all work successfully. I often try to make ArchiCAD work in ways that aren't documented - and sometimes have a little success. No COUNT thought

[Anonymous]

Danny wrote:
I have tested CASTing the type in V15, V16 & V17 and they all work successfully. I often try to make ArchiCAD work in ways that aren't documented - and sometimes have a little success. No COUNT thought :cry:

Well thank you so much for posting the info about CAST. You have spurred me on to re-investigate old territory.

Over the years I tried variations on SELECT through the AC interface as I was learning SQL. DISTINCT was one of the few that worked. "TOP nnn" does not, which one would have thought would be a tool critical for beginners in order to keep "inelegant queries" from running wild and giving the SQL interface a bad rap. I've certainly done a few of them.

While GSSQL may be a subset of a standard version (a good thing!) at the UI it seems to be a very small subset. With the advent of robust Schedule capability, I suppose the impetus for pushing the SQL interface forward was lessened. However, there are still many queries that are important during the transition between Design-Construction-FM that the Schedule cannot do.

Bringing in an additional parameter a second and third time in the filter section of the Schedule is one example. (One can construct work-arounds, of course.) Querying for all and only objects in a zone category is another. Yes, one can get that info, but it's clunky: one also has to accept all sorts of non-relevant info in the output, then post-process it elsewhere. All I want to know is: 'Show me the equipment in the Accounting Dept,' or 'show me everyone in Engineering'.

Anyway, great news about CAST! Thanks again.

[Karl Ottenstein]

I searched my computer for a reference to Graphisoft SQL and found the attached PDF which it looks like I converted from an HTML file some years ago... 2005 maybe? Don't see a nondisclosure, so assume this is pubic info.

No COUNT, but the CAST is described. Also an interesting image converter that I hadn't tried.

[Anonymous]

Karl wrote:
I searched my computer for a reference to Graphisoft SQL and found the attached PDF which it looks like I converted from an HTML file some years ago... 2005 maybe? Don't see a nondisclosure, so assume this is pubic info.

No COUNT, but the CAST is described. Also an interesting image converter that I hadn't tried.

Thanks, Karl. I think I've seen this and forgotten it somewhat. Great reminder. The data type conversion matrix looks like something from the API documentation. Note that GDLVARIANT is shown as a type that cannot be converted. But this is exactly what I and Danny Sparks-Cousins used the CAST for: to convert GDLVARIANT to CHAR so that the VALUE of an additional parameter, (say, the value '=Xxyz 123'), can be queried. Without CAST, I get the error that 'Xxyz 123' is not the same type as GDLVARIANT.

I guess the conversion matrix is wrong in this regard, or else my understanding of CAST is faulty.

The CAST and FLAT combination has an unexpected result -- i.e. at odds with the Schedule'd version of a similar query -- which I will post as time permits.

[Danny Sparks-Cousins]

Thanks for the document Karl, great to have some more reference.

The Type Conversion section indicates that type GDLVARIANT is not able to be cast, but in my example I am CASTing objects.parameters.value (which is type GDLVARIANT) to type CHAR and it is working successfully ...

[Karl Ottenstein]

Good to clarify that. Perhaps GS has added new features, or the document has errors as Didrik suggests... the file is close to 8 years old I think...

When I played with all of this years ago... when I was a Windows/PC guy (as it only works there) ... I used the ODBC connection to Microsoft Access where the tables become linked and then any normal query can be used, including COUNT. And, of course, visual queries can be dragged and dropped without using SQL at all.

Too many years now - maybe Didrik remembers - but I cannot recall if ODBC exposes 100% of the data available within the AC SQL window.

[Laszlo Nagy]

The Help Center at this page

http://helpcenter.graphisoft.com/guides/archicad-calculation-guide/sql-in-archicad/

says:

Documentation is available for GRAPHISOFT SQL. It is included in the GRAPHISOFT ODBC (Open Database Connectivity) documentation which can be downloaded from the Documentation section of the GRAPHISOFT Developer Center at the following web page: http://www.GRAPHISOFT.com/support/developer/.

So you will probably be able to find the most up-to-date SQL Reference in the ODBC Docs if you download it.

[Anonymous]

Karl wrote:
....

Too many years now - maybe Didrik remembers - but I cannot recall if ODBC exposes 100% of the data available within the AC SQL window.

I don't recall very much. I'll check the document cited by Laszlo. My recollection is that the SQL doc used to be the one cited for AC14 ODBC, referring users back to the ODBC Doc for AC13 which contained the SQL info. I asked along the way -- of course! -- why ODBC wasn't brought forward with current doc names, contents, etc.

After you were here showing me various Access/SQL/ACDB things, in subsequent efforts we found that the ODBC required great care to use during AC13, AC14, if one were trying to go the 64-bit route under pre-Windows 7 (Vista & before). It entailed making sure that MS Office 64-bit was installed in order to get Excel 64-bit installed. It was a daisy chain of dependencies which MS Tech support was aware of and recommended against, -- but was documented on various forums -- since many 3rd party apps and MS apps such as OneNote still required 32-bit environment.

In intervening times, ironically the documentation for creating DSNs -- and GS documentation generally -- improved around the time the robust Scheduling capability appeared, so the impetus to SQL and ODBC was relieved. Better documentation, too late.

In more recent times, however, some clients are expecting the SQL interface to be full blown SQL92 and want to tap directly into ACDB using technical skills (i.e., SQL) they already possess. (UPDATE and CREATE at the UI would be helpful for them too, but we all appreciate the support issues that introduces... I guess ...)

GSHU graciously offered me limited tech support on ODBC issues but the installations which worked were eventually destroyed by various system updates, new computers with pre-installed crapware, long periods of disuse, and counter-intuitive 32- vs. 64-bit ODBC admin operation.

My specific recollection was that the ODBC interface did not support everything under the hood, that there were responses to me that stated such-and-such was an ODBC limitation. Consistent with this is that various fields shown via "Show Data Structure..." indicate field lengths much greater than shown or usable through the AC UI. One supposes therefore that fields having underlying 255 character lengths are really 255 characters in ACDB even though the user can't assign a 255 character string to them.

It would really be an improvement to have a document showing the tricks and power of SQL-to-AC.

One day, the whole issue of "should this data be IN the model, or should it be linked TO the model?" will hopefully go away, to be replaced by the question "Which datasets do you want in the model now for such-and-such use cases?"