Sorgu sonucuna satır no alanı eklemek:
SELECT [URUN] ,[MAKINE] ,[ONCELIK] ,[KOVAN] ,[SNO] ,ROW_NUMBER() OVER(ORDER BY URUN,MAKINE,ONCELIK) AS KayitNo FROM [MACELLAN].[dbo].[PNTFICRONURNALTRMK] ORDER BY URUN,MAKINE,ONCELIK
| URUN | MAKINE | ONCELIK | KOVAN | KayitNo |
| BCS40K | L411/412 | 1 | 400 | 1 |
| BGS45T | L12/512 | 1 | 400 | 2 |
| BOHCA1 | L411/412 | 1 | 400 | 3 |
| D00NAK | HF Super VM | 2 | 400 | 4 |
| D00NAK | L409/416 | 1 | 400 | 5 |
Select sonucu dönen kayıtlarda belirli bir karakter dizisini belirli başka bir karakter dizisi ile değiştirmek.
SELECT A.WAREHOUSE, A.STOCKPLACE,A.MATERIAL , REPLACE(A.MATERIAL,'LPA34T','L0PA1T') AS MATERIAL2 , A.BEDEN, A.RENK FROM PENTI.dbo.VW_STOK AS A WHERE (A.MATERIAL NOT LIKE 'QC______%') AND A.MATERIAL LIKE '%LPA34T%';
| WAREHOUSE | STOCKPLACE | MATERIAL | MATERIAL2 | BEDEN | RENK |
| 170 | 1 | LPA34T | L0PA1T | 125 | |
| 170 | YH015A | LPA34TD | L0PA1TD | 125 | |
| 180 | 1 | LPA34TB | L0PA1TB | 125 | 2065 |
| 180 | 1 | LPA34TB | L0PA1TB | 125 | 2097 |
| 190 | DIKIS | LPA34TF | L0PA1TF | 125 | 2024 |
| 190 | DIKIS | LPA34TF | L0PA1TF | 125 | 2028 |
| 190 | DIKIS | LPA34TF | L0PA1TF | 125 | 2050 |
| 190 | DIKIS | LPA34TF | L0PA1TF | 125 | 02N93 |
SELECT A.*
FROM IASBOMITEM A,IASBOMHEAD B
WHERE A.COMPONENT
LIKE ‘%[abcdefghijklmnopqrstuvwxyz]%’
collate Latin1_General_CS_AS
AND A.QUNIT=’DZ’
AND B.BOMNUMBER=A.BOMNUMBER
AND B.ISDELETE=0
Otomatik raporlarda rapor çekme gününü alıp istenilen saatler i sorguya ekleyip sonuç almak.
SELECT CONVERT(DATETIME, CONVERT(varchar(11),GETDATE()-1, 111 ) + ‘ 08:30:00’, 111) BeginDate,
CONVERT(DATETIME, CONVERT(varchar(11),GETDATE(), 111 ) + ‘ 08:30:00’, 111) EndDate
BeginDate EndDate
2013-05-27 08:30:00.000 2013-05-28 08:30:00.000
Sorgu sonucu gelen düşey listeyi virgüller ile birleştirerek yatay hale getirmek.
SELECT A.COMPONENT
,STUFF( (SELECT ‘, ‘ + SUBSTRING(B.MATERIAL,LEN(B.MATERIAL)-6,7)
from IASBOMITEM B WITH (NOLOCK)
WHERE B.CLIENT=’00’ AND B.COMPANY=’01’ AND B.COMPONENT=A.COMPONENT
AND B.MATERIAL LIKE ‘%D’ FOR XML PATH(”)),1,1,”) AS MATERIALS
FROM IASBOMITEM A WITH (NOLOCK),IASMATBASIC C WITH (NOLOCK)
WHERE A.CLIENT=’00’ AND A.COMPANY=’01’ AND A.COMPONENT=C.MATERIAL
AND C.CLIENT=A.CLIENT AND C.COMPANY=A.COMPANY AND C.MATGRP LIKE ‘C-___’ AND C.ISDELETE=0
GROUP BY A.COMPONENT
ORDER BY A.COMPONENT
COMPONENT MATERIALS ------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 806860 NULL D1028657 DPDNAHD, DPDNAHD D1028657K NULL D1028657S NULL D1059 NULL D140MM2X10S LILYAHD, LRYALHD D1446 LB178ZD D2266 LMS2GZD, LF51GZD, LMS2GZD D2565 LF82MZD, LF82MZD D267 LL261ZD, LL61ZQD, LM85SZD, LM87MZD, LMS84ZD, LMS94ZD, LU884ZD, LL261ZD, LM85SZD, LM87MZD, LMS84ZD, LMS94ZD D267-2 NULL D2837 LB175ZD D2837S LB175ZD D4035 DJ17ZAD D6236 DJP17HD D727907 F16MD D, LF80MZD, LF80MZD D728117 DLLADHD, FBASIHD, DLLADHD, FBASIHD D728135 LU0A4HD, LG18UHD, LB112HD, DP354HD, FBASIHD, LB112HD, FBBASHD D728135SS LU164HD, LB13UHD, LB13UHD D7849/S8 LTV20HD, DP492KD, DB392KD, DD392KD D806860 LP530HD, LTA16HD, LP530HD, LTS15HD, LU115HD, LTA16HD D806860/01 NULL D806860/02 LU118HD D80MM1X40S LP0A1HD D80MM2X10S LSKA1HD, DVICTHD, LL115HD, LP0A1HD D8300 LG11SHD D830-0015 LP120HD DG11780089 LBA24HD F001EL0707044CL FBBASKD FLBSC01 FPLW1KD FLBST01 FBASIAD, FBASIHD, FBASITD, FBBASHD, FBBASTD, FTAS2HD GUPUR-01 DP354ED, DP355ED, DP354HD (32 row(s) affected)