Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Case ... end in sqlserver query
Message
 
 
To
22/11/2016 05:07:03
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9
OS:
Windows 7
Network:
Windows XP
Database:
MS SQL Server
Application:
Desktop
Miscellaneous
Thread ID:
01643810
Message ID:
01643812
Views:
43
Try
declare @acc_code1 varchar(7)= '2310001'
declare @f_date1 datetime= '20161101'

select acc_code, SUM(data1.dr) as dr, SUM(data1.cr) as cr,
sum(data1.dr)-sum(data1.cr) as balance,
CASE sign(sum(data1.dr) - sum(data1.cr)) when 1 then 'dr' when -1 then 'cr' else '' end as drcr
 
 from
(
select code as acc_code, SUM(open_dr) as dr,SUM(open_cr) as cr
 from master where acc_code=@acc_code1 group by code
union all
select acc_code, SUM(dr_amount) as dr, SUM(cr_amount) as cr
 from vouchers where acc_code=@acc_code1 and date<@f_date1 group by acc_code
union all
select acc_code, SUM(dr_amount) as dr, SUM(cr_amount) as cr
 from cashsalp where acc_code=@acc_code1 and date<@f_date1 group by acc_code)
as data1
group by acc_code
order by acc_code
>Dear Experts.
>
>I am trying these codes
>
>
>declare @acc_code1 varchar(7)= '2310001'
>declare @f_date1 datetime= '2016-11-01'
>
>select acc_code,SUM(data1.dr)as dr, SUM(data1.cr)as cr,
>sum(data1.dr)-sum(data1.cr)as balance,
>(CASE 
> WHEN SUM(data1.dr)>SUM(data1.cr) THEN drcr='dr'
> WHEN SUM(data1.cr)>SUM(data1.dr) THEN drcr='cr'
> WHEN SUM(data1.cr)=SUM(data1.dr)THEN drcr=''
>  END) 
> from
>(
>select code as acc_code, SUM(open_dr)as dr,SUM(open_cr)as cr
> from master where acc_code=@acc_code1 group by code
>union all
>select acc_code, SUM(dr_amount)as dr,SUM(cr_amount)as cr
> from vouchers where acc_code=@acc_code1 and date<@f_date1 group by acc_code
>union all
>select acc_code,SUM(dr_amount)as dr,SUM(cr_amount)as cr
> from cashsalp where acc_code=@acc_code1 and date<@f_date1 group by acc_code)
>as data1
>group by acc_code
>order by acc_code
>
>
>
>it says:
>
>Msg 102, Level 15, State 1, Line 7
>Incorrect syntax near '='.
>Msg 156, Level 15, State 1, Line 21
>Incorrect syntax near the keyword 'as'.
>I am trying to get a new field called drcr.
>
>Please help
If it's not broken, fix it until it is.


My Blog
Previous
Reply
Map
View

Click here to load this message in the networking platform