SQL
Tags: uyumsoft
Published: 2024-01-15 16:13:46

> Dinamik Tablo View
```
CREATE OR REPLACE VIEW UYUMSOFT.ZZZW_ITEM_NAME_MANUEL AS
SELECT 
0 AS CREATE_USER_ID,
0 AS UPDATE_USER_ID,
CURRENT_DATE AS CREATE_DATE,
CURRENT_DATE AS UPDATE_DATE,
ROW_NUMBER() OVER () AS ITEM_NAME_MANUEL_ID,
ITEM_NAME_MANUAL
FROM PSMT_REQUEST_D
GROUP BY ITEM_NAME_MANUAL;
```

> SQL XMLAGG( XMLELEMENT ) ORACLE
```
RTRIM(XMLAGG( XMLELEMENT (e, ORDER_DOC_NO, ',')).EXTRACT ('//text()').GETCLOBVAL(), ', ') AS ORDER_DOC_NO
```

```
TO_CHAR(SUBSTR(RTRIM(XMLAGG( XMLELEMENT (e, SUBSTR (IT.DCARD_NAME, 1, 99) || ',')).EXTRACT ('//text()').GETCLOBVAL(), ','), 1, 99)) AS "DCARD_NAME"
```

```
MERGE INTO FINT_CAD_D TRG
USING (SELECT CD.ROWID AS RID, CM.CREDIT_ACC_ID
         FROM FINT_CAD_D CD
        INNER JOIN FINT_CAD_M CM
           ON CD.CAD_M_ID = CM.CAD_M_ID
        WHERE CM.CAD_M_ID = 4918) SRC
ON (TRG.ROWID = SRC.RID)
WHEN MATCHED THEN
  UPDATE SET TRG.CREDIT_ACC_ID = SRC.CREDIT_ACC_ID
```

```
postgresql integer to decimal
CAST(COUNT(IPM.QTY) AS DECIMAL) AS "PALET SAYISI",
```

> INSERT INTO
```
INSERT INTO ASTT_ACTV_VAL_CHANGE_D 
(
CREATE_USER_ID,
UPDATE_USER_ID,
RATE,
AMORT_DIFFRENCE,
ASSET_D_ID
)
select
CREATE_USER_ID,
UPDATE_USER_ID,
RATE,
AMORT_DIFFRENCE
ASSET_D_ID
from ZZ_ASTT_ACTV_VAL_CHANGE_D t
```

```
SUBSTR(CD.code,1,3) IN ('646','656')
```

```
set force_license
update gnlp_general_system_param set force_license=1
```

```
Oracle RENAME Table
RENAME ZZZT_FIN_INVOICE TO ZZZB_FIN_INVOICE
```

```
SQL NOT EXISTS
SELECT
*
FROM FINT_ACC_M M
WHERE 
1=1
AND NOT EXISTS (SELECT * FROM FINT_ACC_D D WHERE M.acc_m_id = D.acc_m_id AND M.cat_code1_id = D.cat_code1_id)
```

```
ROW_NUMBER
OVER (PARTITION BY MM.FIRMA_KOD ORDER BY MM.EKIPMAN_KOD) AS DOC_NO,
```

```
HAVING COUNT(*) > 1
SELECT
doc_no,
note1,
fin_mcard_id,
cat_code1_id,
amt_debit,
amt_credit,
count(*) AS count
FROM fint_fin_m --WHERE fin_m_id IN (81118,10000018)
GROUP BY
doc_no,
note1,
fin_mcard_id,
cat_code1_id,
amt_debit,
amt_credit
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;
```

```
UPDATE UPAGE
SET MAIN_OBJECT_NAME='PSM.InvoiceMCollection,PSM,Version=1.0.0.0,Culture=neutral,PublicKeyToken=null'
where page_code='INV201101'
```

```
SELECT
*
FROM all_tab_columns S
WHERE S.TABLE_NAME LIKE 'TETD_TET_CAR%' AND S.OWNER = 'UYUMSOFT'
AND S.COLUMN_NAME = 'TET_CAR_TRACKING_ID'
```

```
script.txt
C:\inetpub\wwwroot\Senfoni\CustomPrg
UPDATE APPD_FILTER SET DOWN_FIELDS = 'LineNo,UnitPriceTra,Qty,LotCode' WHERE APPD_FILTER_ID = 1306 AND OBJECT_TYPE LIKE '%PSM.OrderD%'
UPDATE APPD_FILTER SET DOWN_FIELDS = 'Description,LotPatternMCode' WHERE APPD_FILTER_ID = 6351 AND OBJECT_TYPE LIKE '%GNL.TempCoDocTraCollection%'
```

```
CONNECT BY LEVEL
INNER JOIN (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL < 100000) LVL ON LVL.RN <= POD.QTY
```

```
BEGIN TRANSACTION;
/*CREATE TABLE zzzt_pre_cost
  AS (SELECT * FROM zzzw_pre_cost);
COMMIT;
*/
DELETE FROM zzzt_pre_cost;
INSERT INTO zzzt_pre_cost (SELECT * FROM zzzw_pre_cost);
COMMIT;
END;
```

> SQL BEGIN TRANSACTION
```
BEGIN
UPDATE GNLD_COUNTRY SET LATITUDE = 1, LONGITUDE=2 WHERE COUNTRY_ID = 103;
UPDATE GNLD_CITY SET LATITUDE = 3, LONGITUDE=4 WHERE CITY_ID = 12290;
COMMIT;
END;
```

> e-Fatura Ürün Ad, fn_ExecuteSQL
```
{fn_ExecuteSQL(SELECT
CASE WHEN (fd.item_name_manual = '' OR fd.item_name_manual IS NULL)  THEN concat(tl.dcard_name,' ', tt.item_attribute_code) ELSE fd.item_name_manual END AS dcard_name
FROM psmt_invoice_d fd
LEFT JOIN invw_item_tables tl ON tl.linetype = fd.line_type AND tl.dcard_id = fd.dcard_id
LEFT JOIN invd_item_attribute tt ON fd.item_attribute1_id = tt.item_attribute_id
WHERE fd.invoice_m_id = #Id# and fd.dcard_id = #D.DcardId# and fd.ITEM_ATTRIBUTE1_ID = #D.ItemAttribute1Id#)}

```

> Oracle
```
{fn_ExecuteSQL(SELECT
CASE WHEN (fd.item_name_manual = '' OR fd.item_name_manual IS NULL) THEN tl.dcard_name || ' ' || tt.item_attribute_code ELSE fd.item_name_manual END AS dcard_name
FROM psmt_invoice_d fd
LEFT JOIN invw_item_tables tl ON tl.linetype = fd.line_type AND tl.dcard_id = fd.dcard_id
LEFT JOIN invd_item_attribute tt ON fd.item_attribute1_id = tt.item_attribute_id
WHERE fd.invoice_m_id = #Id# and fd.dcard_id = #D.DcardId# and fd.ITEM_ATTRIBUTE1_ID = #D.ItemAttribute1Id# and fd.line_no=#D.LineNo#)}
```

```
{fn_ExecuteSQL(SELECT max(NOTE1) AS NOTE1 FROM PSMT_INVOICE_D WHERE INVOICE_M_ID = #Id#}
```

> APPD_FILTER, DOWN_FIELDS
```
UPDATE APPD_FILTER SET DOWN_FIELDS = 'LineNo,UnitPriceTra,Qty,LotCode' WHERE APPD_FILTER_ID = 1306 AND OBJECT_TYPE LIKE '%PSM.OrderD%';
UPDATE APPD_FILTER SET DOWN_FIELDS = 'Description,LotPatternMCode' WHERE APPD_FILTER_ID = 6351 AND OBJECT_TYPE LIKE '%GNL.TempCoDocTraCollection%'
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kzejf0ko5ezqd5ht6j70.png)
 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iijqai8gb02l8y9l6rn2.png)