SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.5.362 Pro] - Info on table

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.5.362 Pro] - Info on table
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post [11.5.362 Pro] - Info on table Reply with quote
Having an existing table in a query in the editor SA retrieves the table information correctly and allows inserting parts of it into the editor from the popups:






When having a CREATE TABLE xyz AS SELECT... statement in the editor, there are several problems with it:

#1 Regardless of SA knowing there's (or at least will be) a table named t_service_journey_call, and it offers it in the popup (though it shows it as a local variable),



it does not insert its columns as it would when selecting a table from the popup, it simply inserts its name. So I have to write SELECT col1, col2, etc FROM `t_service_journey_call` as tsjc myself, and after that SA does recognize it as a table when adding new parts to the query. Which leads to...

#2 SA tries to guess the future structure of that table. Alas, that guess is not a very good one. The connection SE uses in the screenshot is made to a MariaDB database, and the type varchar(max) is not a valid type there.
#3 Furthermore, the columns SA guesses the t_service_journey_call table will have are not the actual ones that are used to construct that table but all session variables that are set at the top of the editor contents instead.



#4 and those will be the ones further popups will offer as columns from the table t_service_journey_call


Mon Feb 01, 2021 5:05 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I think the answer to your questions / observations is quite simple, when it comes to new table declarations in scripts the Intellisense doesn't recognize yet columns in a table defined using CREATE TABLE ... AS SELECT... It picks up the object name, but doesn't get further than that. As in 11.5, it does recognize CREATE TABLE ... (...) and also DECLARE @table_var TABLE(....).



Mon Feb 01, 2021 5:53 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I checked few other things, and I think it's worth noting that with SQL Server environments it also recognizes SELECT ... INTO ... FROM ..



Mon Feb 01, 2021 5:57 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
I see. That means the whole shebang will start work properly as soon as I start defining the new tables explicitly instead of shortcutting the process by using the CREATE TABLE ... AS SELECT ... statement, right? Do you plan to implement recognition of that statement?
Tue Feb 02, 2021 4:26 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Yes, there are planned enhancements for the next major version 12 to enhance intellisense for many types of DDL statements, including CTAS (a.k.a CREATE TABLE AS subquery).
Tue Feb 02, 2021 12:00 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
That's great! Do you have a rough ETA for v12 perhaps? Can you share a list of planned changes?
Tue Feb 02, 2021 1:52 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I think the ETA is 5 to 6 months. There are a lot of things still being evaluated, big appetite for adding new enhancements, improvements, and new major features to the next major version code branch. The roadmap for 12.x refers to significant intellisense enhancements for all kinds of DDL operations, expanding Data Factory to a full blown ETL framework, full multi-threading for all tools and internally executed queries to enable more things to run concurrently with less waits, big internal changes in the database catalog caching to resolve current shortcomings, including static caching to files to deal with monstrous and notoriously slow Oracle data dictionaries, SQL profilers for Oracle and DB2, and so on... What would actually make to the initial 12.0 release is not yet known, it's pretty dynamic at this point. The work on 12.0 has already started.
Wed Feb 03, 2021 11:58 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.