Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Case ... end in sqlserver query
Message
 
 
À
22/11/2016 05:07:03
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Versions des environnements
Visual FoxPro:
VFP 9
OS:
Windows 7
Network:
Windows XP
Database:
MS SQL Server
Application:
Desktop
Divers
Thread ID:
01643810
Message ID:
01643812
Vues:
44
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
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform