SQLTeam.com | Weblogs | Forums

Fix my syntax error so this will work! Stumped


#1

Below is my statement and why it is this way. The part at the top before the double spaced line break is what needs to be fixed. The second part is the original statement. The problem with the original statement is that in the case that "B$UORG" = 0 , then we have a divide by zero error. So I added the top section to run first, to ensure that B$UORG <> 0 and THEN it runs the second part of the statement. Finishing with ELSE 0 END in case B$UORG does equal 0, the ELSE 0 END will just return a 0. I'm getting a syntax error "at or near" the <>

CASE
WHEN ( SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" ) <> 0 THEN

( SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$AEXP" / 100 )
END ) ) / ( SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) )


#2

Please post the entire SQL so that we can help. It is easier for us to help if we can pull it into SSMS and use the syntax parser.


#3

Here is the entire SQL statement:

SELECT
ABAC02,
" ABAC0201" AS ABAC0201,
"B$CO" AS "B$CO",
"B$NAME" AS "B$NAME",
"B$MCU" AS "B$MCU",
"B$DL01" AS "B$DL01",
"B$SRP1" AS "B$SRP1",
"B$SRPD" AS "B$SRPD",
"B$LITM" AS "B$LITM",
MAX( "B$DSC1" ) AS "B$DSC1",
SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" = &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) AS Q1ITEM,
SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" = &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) AS Q2ITEM,
SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) AS Q1YTD,
SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) AS Q2YTD,
SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" = &B$DGM2 ) THEN ( "B$AEXP" / 100 )
ELSE 0
END ) AS A1ITEM,
SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" = &B$DGM2 ) THEN ( "B$AEXP" / 100 )
ELSE 0
END ) AS A2ITEM,
SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$AEXP" / 100 )
ELSE 0
END ) AS A2YTD,
SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$AEXP" / 100 )
ELSE 0
END ) AS A1YTD,
SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" = &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) + SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" = &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) + SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) + SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) + SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" = &B$DGM2 ) THEN ( "B$AEXP" / 100 )
ELSE 0
END ) + SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" = &B$DGM2 ) THEN ( "B$AEXP" / 100 )
ELSE 0
END ) + SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$AEXP" / 100 )
ELSE 0
END ) + SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$AEXP" / 100 )
ELSE 0
END ) AS COLUMN0018,
CASE
WHEN ( SUM (
CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
END ) ) <> 0 THEN ( SUM (
CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$AEXP" / 100 )
END ) ) / ( SUM (
CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
END ) )
ELSE 0
END AS "asp1",
CASE
WHEN ( SUM (
CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
END ) ) <> 0 THEN ( SUM (
CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$AEXP" / 100 )
END ) ) / ( SUM (
CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
END ) )
ELSE 0
END AS "asp2"
FROM
TQCMOD.F55402 F55402,
GENPRDDTA.F0101 F0101
WHERE
F55402."B$AN8" = F0101.ABAN8
AND ( ( "B$TYRG" = '1'
AND "B$DGY" <= &B$DGY2
AND "B$DGM" <= &B$DGM2
AND "B$CO" BETWEEN &B$CO2 AND &B$CO2
AND "B$MCU" BETWEEN &B$MCU2 AND &B$MCU2)
AND ABAT1 = &ABAT1)

GROUP BY
ABAC02,
" ABAC0201",
"B$CO",
"B$NAME",
"B$MCU",
"B$DL01",
"B$SRP1",
"B$SRPD",
"B$LITM"
HAVING
SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) + SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$UORG" )
ELSE 0
END ) + SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$AEXP" / 100 )
ELSE 0
END ) + SUM ( CASE
WHEN ( CAST ( "B$YEAR" AS NUMERIC ) = &B$DGY2 - 1 AND "B$DGM" <= &B$DGM2 ) THEN ( "B$AEXP" / 100 )
ELSE 0
END ) <> 0
ORDER BY
1,
3,
5,
7,
9


#4

Looks like you just need to put square brackets around your object names due to the special characters being used. Such as: &B$DGY2 --> [&B$DGY2]