Forum Discussion

Brooks's avatar
Brooks
New Contributor III
3 days ago
Solved

SQL in BR gets syntax errors, but runs from Data Adapter

Has anybody seen this error?  If so, how did you fix it? I can run the SQL query in question from a Data Adapter just fine, but when trying to run it from a BR I get the above error.  At this ...
Connectors
  • Brooks's avatar
    Brooks
    2 days ago

    I just figured out the answer to my own question, as you were typing your response.  Thank you for responding.  As it so happens, you cannot have any spaces at the end of a line when calling the SQL from a business rule with Progress, nor can you have comments.  This is different from other SQL databases.
    With most SQL I can do this:
    sqlQuery.Append(" SELECT                                                                                                      ")
    sqlQuery.Append("    GL.GLCode AS Account,                                                                                    ")
    sqlQuery.Append("    IFNULL(CostCentre.CostCentreCode,'') AS CostCenter,                                                      ")
    sqlQuery.Append("    --CAST(IncomeStatement.OpeningBalance AS DECIMAL(38, 2)) AS OpeningBalance,                              ")
    sqlQuery.Append("    --CAST(IncomeStatement.PeriodActivity AS DECIMAL(38, 2)) AS PeriodActivity,                              ")
    sqlQuery.Append("    CAST((IncomeStatement.OpeningBalance + IncomeStatement.PeriodActivity) AS DECIMAL(38, 2)) AS Balance     ")
    imagine that this window is wide enough that the end ") all line up vertically.

    With Progress I can only do this:
    sqlQuery.Append(" SELECT")
    sqlQuery.Append("    GL.GLCode AS Account,")
    sqlQuery.Append("    IFNULL(CostCentre.CostCentreCode,'') AS CostCenter,")

    The closing brackets must be right up against the end of the line.  It is a bit harder on the eyes.

    I prefer the former because it is easier to extract the SQL and put it into a data adapter to monkey with if you want to experiment, but c'est la vie.

RobbSalzmann's avatar
RobbSalzmann
Valued Contributor II
2 days ago

Very difficult to help or troubleshoot without seeing the actual code.  The error suggests you have an extra trailing comma somewhere in your SQL.

Brooks's avatar
Brooks
New Contributor III
2 days ago

I just figured out the answer to my own question, as you were typing your response.  Thank you for responding.  As it so happens, you cannot have any spaces at the end of a line when calling the SQL from a business rule with Progress, nor can you have comments.  This is different from other SQL databases.
With most SQL I can do this:
sqlQuery.Append(" SELECT                                                                                                      ")
sqlQuery.Append("    GL.GLCode AS Account,                                                                                    ")
sqlQuery.Append("    IFNULL(CostCentre.CostCentreCode,'') AS CostCenter,                                                      ")
sqlQuery.Append("    --CAST(IncomeStatement.OpeningBalance AS DECIMAL(38, 2)) AS OpeningBalance,                              ")
sqlQuery.Append("    --CAST(IncomeStatement.PeriodActivity AS DECIMAL(38, 2)) AS PeriodActivity,                              ")
sqlQuery.Append("    CAST((IncomeStatement.OpeningBalance + IncomeStatement.PeriodActivity) AS DECIMAL(38, 2)) AS Balance     ")
imagine that this window is wide enough that the end ") all line up vertically.

With Progress I can only do this:
sqlQuery.Append(" SELECT")
sqlQuery.Append("    GL.GLCode AS Account,")
sqlQuery.Append("    IFNULL(CostCentre.CostCentreCode,'') AS CostCenter,")

The closing brackets must be right up against the end of the line.  It is a bit harder on the eyes.

I prefer the former because it is easier to extract the SQL and put it into a data adapter to monkey with if you want to experiment, but c'est la vie.

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    2 days ago

    Nice catch! Do yourself a favor and stop using StringBuilder for sql queries.  This is a poor coding example set by OneStream. 

    Write better, more readable code by simply using a String for your nicely formatted SQL query:

    Dim sqlQuery As String =
    "SELECT
        GL.GLCode AS Account,
        IFNULL(CostCentre.CostCentreCode,'') AS CostCenter"

    also, consider using the preferred, non-dialectic COALESCE instead of IFNULL:

    Dim sql As String =
"SELECT
    GL.GLCode AS Account,
    COALESCE(CostCentre.CostCentreCode, '') AS CostCenter"

     

    • Brooks's avatar
      Brooks
      New Contributor III
      2 days ago

      So then I could still insert variables and be done with it?

      "SELECT   
      crpdta.F0902.GBLT,  crpdta.F0902.GBCO,  crpdta.F0902.GBSBL,  crpdta.F0902.GBOBJ,  crpdta.F0902.GBSUB,  crpdta.F0902.GBMCU, 
      CASE
        WHEN '" & wfMonth.ToString & "' = '1' THEN  crpdta.F0902.GBAN01
        WHEN '" & wfMonth.ToString & "' = '2' THEN ( crpdta.F0902.GBAN01 +  crpdta.F0902.GBAN02)
        WHEN '" & wfMonth.ToString & "' = '3' THEN ( crpdta.F0902.GBAN01 +  crpdta.F0902.GBAN02 +      crpdta.F0902.GBAN03)  etc."

      Wow.  Thanks!