|
Thank you!! The goal was to generate an insert statements for big tables to be used in a python script, at the end Syscolumns helped me a lot.
And again LIST() is such a helpful aggregate function - you might make sure that both lists are sorted identically by adding an ORDER BY expression.
(22 Sep '21, 03:09)
Volker Barth
Replies hidden
Is the GROUP BY TNAME not enough to guarantee that the column names are correctly assigned to each TableName?
(22 Sep '21, 03:48)
Baron
Oh, you mean in order to keep the same order between vals and cols. The question is here how does the list function work? Doesn't it take the same order as they appear in the subquery (of table T1)?
(22 Sep '21, 04:16)
Baron
Replies hidden
In my understanding, unless you specify an ORDER BY clause, the SQL query engine is free to order result sets - and for LIKE, to order the different entries - to its preference according to the used access plans, i.e. it does not need to order them in any meaningful way. And I'm quite sure you want column names and values in the "correct order" here.
(22 Sep '21, 04:35)
Volker Barth
Yes, I need to have them in the "correct order" :) But where to add the order by exactly?
(22 Sep '21, 05:30)
Baron
The full LIST syntax is LIST( [ALL | DISTINCT ] string-expression [, delimiter-string ] [ ORDER BY order-by-expression [ ASC | DESC ], ... ] )
(22 Sep '21, 05:41)
Volker Barth
Thanks, should it then look like this?
(22 Sep '21, 07:05)
Baron
tname is superfluous here, as you already group by tname.
(22 Sep '21, 08:19)
Volker Barth
So you mean only order by cols?
(22 Sep '21, 09:05)
Baron
1
adding order by cols helps to keep both lists in the same order, but both come in a false order (alphabeitcally instead of the real order). So, the correct statement should look like this:
(24 Sep '21, 04:59)
Baron
|
|
The DESCRIBE statement cannot be coded inside a server-side SQL operation because it a client-side ISQL statement. However, you can SELECT FROM SYS.SYSCOLUMNS to get the same result: DESCRIBE TABLE rroad_sample_memo;
SELECT LEFT ( cname, 32 ) AS "Column",
LEFT ( coltype, 32 ) AS "Type",
IF nulls = 'N' THEN 0 ELSE 1 ENDIF AS "Nullable",
IF in_primary_key = 'N' THEN 0 ELSE 1 ENDIF AS "Primary Key"
FROM SYS.SYSCOLUMNS
WHERE tname = 'rroad_sample_memo'
ORDER BY colno;
Column Type Nullable Primary Key
-------------------------------- -------------------------------- -------- -----------
sampling_id unsigned int 0 0
sample_set_number unsigned bigint 0 1
memo long varchar 0 0
Column Type Nullable Primary Key
-------------------------------- -------------------------------- -------- -----------
sampling_id unsigned int 0 0
sample_set_number unsigned bigint 0 1
memo long varchar 0 0
(3 rows)
Execution time: 0.002 seconds
FYI the system catalog views contain vastly more information than any DESCRIBE statement. |
FWIW, there's also the sa_get_table_definition system function that returns basically the CREATE TABLE statement etc. as a long varchar for the according table, but of course that is not the result set you are looking for.
Yes, but it returns the result in a one single column/row, which doesn't help in my case