SQL de bir tablodaki duplicate kayıtları bulma

Yayın tarihi
Cevapla

SELECT A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR
FROM IASMATVENDORS A
GROUP BY A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR
HAVING COUNT(*)>1;

SELECT A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR
INTO TMPIASMATVENDORS
FROM IASMATVENDORS A
GROUP BY A.MATERIAL,A.COMPANY,A.VALIDUNTIL,A.CLIENT,A.VENDOR
HAVING COUNT(*)>1;

SELECT A.* FROM IASMATVENDORS A,TMPIASMATVENDORS B
WHERE A.CLIENT=B.CLIENT AND A.COMPANY=B.COMPANY AND A.MATERIAL=B.MATERIAL AND A.VENDOR=B.VENDOR
AND VALIDFROM >’2009-01-01′;

DELETE A FROM IASMATVENDORS A,TMPIASMATVENDORS B
WHERE A.CLIENT=B.CLIENT AND A.COMPANY=B.COMPANY AND A.MATERIAL=B.MATERIAL AND A.VENDOR=B.VENDOR
AND VALIDFROM >’2009-01-01′;

UPDATE IASMATVENDORS SET VALIDFROM =’2005-01-01′ WHERE MATERIAL LIKE ‘%’ AND VALIDFROM <>’2005-01-01′;

DROP TABLE TMPIASMATVENDORS;

SQL de CASE — WHEN kullanımı

Yayın tarihi
Cevapla

Belirli koşullara göre bir sütunda yer almasını istediğimiz değeri getirmek.

SELECT IASPURINF.[DOCUMENT], IASPURINF.COMPANY, IASPURINF.PLANT, IASPURINF.MATERIAL, IASPURINF.VENDOR, IASPURINF.BUSAREA, IASPURINF.VALIDFROM,
IASPURINF.VALIDUNTIL, IASPURINF.PURDEPT, IASPURINF.NET, IASPURINF.PUNIT, IASPURINF.CURRENCY, IASPURINF.PURUNIT, IASPURINF.STEXT,
IASPURINF.DELIVERYDAY, IASPURINF.MINORDER, IASPURINF.VMATERIAL, IASCUSTOMER.NAME1
,V.EXCHRATESAL
,CASE WHEN IASPURINF.CURRENCY =’TL’ THEN IASPURINF.NET ELSE IASPURINF.NET * V.EXCHRATESAL END AS FTL
FROM IASCUSTOMER INNER JOIN
IASPURINF ON IASCUSTOMER.CLIENT = IASPURINF.CLIENT AND IASCUSTOMER.COMPANY = IASPURINF.COMPANY AND
IASCUSTOMER.CUSTOMER = IASPURINF.VENDOR AND IASCUSTOMER.BUSAREA = IASPURINF.BUSAREA LEFT OUTER JOIN
VW_IAS_KUR AS V ON IASPURINF.CURRENCY = V.CURRENCY
WHERE (IASPURINF.CLIENT = ’00′) AND (IASPURINF.COMPANY LIKE ’01′) AND (IASPURINF.PLANT LIKE ‘%’) AND (IASPURINF.VENDOR LIKE ‘%’) AND
(IASPURINF.MATERIAL LIKE ‘WU17X13′) AND (IASPURINF.BUSAREA LIKE ‘%’) AND (IASPURINF.[DOCUMENT] = 4) AND (IASPURINF.ITEMNUM = 1) AND
(IASCUSTOMER.ISCUSTORVEND >= 2) AND (IASPURINF.STEXT LIKE ‘%’) AND (IASCUSTOMER.NAME1 LIKE ‘%’) AND (IASPURINF.PURDEPT LIKE ‘%’) AND
(IASPURINF.VALIDFROM <= GETDATE()) AND (IASPURINF.VALIDUNTIL >= GETDATE()) AND (IASPURINF.ISDELETE = 0)
ORDER BY IASPURINF.COMPANY, IASPURINF.PLANT, IASPURINF.MATERIAL, IASPURINF.VENDOR, IASPURINF.BUSAREA

MySQL e scriptlerin çakılması

Yayın tarihi
Cevapla

Geliştirme SQL dosyasının ilk satırında aşağıdaki satır olmalı..

SET NAMES LATIN5;

Scripti işlemek için komut isteminde:

mysql -uKULLANICI -pSIFRE -hServerip

ile mysql bağlanılır.

showdatabases;

komutu ile veritabanları listelenir.

mysql -uKULLANICI -pSIFRE -hServerip  IAS602d < c:\hcmt82.sql

komutu ile script ilgili veritabanına çakılır.

Dahasonra da bu script hangi dialog veya sınıflarla ilgili ise çevir kaydet yapılır.