PlItems

ID: 229
PlCodelang: PowerShell
PlGroup: Strings
PlItemTitle: String nach einem Teilstring durchsuchen
Code: # See also a good hint with explanation between like and contains: # http://windowsitpro.com/blog/powershell-contains $fullstring = "This is my string"; if ($fullstring -like "*my*") { Write-Host "found" -ForegroundColor Green; } else { Write-Host "nope" -ForegroundColor Red; }

ID: 234
PlCodelang: PowerShell
PlGroup: Datum, Zeit
PlItemTitle: Aktuelles Datum/Uhrzeit
Code: $myDate=Get-Date -format "yyyy-MM-dd hh:mm"; write-host $myDate -Foregroundcolor Cyan; # 2014-01-14 10:15 # Maybe AM or PM # For 24h Version $myDate=Get-Date -format "yyyy-MM-dd HH:mm"; write-host $myDate -Foregroundcolor Cyan; # 2014-01-14 22:15

ID: 248
PlCodelang: PowerShell
PlGroup: Fallunterscheidung
PlItemTitle: Ternary operator
Code: ## Example from here: https://www.kongsli.net/2012/02/23/powershell-another-alternative-to-ternary-operator/ $thisVar='OK' $resultVar = @{$true=1;$false=2}[$thisVar -eq 'OK'] // same as if-else construction if ($thisVar -eq 'OK') { $resultVar = 1; } else { $resultVar = 2; }

ID: 254
PlCodelang: PowerShell
PlGroup: Filehandling
PlItemTitle: Alle Dateien aus Verzeichnis auslesen
Code: $CSVFullPath = "C:\Temp" $FileFilter = "*.csv" $ListOfFiles = (Get-ChildItem -Path $CSVFullPath -Filter $FileFilter) | Select Name, BaseName, DirectoryName, FullName, Extension $ListOfFiles.Name
Result Example:

a.csv
b.csv
c.csv

ID: 255
PlCodelang: PowerShell
PlGroup: Filehandling
PlItemTitle: Verzeichnis rekursiv durchsuchen
Code: $CSVFullPath = "C:\Temp" $FileFilter = "*.*" $ListOfFiles = (Get-ChildItem -Path $CSVFullPath -Filter $FileFilter -Recurse) | Select Name, BaseName, DirectoryName, FullName, Extension | Where {$_.DirectoryName -like "*not_processed*" } $ListOfFiles.FullName
Result Example:

C:\Temp\ready_for_import_not_processed\a.csv
C:\Temp\ready_for_import_not_processed\b.csv

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: 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: 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: 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: 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