Highlighted
Absent Member.
Absent Member.
1453 views

Exception: Expected Identifier or quoted identifier

Jump to solution

Hi, Can anybody help me out in getting this query to work? I've tested it on the sql command line and it works perfectly but when I try to run the project it produces that error in the post title, I am using Oracle database.

      

accounts-balances-totals.

      

    set customer-id to type Int64::Parse(report-term::ToString()).

      

    exec sql declare accounts_totals_balances dataset cursor for

      

         select credit_account as account_id, sum(credit) as credit_balance, sum(debit) as debit_balance, sum(credit) - sum(debit) as balance

      

         from

      

         (

      

           select credit_account, transaction_id, transaction_date, amount as credit, 0 as debit

      

           from bank_transaction

      

           union

      

           select debit_account, transaction_id,transaction_date, 0 as credit, amount as debit

      

           from bank_transaction

      

         ) where credit_account in (select account_id from customers_accounts where customer_id = :customer-id)

      

         group by credit_account

      

    end-exec.

      

   

      

    exec sql

      

        open accounts_totals_balances

      

    end-exec.

      

   

      

    exec ado

      

        get cursor accounts_totals_balances into :balance-sheet-set

      

            rename datatable as accounts_totals

      

    end-exec.

      

   

      

    set result-set to balance-sheet-set::Copy.

      

   

      

    exec sql

      

        close accounts_totals_balances

      

    end-exec.

Thanks

0 Likes
1 Solution

Accepted Solutions
Highlighted
Absent Member.
Absent Member.

Thank you dmccann, I've moved the headache to the database 😉 by creating a view of the upper query and selecting whatever I want from it but, the view query still needs optimization!

Regards

View solution in original post

0 Likes
2 Replies
Highlighted
Absent Member.
Absent Member.

Since the query runs ok in sql manager, it must be in the oher code not in the select statement.  Could it be the EXEC ADO on the "Get Cursor" statement that you are using where you are using EXEC SQL everywhere else? Or should you be using the "Fetch Cursor" statement rather than the "Get Cursor"?

0 Likes
Highlighted
Absent Member.
Absent Member.

Thank you dmccann, I've moved the headache to the database 😉 by creating a view of the upper query and selecting whatever I want from it but, the view query still needs optimization!

Regards

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.