SQL tips and tricks – reference

From Julian to a normal date
CONVERT(VARCHAR(10),CAST(LEFT(CAST(D1.CDSSDJ AS DECIMAL)+1900000,4) AS CHAR(4))
+DATEADD(DAY,0,CAST(RIGHT(CAST(D1.CDSSDJ AS DECIMAL)+1900000,3) AS INT)-1),121)

@TODAY variable
DECLARE @TODAY INT = (YEAR(GETDATE())-1900)*1000+DATEPART(Y,GETDATE())

Get today – 4 days (doesn’t work in leap years)
DECLARE @FROM INT = CASE WHEN @TODAY%1000 < 4 THEN @TODAY- 1000 + 365 - 3 ELSE @TODAY - 3 END SELECT @TODAY [TODAY], @TODAY%1000 [TODAY%1000], @TODAY- 1000 + 365 [TODAY- 1000 + 365], @FROM More that one value on one row:
, ISNULL((SELECT RTRIM(EAEMAL) + ‘ ‘ AS [text()] FROM CRPDTA.F01151
WHERE EAAN8 = CHUSA5 AND EAETP = ‘INV’
ORDER BY EAEMAL FOR XML PATH (”)), ”) [Customer INV emails]

Store SELECT results into a variable
DECLARE @JDEPROD ASVARCHAR(20)=”
SELECT @JDEPROD = @JDEPROD + NAMEFROMSYS.SERVERSWHERENAMELIKE’OUSPXD15%’
SELECT @JDEPROD

UDC value match
LEFT OUTER JOIN CRPCTL.F0005 ON DRSY = ’01’ AND DRRT = ‘CM’ AND CAST(CAST(ABAN8 AS INT) AS VARCHAR(8)) = DRDL02

DD – List data dictionary elements where the same UDS is used for validation
SELECT TOP 33 *FROM DDPD920.DD920.F9210 WHERE FRERO1 =’00’AND FRERO2 =’DT’

JDE table fields and indexes
F98711 – List of Fields – Table Columns
F98712 – Primary Index Header – one line per index (INID)
F98713 – Primary Index Detail – one line rep index field (INID CMPI SRTO)

Data Source Master
F986101 – Object Configuration Master
F98611 – Data Source Master
F986115 – Table and Data Source Sizing Table

Users and roles
F0092 Users
F0093 User Environments
F0094 Environment Master
F98OWSEC Users / Password / Fast Path
F00950 Security Workbench Table 1-action 4-row
F95921 Role Relationships Table