An SQL statement that contains almost all the standard query syntax

Standard | grammar | statement
W_kmr01_11----KMM13BR copywrite by LD 2000/09/07 15:30

SELECT DISTINCT SUBSTRING (KMM102. kmm102_bgt_type,1,4) as Bgt_type,
(Case SUBSTRING (KMM102). kmm102_bgt_type,3,2) when "THEN" "1" when "Ten" THEN "2"
When "one" THEN "3" ELSE "9" end) as Bgt_class,
SUBSTRING (KMM102. kmm102_bgt_type,1,2) as Bgt_team,

(ISNULL (SELECT SUBSTRING (KMC101). Kmc101_name,charindex ("-", KMC101. Kmc101_name) +1,12) from KMC101
WHERE (KMC101. Kmc101_type = "Budgetcode")
and (KMC101. Kmc101_code = SUBSTRING (KMM102. kmm102_bgt_type,1,4) + ")") as Bgt_name,

SUM (ISNULL (KMM101). kmm101_bgt_jan,0)) as bgt_amt01,
SUM (ISNULL (KMM101). kmm101_bgt_feb,0)) as bgt_amt02,
SUM (ISNULL (KMM101). kmm101_bgt_mar,0)) as bgt_amt03,
SUM (ISNULL (KMM101). kmm101_bgt_apr,0)) as bgt_amt04,
SUM (ISNULL (KMM101). kmm101_bgt_may,0)) as bgt_amt05,
SUM (ISNULL (KMM101). kmm101_bgt_jun,0)) as bgt_amt06,
SUM (ISNULL (KMM101). kmm101_bgt_jul,0)) as bgt_amt07,
SUM (ISNULL (KMM101). kmm101_bgt_aug,0)) as bgt_amt08,
SUM (ISNULL (KMM101). kmm101_bgt_sep,0)) as bgt_amt09,
SUM (ISNULL (KMM101). kmm101_bgt_oct,0)) as Bgt_amt10,
SUM (ISNULL (KMM101). kmm101_bgt_nov,0)) as bgt_amt11,
SUM (ISNULL (KMM101). kmm101_bgt_dec,0)) as bgt_amt12,

(SUM (ISNULL (KMM101). kmm101_bgt_jan,0) + SUM (ISNULL) (KMM101. kmm101_bgt_feb,0)) +
SUM (ISNULL (KMM101). kmm101_bgt_mar,0) + SUM (ISNULL) (KMM101. kmm101_bgt_apr,0)) +
SUM (ISNULL (KMM101). kmm101_bgt_may,0) + SUM (ISNULL) (KMM101. kmm101_bgt_jun,0)) +
SUM (ISNULL (KMM101). kmm101_bgt_jul,0) + SUM (ISNULL) (KMM101. kmm101_bgt_aug,0)) +
SUM (ISNULL (KMM101). kmm101_bgt_sep,0) + SUM (ISNULL) (KMM101. kmm101_bgt_oct,0)) +
SUM (ISNULL (KMM101). kmm101_bgt_nov,0) + SUM (ISNULL) (KMM101. kmm101_bgt_dec,0))) as AMT01,
("XXXXXXXX") As AMT02,

SUM ((ISNULL) (CONVERT (CHAR (8), KMM102. kmm102_chk_date,112) < "YYYYMMDD")
THEN KMM102. Kmm102_chk_amt ELSE 0 End), 0)) as AMT09,
SUM ((ISNULL) ((CONVERT (CHAR (8), KMM102). kmm102_over_date,112) < "YYYYMMDD")
and KMM102. Kmm102_over_status <> "00")
THEN KMM102. Kmm102_over_amt
When (CONVERT (CHAR (8), KMM102. kmm102_over_date,112) < "YYYYMMDD")
and KMM102. Kmm102_over_status = "00")
THEN KMM102. Kmm102_over_amt ELSE 0 End), 0)) as AMT07,
SUM ((ISNULL) ((CONVERT (CHAR (8), KMM103. kmm103_pay_date,112) < "YYYYMMDD")
and KMM103. Kmm103_pay_status <> "2")
THEN KMM103. Kmm103_pay_amt ELSE 0 End
From KMM103
WHERE (KMM103. Kmm103_yy = KMM102. KMM102_YY)
and (KMM103. Kmm103_bgt_kind = KMM102. Kmm102_bgt_kind)
and (KMM103. Kmm103_case_no = KMM102. kmm102_case_no)) (0)) as AMT03,
SUM ((ISNULL) ((CONVERT (CHAR (8), KMM103. kmm103_pay_date,112) < "YYYYMMDD")
and KMM103. Kmm103_pay_status <> "1")
THEN KMM103. Kmm103_pay_amt ELSE 0 End
From KMM103
WHERE (KMM103. Kmm103_yy = KMM102. KMM102_YY)
and (KMM103. Kmm103_bgt_kind = KMM102. Kmm102_bgt_kind)
and (KMM103. Kmm103_case_no = KMM102. kmm102_case_no)) (0)) as AMT04
From KMM102,
KMM101
WHERE (KMM102. Kmm102_yy = "YYY") and
(KMM102. Kmm102_bgt_kind = "1") and
(KMM101. Kmm101_yy =* KMM102. KMM102_YY) and
(KMM101. Kmm101_bgt_kind =* KMM102. Kmm102_bgt_kind) and
(KMM101. Kmm101_bgt_type =* KMM102. Kmm102_bgt_type)
GROUP by SUBSTRING (KMM102. kmm102_bgt_type,1,4),
SUBSTRING (KMM102. kmm102_bgt_type,3,2),
SUBSTRING (KMM102. kmm102_bgt_type,1,2)
ORDER BY Bgt_class ASC,
Bgt_type DESC
;