IF statement Query

[JayEff]

Hi,
Does anybody can help me with this statement. I am getting an 'Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'BEGIN'. error.

The 2 query are running perfectly except when I am putting them togheter in a simple If ...end statement. I have never used a 'If statement' in SQL, that is why I am asking. Is there a special way to put parentheses

I have tried a lot of variants like putting parentheses in the front of Select statement and at the end of the statement, but it is not working. Can we used 'if...else...endif' statement like without the BEGIN keyword?

Here's the query :

IF (SELECT SUBSTRING(CCA.order, PATINDEX('%[^0]%', CCA.order), LEN(CCA.order))
FROM tbl_order CCA
WHERE LEN(SUBSTRING(CCA.order, PATINDEX('%[^0]%', CCA.order), LEN(CCA.order)))) < 6
BEGIN
SELECT RIGHT(SUBSTRING(CCA.order, PATINDEX('%[^0]%', CCA.order), LEN(CCA.order)), 4)
FROM tbl_order CCA
WHERE LEN(SUBSTRING(CCA.order, PATINDEX('%[^0]%', CCA.order), LEN(CCA.order)))) < 6
END

Thanks

[DNAunion2000]

/*DNAunion*/ When trying to get something to work for the first time, try using the simplest example you can think of.

Here's the IF part...


IF (SELECT SUBSTRING(CCA.order, PATINDEX('%[^0]%', CCA.order), LEN(CCA.order))
FROM tbl_order CCA
WHERE LEN(SUBSTRING(CCA.order, PATINDEX('%[^0]%', CCA.order), LEN(CCA.order)))) < 6


I count 8 ('s and 8 )'s so this boils down to, IF (something) < 6, and (something) should evaluate to a single value. Does it?



And here's the target of your IF.


BEGIN
SELECT RIGHT(SUBSTRING(CCA.order, PATINDEX('%[^0]%', CCA.order), LEN(CCA.order)), 4)


Okay, balanced parentheses...


FROM tbl_order CCA
WHERE LEN(SUBSTRING(CCA.order, PATINDEX('%[^0]%', CCA.order), LEN(CCA.order)))) < 6
END


I count 4 "(" and 5 ")". Unbalanced parentheses.

[JayEff]

Thanks! I did not posted the whole query, just a chunk of it. I think I was a little bit dazed by the amount of parentheses. By the way, Platt's first law is absolute. Thanks again.