PlItems

ID: 296
PlCodelang: EXASOL
PlGroup: Datenmengen
PlItemTitle: Differenzen anzeigen
Code: WITH a AS ( SELECT 1 AS n UNION ALL SELECT 2 AS n UNION ALL SELECT 3 AS n UNION ALL SELECT 4 AS n ), b AS ( SELECT 4 AS n UNION ALL SELECT 5 AS n UNION ALL SELECT 6 AS n UNION ALL SELECT 7 AS n ) SELECT * FROM a EXCEPT -- <------------------------------ SELECT * FROM b
Result Example:

N
1
2
3

ID: 298
PlCodelang: EXASOL
PlGroup: Datum, Zeit
PlItemTitle: Aktuelles Datum/Uhrzeit
Link Technical Documentation: https://docs.exasol.com/db/latest/sql_references/formatmodels.htm
Code: SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DDTHH24:MI:SS.FF3') AS iso_date_string
Result Example:

ISO_DATE_STRING
2021-06-20T17:12:34.567

ID: 293
PlCodelang: BigQuery
PlGroup: Datum, Zeit
PlItemTitle: Gestriges Datum
Code: SELECT CURRENT_TIMESTAMP() AS today_timestamp , DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) AS yesterday_as_date
Result Example:

today_timestamp	yesterday_as_date
2024-02-07 13:40:37.903	2024-02-06

ID: 294
PlCodelang: BigQuery
PlGroup: Datenmengen
PlItemTitle: Differenzen anzeigen
Link Reference: https://gist.github.com/yancya/bf38d1b60edf972140492e3efd0955d0
Code: WITH a AS ( SELECT * FROM UNNEST([1,2,3,4]) AS n ), b AS ( SELECT * FROM UNNEST([4,5,6,7]) AS n ) SELECT * FROM a EXCEPT DISTINCT -- <------------------ SELECT * FROM b
Result Example:

n
1
2
3

ID: 295
PlCodelang: BigQuery
PlGroup: Datenbank
PlItemTitle: Eine Menge B reduziert um Menge A
Code: -- Siehe BigQuery -> Differenzen anzeigen -- https://proglang.meta-grid.com/index.php?r=plItems/view&id=294

ID: 297
PlCodelang: BigQuery
PlGroup: Datum, Zeit
PlItemTitle: Aktuelles Datum/Uhrzeit
Code: SELECT FORMAT_TIMESTAMP("%FT%H:%M:%E3S", CURRENT_DATETIME()) AS iso_date_string
Result Example:

ISO_DATE_STRING
2021-06-20T17:12:34.567

ID: 300
PlCodelang: BigQuery
PlGroup: Datenbank
PlItemTitle: UPDATE SQL mit JOIN
Link Credits: Yannik :-)
Code: UPDATE `project.dataset.table1` t1 SET t1.target_column = t2.source_column FROM `project.dataset.table2` t2 WHERE t1.join_key = t2.join_key;

ID: 283
PlCodelang: Yii2
PlGroup: Datenbank
PlItemTitle: Verzeichnis erstellen
Code: <?php // Example as SQL: // SELECT * FROM MainTable // WHERE fieldA=5 AND fieldB IN (SELECT id FROM SubTable WHERE fieldC=551) $fieldC = 551; $SubTable_idQry = SubTable::find()->select('id')->where(['fieldC' => $fieldC]); // Static way: $fieldA = 5; // As result: $fieldA = ThirdTable::find() ->select('fieldA_Reference') ->where(['fieldC' => $fieldC]) ->one(); $MainTableResultQry = MainTable::find() ->where(['in', 'fieldB', $SubTable_idQry]) ->andWhere(['fieldA' => $fieldA]) ->all(); ?>

ID: 284
PlCodelang: Yii2
PlGroup: Datenbank
PlItemTitle: Abfrage mit Aggregration
Code: <?php /* // Example as SQL SELECT FieldA ,COUNT(FieldA) as cnt FROM MainTable WHERE FieldA = 5 GROUP BY FieldA */ $MainTableResultQry = MainTable::find() ->select(['FieldA', 'COUNT(FieldA) as cnt']) ->where(['FieldA' => 5]) ->groupBy(['FieldA']) ->createCommand() ->queryAll(); ?>
Result Example:

---

FieldA	cnt
1000	23

| 1500 | 5 |

$MainTableResultQry[0 => ['FieldA' => 1000, 'cnt' => 23], 1 => [['FieldA' => 1500, 'cnt' => 5]]]

ID: 286
PlCodelang: Yii2
PlGroup: Yii2-ActiveQuery
PlItemTitle: Abfrage mit Subselect
Code: <?php /* SQL Example: SELECT * FROM `Cities` WHERE (`postalcode_id` IN (SELECT `id` FROM `postalcodes` WHERE `postalcode`='12345') ) OR (`region_id` IN (SELECT `id` FROM `regions` WHERE `region`='North') ) */ $active_query_postalcodes_For_Subselect = \app\models\Postalcodes::find()->select("id")->where(["postalcodes" => '12345']); // Attention! No "->all()" at the end to create a subselect! $active_query_regions_For_Subselect = \app\models\Regions::find()->select("id")->where(["region" => 'North']); // Attention! No "->all()" at the end to create a subselect! $model = new \app\models\Cities(); $all_Cities_With_IN_and_OR = $model::find() ->where(['in', "postalcode_id", $active_query_postalcodes_For_Subselect]) ->orWhere(['in', "region_id", $active_query_regions_For_Subselect]) ->all(); ?>

ID: 292
PlCodelang: Yii2
PlGroup: Yii2-Internal
PlItemTitle: Prüfen ob aktueller User ein Gast ist
Code: use Yii; if (! \Yii::$app->user->isGuest) { $result = "User is logged in. User is no guest" }

ID: 302
PlCodelang: Yii2
PlGroup: Sessions
PlItemTitle: Werte in Session speichern und lesen
Link Reference: https://stackoverflow.com/questions/25079781/how-to-add-more-user-identity-session-attributes-in-yii2
Link Credits: https://stackoverflow.com/a/25080633
Code: \Yii::$app->session->set('user.attribute',$value); \Yii::$app->session->get('user.some_attribute');

ID: 266
PlCodelang: MS Access VBA
PlGroup: Array
PlItemTitle: Dictionary Basics
Code: ' empty dict Dim myDict As Dictionary Set myDict = New Dictionary ' some static values myDict.Add "key1", "value1" myDict.Add "key2", "valueY" myDict.Add "keyX", "value98" ' update a key programmatically myDict.Remove "key1" myDict.Add "key1", "new value" ' iterate Dim strKey As Variant Dim current_key As String Dim strValue As String For Each strKey In myDict.Keys() current_key = strKey strValue = myDict.Keys(strKey) Next ' Exists If myDict.Exists(strKey) Then Debug.Print "yes" End If ' get an specific item Dim subdict As String subdict = myDict.Keys("key2") # valueY

ID: 262
PlCodelang: MS Access SQL
PlGroup: Datenbank
PlItemTitle: CTAS
Code: -- Sollte die Zieltabelle (myDestinationTable) bereits vorhanden sein -- wird diese überschrieben (neue Strukturen werden übernommen) SELECT * INTO myDestinationTable FROM mySourceTable

ID: 272
PlCodelang: MS Access SQL
PlGroup: Datenbank
PlItemTitle: NULL Wert eines Feldes übersteuern
Link Technical Documentation: https://www.techonthenet.com/access/functions/advanced/nz.php
Code: SELECT FieldA ,Nz([FieldB,"This field is null") As FieldB_Nz FROM TableA
Result Example:

FieldA | FieldB_Nz ContentA | This field is null

ID: 256
PlCodelang: PowerShell
PlGroup: Textdateien
PlItemTitle: TextDatei auslesen
Code: $myFilename = "file.txt" $fileContent = (Get-Content $myFilename)

ID: 257
PlCodelang: PowerShell
PlGroup: Schleifen
PlItemTitle: For-Schleife
Code: $elements ="A;B;C" $elements.Split(";") | ForEach-Object { write-host $_ } Write-Host " - Next example -" $elements ="D;E;F" ForEach ($element in $elements.Split(";")) { Write-Host $element }
Result Example:

A
B
C
- Next example -
D
E
F

ID: 258
PlCodelang: PowerShell
PlGroup: Exceptions
PlItemTitle: Try Catch Except
Link Credits: https://ashrafur.wordpress.com/2010/03/04/powershell-rename-folderdirectory/
Code: $DirectoryName = "C:\Temp\rename_test_new" Try { $newDirectoryName = $DirectoryName -replace "_new", "_old" Rename-Item -path $DirectoryName -newName $newDirectoryName } Catch { Write-host "Error. Exit!" Write-Error $_.Exception.Message exit # Wenn das Script hart beendet werden soll, dann kann exit verwendet werden }

ID: 259
PlCodelang: PowerShell
PlGroup: Strings
PlItemTitle: Multiline String Variable
Link Reference: https://blogs.technet.microsoft.com/heyscriptingguy/2015/12/31/powertip-use-here-strings-with-powershell/
Link Credits: https://powershell.org/forums/topic/debugging-advanced-functions-with-multi-line-string-assignment-problems/
Code: # In PowerShell such variables are called: Here-Strings $mySQLString = @" SELECT 1 FROM dbo.TestTable WHERE 1=1 "@

ID: 267
PlCodelang: PowerShell
PlGroup: Array
PlItemTitle: Dictionary Basics
Code: # empty Hashtable $myDict = @{} # some static values $myDict.Add("key1", "value1") $myDict.Add("key2", "valueY") $myDict.Add("keyX", "value98") # update a key programmatically $myDict.Remove("key1") $myDict.Add("key1", "new value") # iterate Foreach ($key in $myDict.keys) { $strKey = $key $strValue = $Parameter[$key] } # Exists If ($myDict.ContainsKey("key1") -eq $true) { write-host "yes" } # get an specific item $subdict = $myDict("key2") # valueY