Page 1 of 1

TIPP: SQL SELECT * except 2 columns/SELECT * außer 2 Spalten

PostPosted: 25 Mar 2019, 19:20
by jclassen
In order to SELECT all columns FROM a table except for two specific columns, insert the following code in a RapidRep Script/SELECT-Executable or Function.
Adjust table, columns and connection to your specific needs.
Omit the connection name ("MyConnection") in order to use the default connection.

Zum Abfragen aller, außer zwei Spalten einer Tabelle, einfach den folgenden Code in ein RapidRep-Skript/SELECT-Executable oder Funktion einfügen.
Tabelle, Spalten und Verbindung je nach Bedürfnis anpassen.
Verbindungsname ("MyConnection") weglassen, um die Standard-Verbindung zu verwenden.

Code:
#set($table = "MY_TABLE")
#set($columns = $RR.getColumnLabels("SELECT * FROM $table", "MyConnection"))
$RR.call($columns.remove("MY_COLUMN_x"))
$RR.call($columns.remove("MY_COLUMN_y"))
SELECT
$RR.listElements($columns, "$RR.character(10), ")
FROM $table

Re: TIPP: SQL SELECT * except 2 columns/SELECT * außer 2 Spa

PostPosted: 25 Mar 2019, 21:16
by jclassen
Alternative (RR 5.7.5 and higher):
You can also use BaseLib (needs to be declared in "Libraries"). It offers a range of convenient functions such as "modifyStringListColumns".
The function returns a list of column names after ignoring columns in List $ignore and/or renaming columns as specified in Map $renamings.

Code Example:
#set($table = "MY_TABLE")
#set($columns = $RR.getColumnLabels("SELECT * FROM $table"))
#set($renamings = {"MY_COLUMN_1":"MY_COLUMN_a", "MY_COLUMN_2":"MY_COLUMN_b"}) ## no renaming? -> #set($renamings = {})
#set($ignore = ["MY_COLUMN_x", "MY_COLUMN_y"])
#set($columns = $BaseLib.modifyStringListColumns($columns, $ignore, $renamings))
SELECT
$RR.listElements($columns, "$RR.character(10), ")
FROM $table