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

Useful code snippets and help for problems with Velocity-/Java-Script Code

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

Postby jclassen » 25 Mar 2019, 19:20

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
jclassen
 
Posts: 16
Joined: 19 Apr 2017, 16:34

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

Postby jclassen » 25 Mar 2019, 21:16

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
jclassen
 
Posts: 16
Joined: 19 Apr 2017, 16:34


Return to Developers Corner



Who is online

Users browsing this forum: No registered users and 0 guests

cron