Click here to Skip to main content
16,004,778 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi
SQL
select  zee.TransId,zee.amount from OJDT t1
inner join
(

                SELECT
                    ojdt.TransId,
                    SUM(JDT1.Debit * 1 /100) as amount --
                FROM OJDT
                    INNER JOIN JDT1 ON JDT1.TransId = OJDT.TransId
                    INNER JOIN OACT ON OACT.AcctCode = JDT1.Account
                    INNER JOIN
                            (
                                SELECT
                                    AcctCode, AcctName, Head1Code, Head4Code, AcctTypeCode, Head1,
                                    CASE
                                        WHEN Head1Code='5140001' THEN 124
                                        WHEN Head1Code='5140002' THEN 188
                                        WHEN Head1Code='5140003' THEN 195
                                        WHEN Head1Code='511001'  THEN 104
                                        WHEN Head1Code='513002'  THEN 124
                                        WHEN Head1Code='513001'  THEN 104
                                        WHEN Head1Code='512021'  THEN 199
                                        WHEN Head1Code='511003'  THEN 108
                                        WHEN Head1Code='512012'  THEN 109
                                        WHEN Head1Code='512013'  THEN 110
                                        WHEN Head1Code='512014'  THEN 115
                                        WHEN Head1Code='512015'  THEN 112
                                        WHEN Head1Code='511002'  THEN 106
                                        ELSE ''
                                    END ItmsGrpCod
                                FROM
                                    DAccount
                            ) D1 ON D1.AcctCode = JDT1.ShortName
                    INNER JOIN OITB ON OITB.ItmsGrpCod = D1.ItmsGrpCod
                WHERE
                    OJDT.TaxDate BETWEEN '2013-12-01' AND '2013-12-31'
                    AND JDT1.Account NOT IN ('_SYS00000001371','_SYS00000006981','_SYS00000006876')
                    AND OJDT.TransCode IN ('JV')
                    AND OJDT.ObjType = '30'
                group by
                    OJDT.TransId
    ) zee on zee.TransId = t1.TransId
    where
        T1.TaxDate BETWEEN '2014-01-01' AND '2014-01-31'

wanted to insert this query in declared function which is @Tax it is generating error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
PLz Help
Posted
Comments
Vedat Ozan Oner 13-Feb-14 3:34am    
do you have any error in this query?
xibit89 14-Feb-14 5:36am    
Nope query is tested and works Fine..
xibit89 14-Feb-14 5:37am    
I have solved it my self have a look in solution..

1 solution

SQL
declare @Tax numeric


SQL
select @Tax = zee.amount from OJDT t1
inner join
(

                SELECT
                    ojdt.TransId,
                    SUM(JDT1.Debit * 1 /100) as amount --
                FROM OJDT
                    INNER JOIN JDT1 ON JDT1.TransId = OJDT.TransId
                    INNER JOIN OACT ON OACT.AcctCode = JDT1.Account
                    INNER JOIN
                            (
                                SELECT
                                    AcctCode, AcctName, Head1Code, Head4Code, AcctTypeCode, Head1,
                                    CASE
                                        WHEN Head1Code='5140001' THEN 124
                                        WHEN Head1Code='5140002' THEN 188
                                        WHEN Head1Code='5140003' THEN 195
                                        WHEN Head1Code='511001'  THEN 104
                                        WHEN Head1Code='513002'  THEN 124
                                        WHEN Head1Code='513001'  THEN 104
                                        WHEN Head1Code='512021'  THEN 199
                                        WHEN Head1Code='511003'  THEN 108
                                        WHEN Head1Code='512012'  THEN 109
                                        WHEN Head1Code='512013'  THEN 110
                                        WHEN Head1Code='512014'  THEN 115
                                        WHEN Head1Code='512015'  THEN 112
                                        WHEN Head1Code='511002'  THEN 106
                                        ELSE ''
                                    END ItmsGrpCod
                                FROM
                                    DAccount
                            ) D1 ON D1.AcctCode = JDT1.ShortName
                    INNER JOIN OITB ON OITB.ItmsGrpCod = D1.ItmsGrpCod
                WHERE
                    OJDT.TaxDate BETWEEN '2013-12-01' AND '2013-12-31'
                    AND JDT1.Account NOT IN ('_SYS00000001371','_SYS00000006981','_SYS00000006876')
                    AND OJDT.TransCode IN ('JV')
                    AND OJDT.ObjType = '30'
                group by
                    OJDT.TransId
    ) zee on zee.TransId = t1.TransId
    where
        T1.TaxDate BETWEEN '2014-01-01' AND '2014-01-31'
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900