You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
; Es existiert eine Tabelle namens Table.Cities welche einen Eintrag mit der ID=15 enthält. Diese Tabelle enthält das Feld "Name"
Set ds=##class(Table.Cities).%OpenId(15)
Write !,ds.Name
; Oder über SQL
Set result = ##class(%Library.ResultSet).%New()
Set sql = "SELECT Name FROM Table.Cities WHERE ID=15"
Do result.Prepare(sql)
Do result.Execute("")
For
{
Quit:'result.Next()
Set name=result.Data("Name")
}
269
PL/pgSQL
Datenbank
UPDATE SQL mit JOIN
UPDATE TableToBeUpdated
SET myField1 = 'Update this field'
FROM
(
SELECT * FROM JoinedTable
WHERE cond1 = 1
)
J
WHERE J.ID = TableToBeUpdated.ID
;
270
T-SQL
Datenbank
UPDATE SQL mit JOIN
UPDATE UPD_TABLE
SET UPD_TABLE.myField1 = 'Update this field'
FROM TableToBeUpdated UPD_TABLE
LEFT JOIN
(
SELECT * FROM JoinedTable
WHERE cond1 = 1
)
J
WHERE J.ID = UPD_TABLE.ID
;
271
MySQL
Datenbank
UPDATE SQL mit JOIN
UPDATE TableToBeUpdated UPD_TABLE
INNER JOIN JoinedTable J ON J.id = UPD_TABLE.id
SET UPD_TABLE.myField1 = 'Update this field'
;
272
MS Access SQL
Datenbank
NULL Wert eines Feldes übersteuern
SELECT
FieldA
,Nz([FieldB,"This field is null") As FieldB_Nz
FROM TableA
275
PL/pgSQL
Datenbank
Stored Procedure anlegen/löschen
CREATE OR REPLACE FUNCTION increment(i INT) RETURNS void AS $$
BEGIN
INSERT INTO accounts (id,name, balance) VALUES (i, 'Test', 1200);
END; $$
LANGUAGE plpgsql;
DROP FUNCTION increment(i INT);
276
T-SQL
Datenbank
Stored Procedure anlegen/löschen
CREATE PROCEDURE increment(@i INT) AS
BEGIN
INSERT INTO accounts (id,name, balance) VALUES (i, 'Test', 1200);
END;
277
PL/pgSQL
Datenbank
Datenbankobjekte Kommentar
COMMENT ON FUNCTION increment(i INT) IS 'This is a comment';
283
Yii2
Datenbank
Verzeichnis erstellen
<?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();
?>
284
Yii2
Datenbank
Abfrage mit Aggregration
<?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();
?>
