CODEWITHSUNDEEP
sqlsql-servert-sqlsubquery
Ubaid Ashraf
Ubaid Ashraf

Reputation: 885

Sub Query not working properly

I am trying to insert result of subquery inside another temporary table. SubQuery works fine when i execute that only, but not when as subquery. It throws syntax error. Error is :Incorrect syntax near ')'. It is on first line of subquery

    DECLARE @TempT TABLE
    (
      RowID INT IDENTITY(1, 1) ,
      Date DATETIME ,
      Type NVARCHAR(MAX) ,
      V_No INT ,
      Chq_No INT ,
      Description NVARCHAR(MAX) ,
      Debit MONEY ,
      Credit MONEY ,
      voucher_type_no INT ,
      status NVARCHAR(10) ,
      Clr_Date DATETIME ,
      Voucher_Id INT ,
      Party_Name NVARCHAR(MAX) ,
      DateYYYYMMDD DATETIME
    )

DECLARE @i INT= 1
INSERT  INTO @TempT
        SELECT  *
        FROM    ( SELECT    v.date 'Date' ,
                            vt.voucher_type_shortname 'Type' ,
                            v.voucher_no 'V_No' ,
                            v.cheque_no 'Chq_No' ,
                            a.account_name 'Description' ,
                            ( CASE WHEN SUM(v.amount) > 0 THEN SUM(v.amount)
                                   ELSE 0
                              END ) Debit ,
                            ( CASE WHEN SUM(v.amount) < 0 THEN SUM(-v.amount)
                                   ELSE 0
                              END ) Credit ,
                            v.voucher_type_no ,
                            r.status ,
                            r.recon_date 'Clr_Date' ,
                            MIN(v.voucher_id) Voucher_Id ,
                            '' 'Party_Name' ,
                            CONVERT(VARCHAR(10), r.recon_date, 126) 'Date YYYY-MM-DD'
                  FROM      voucher v
                            LEFT OUTER JOIN reconcilation r ON v.voucher_id = r.voucher_id
                            LEFT OUTER JOIN account a ON v.other_acno = a.account_no ,
                            voucher_type vt
                  WHERE     v.voucher_type_no = vt.voucher_type_no
                            AND v.voucher_type_no > 0
                            AND v.other_acno = a.account_no
                            AND v.acc_year = 51
                            AND v.account_no = 10030
                            AND R.recon_date <= '2015-01-12'
                            AND R.recon_date >= '2009-04-01'
                            AND V.posted IN ( 1, 2 )
                  GROUP BY  v.voucher_no ,
                            v.cheque_no ,
                            v.date ,
                            vt.voucher_type_shortname ,
                            a.account_name ,
                            v.voucher_type_no ,
                            r.status ,
                            r.recon_date
                )

Upvotes: 0

Views: 61

Answers (1)

Sarath Subramanian
Sarath Subramanian

Reputation: 21381

Use a alias name after the last bracket

    DECLARE @TempT TABLE
    (
      RowID INT IDENTITY(1, 1) ,
      Date DATETIME ,
      Type NVARCHAR(MAX) ,
      V_No INT ,
      Chq_No INT ,
      Description NVARCHAR(MAX) ,
      Debit MONEY ,
      Credit MONEY ,
      voucher_type_no INT ,
      status NVARCHAR(10) ,
      Clr_Date DATETIME ,
      Voucher_Id INT ,
      Party_Name NVARCHAR(MAX) ,
      DateYYYYMMDD DATETIME
    )

DECLARE @i INT= 1
INSERT  INTO @TempT
        SELECT  *
        FROM    ( SELECT    v.date 'Date' ,
                            vt.voucher_type_shortname 'Type' ,
                            v.voucher_no 'V_No' ,
                            v.cheque_no 'Chq_No' ,
                            a.account_name 'Description' ,
                            ( CASE WHEN SUM(v.amount) > 0 THEN SUM(v.amount)
                                   ELSE 0
                              END ) Debit ,
                            ( CASE WHEN SUM(v.amount) < 0 THEN SUM(-v.amount)
                                   ELSE 0
                              END ) Credit ,
                            v.voucher_type_no ,
                            r.status ,
                            r.recon_date 'Clr_Date' ,
                            MIN(v.voucher_id) Voucher_Id ,
                            '' 'Party_Name' ,
                            CONVERT(VARCHAR(10), r.recon_date, 126) 'Date YYYY-MM-DD'
                  FROM      voucher v
                            LEFT OUTER JOIN reconcilation r ON v.voucher_id = r.voucher_id
                            LEFT OUTER JOIN account a ON v.other_acno = a.account_no ,
                            voucher_type vt
                  WHERE     v.voucher_type_no = vt.voucher_type_no
                            AND v.voucher_type_no > 0
                            AND v.other_acno = a.account_no
                            AND v.acc_year = 51
                            AND v.account_no = 10030
                            AND R.recon_date <= '2015-01-12'
                            AND R.recon_date >= '2009-04-01'
                            AND V.posted IN ( 1, 2 )
                  GROUP BY  v.voucher_no ,
                            v.cheque_no ,
                            v.date ,
                            vt.voucher_type_shortname ,
                            a.account_name ,
                            v.voucher_type_no ,
                            r.status ,
                            r.recon_date
                ) TAB

Upvotes: 2

Related Questions