D3 and mvBase

PARTNER David Knight posted 09-14-2023 06:18

Try this link for other syntax options which are SQL flavour dependent:

https://www.w3schools.com/sql/sql_top.asp

Also, do you need a semi-colon at the end?

Best wishes,

David

PARTNER Stefano Gallotta posted 09-14-2023 09:00

Hi @David Knight

Yes, you are correct re the semicolon - but I was using syntax by example to feed the ISQL statement.
I had a look at W3schools (and Quora) and tried all variations. Seems that the support of  SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses FETCH FIRST n ROWS ONLY and ROWNUM are not supported as such.

I have resorted to the conditinal option which will do something like what i wish.

I wonder whether the support of TOP LIMIT etc will be added sometime?

Thanks

Stefano

PARTNER David Knight posted 09-14-2023 09:58

Hi @Stefano Gallotta

Glad you got something to work; but I am a little confused about something:

In my experience the SQL statements are executed from the SQL client; not d3 itself. The OBDC interface exposes and performs translates in real time etc for the tables which are now inside the SQL client; so any syntax issues are the domain of the SQL client. Your comment suggests to me you think the syntax issue is with d3/Rocket?

Which client one are you using? For example, are you using MS Access? MS SQL server? MS SQL-server lite [or whatever it is called, I forget!]

Or are you writing a routine outside of d3 but never-the-less in "SQL-land" via a library perhaps in c#; etc? In which case, once again the SQL syntax is dependent on that library or version; not d3.

I realise that the ODBC interface itself does not support the full set of SQL syntax; but I always took that to mean there was no equivalent within the ODBC driver; not that it affected legal SQL syntax. But maybe I'm wrong?

My experience with SQL via the ODBC connector is limited with all examples I've done are from SQL client products like Access, MS SQL.

PARTNER Stefano Gallotta posted 09-14-2023 10:10

Hi @David Knight

Yeah, I am "pushing the envelope here" :)

I had a discussion with @Brian Cram that I wanted to fetch a block of data from the result of a SQL statement and compare that to a simple select (providing IDs) and then process the records.
It's like the select verb and you can add the attributes at the end - the problem with this is that you're not made aware of just how many MV there may be ... a challenge.
So, my theory is (and I've proved it by writing a function) is to ISQL bla bla bla and build a memory table with the resultant output and THEN process the results in the memory table without having to read the DB.
I worked for a company here in UK and their data was hosted in MSSQL (using OpenInsight) and we achieved incredible speed by letting SQL doing the heavy lifting.

My "challenge" in the post was just to be able to say process 10,000 records - but there are many ways around.

Worth pursuing...

Thanks for your input (and interest)

Stefano

PARTNER David Knight posted 09-14-2023 10:55

Ahh, I see now.

I've dug a bit deeper and see that isql as a command is only on d3/linux; and yes, the documentation states it supports a sub-set of SQL commands; presumably NOT including what you want.

Now, not knowing quite HOW to do it; but if there were a Linux bash command which would perform a full-supported syntax of SQL statements where the connection to d3 was via ODBC if you wanted to read d3 data; or connected to some other SQL dataset; I guess you could build and execute that command via EXECUTE !<somecomplexcommand> CAPTURING <blah><blah><blah>??

But it also seems you have found a way via Mr Cram.

My apologies for being confused, I assumed you were trying to get d3-->SQL by pulling from SQL-land; but what you want is to pull SQL data into d3; and want to use the d3-Linux ISQL command to do so.

Alberto Leal posted 09-14-2023 11:18

let put my finger here to
SELECT * FROM table_name ORDER BY RAND() LIMIT 1;

https://www.mysqltutorial.org/select-random-records-database-table.aspx#:~:text=MySQL%20select%20random%20records%20using%20ORDER%20BY%20RAND,first%20row%20in%20the%20result%20set%20sorted%20randomly.

PARTNER Stefano Gallotta posted 09-15-2023 03:50

@Alberto Leal - Hi - no, unfortunately the LIMIT is also not supported.

Try This:

isql select mykey, name, lastname from master LIMIT 2

(assuming of course the table and definitions are setup)