Some SQL help please

[Pho3nix]

Hi,

I have 2 SQL scripts. 1 works and the other does not.
Have to rewrite this as the server they are working on is SQL 2000

Any help would be appreciated.

Working :

with cte_p as
(
	SELECT EmployeeId,[PAL],[AAL],
    FROM [dbo].[A1] 
    PIVOT( SUM(CurrentImpliedBalance)
          FOR TypeCode IN ([PAL],[AAL])) AS P
)
select 
		EmployeeId,SUM([PAL]) AS PAL,SUM([AAL]) 
from cte_p where EmployeeId = 3039 group by EmployeeId
order  by 1

Not working

Select EmployeeId
    ,SUM( Case When TypeCode = '[PAL]' Then ISNULL(CurrentImpliedBalance,0) End ) As [PAL]
    ,SUM( Case When TypeCode = '[AAL]' Then ISNULL(CurrentImpliedBalance,0) End ) As [AAL]
From [A1] As P
where EmployeeId = 3039
Group By EmployeeId
ORDER BY 1

 

[Hamster]

Don't you need an ELSE in those case statements?

CASE WHEN code = 'a' THEN 'b' ELSE 'c' END

 

[Pho3nix]

Don't you need an ELSE in those case statements?

CASE WHEN code = 'a' THEN 'b' ELSE 'c' END

Not always. Close the statement with the End.

 

[skimread]

What's not working? Error message or what is the result?

 

[Kosmik]

Why are the columns in the second query surrounded with quotes? This normally indicates a string but if I read your first query correctly those are columns and you are checking typecode against the value in the columns? Maybe I'm wrong but only ever use square brackets for columns with strange characters or match a sql keyword.

 

[Pho3nix]

Why are the columns in the second query surrounded with quotes? This normally indicates a string but if I read your first query correctly those are columns and you are checking typecode against the value in the columns? Maybe I'm wrong but only ever use square brackets for columns with strange characters or match a sql keyword.

And I owe you a beer
Copy paste fail which is why it wasn't doing the sum properly. Thanks

 

[freddster]

Hi,

I have 2 SQL scripts. 1 works and the other does not.
Have to rewrite this as the server they are working on is SQL 2000

Any help would be appreciated.

Working :

with cte_p as
(
	SELECT EmployeeId,[PAL],[AAL],
    FROM [dbo].[A1] 
    PIVOT( SUM(CurrentImpliedBalance)
          FOR TypeCode IN ([PAL],[AAL])) AS P
)
select 
		EmployeeId,SUM([PAL]) AS PAL,SUM([AAL]) 
from cte_p where EmployeeId = 3039 group by EmployeeId
order  by 1

Not working

Select EmployeeId
    ,SUM( Case When TypeCode = '[PAL]' Then ISNULL(CurrentImpliedBalance,0) End ) As [PAL]
    ,SUM( Case When TypeCode = '[AAL]' Then ISNULL(CurrentImpliedBalance,0) End ) As [AAL]
From [A1] As P
where EmployeeId = 3039
Group By EmployeeId
ORDER BY 1

WHAAAAAATTTTTT?????
Can't believe it. Worked 3 years ago at place using 2000. Its ancient.

 

[Hamster]

Not always. Close the statement with the End.

Interesting. What does it use for the else by default? NULL?

 

[Necropolis]

Interesting. What does it use for the else by default? NULL?

I think so...

 

[Necropolis]

And I owe you a beer
Copy paste fail which is why it wasn't doing the sum properly. Thanks

Sometimes the smallest mistakes take the longest to find...

 

[DominionZA]

Interesting. What does it use for the else by default? NULL?

Yup

 

[Kosmik]

And I owe you a beer
Copy paste fail which is why it wasn't doing the sum properly. Thanks

Lol happy to help.