Missing expression

Posted by: boivsam on 4 August 2017, 3:13 pm EST

  • Posted 4 August 2017, 3:13 pm EST

    Hi,


    I'm receiving an error when I run my report using the RPX way.


    It says there's a missing expression in my query but if I paste it in a external SQL editor and change my parameters (ex: <%debut%>) for a value (ex: 2008-01-01), it works fine.


    I doubled checked for parameters names and everything looks fine. Is there a possibility my parameters aren't replaced somehow ? Is there a way to watch the SQL query after its parameters were replace by a value ?


    In the script of my RPX report there are simple things like matching a label height with the corresponding field size (for the borders to ajust) or setting the current date in a label of the PageHeader.


    In addition, my query is made with an UNION of two SELECTs and some of the parameters appear more that once.


     


    Here is my VB form code behind :


    Private Sub Form_Load()
        Dim rptXML As ActiveReport
       
        Set rptXML = New ActiveReport
        rptXML.LoadLayout App.Path & "\rptRapportSommaireCommentaire.rpx"
        rptXML.ShowParameterUI = False
        rptXML.Sections("Detail").Controls("DataControl1").ConnectionString = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=XXXXX;Password=XXXXX;Data Source=XXXXX"
        rptXML.Parameters("ua").Value = "'1541301', '1557106', '1557107', 'A154000'"
        rptXML.Parameters("debut").Value = "2008-01-01"
        rptXML.Parameters("fin").Value = "2008-12-31"
       
        rptXML.ScriptDebuggerEnabled = True
       
        rptXML.Run


        Set viewer.ReportSource = rptXML
    End Sub


    *XXXXX : to hide my USER/PASSWD/DB


    Please help,  Thanks !

  • Replied 4 August 2017, 3:13 pm EST

    Will # work with a date format like 2008-01-01 ? Cause I don't want to reformat my date before setting them in my report.


    Thanks

  • Replied 4 August 2017, 3:13 pm EST

    Your test does work well.


    My paramters are set the same way they are in your example with <%%>, except that my parameters for start & end dates (<%debut%> & <%fin%>) are single quoted in my query like :


      AND    PLT.DAT_RECPT >= '<%debut%>'
      AND    PLT.DAT_RECPT <= '<%fin%>'


    The value received is always unquoted like : 2008-01-01, so the result should be PLT.DAT_RECPT >= '2008-01-01'.


    I have another report where do the same trick for date and it works perfect. May be my problem does not origin from the params.


    An idea of what else could cause the issue of Missing expression ?


    Thanks !

  • Replied 4 August 2017, 3:13 pm EST

    Thank you for your question. Unfortunately, I have been unable to reduce the described behavior. The attached sample works well in my testing using parameters and the latest build available (1309). Please let me know if you do not receive similar results.
    You can find the latest build available here:

    http://www.datadynamics.com/forums/37/ShowForum.aspx

    Thanks,
        Eric


    2008/11/TestParameters.zip
  • Replied 4 August 2017, 3:13 pm EST

    The parameter value is 2008-11-26 and the DB value is 2008-11-26.


    Like I've said earlier, the problem might not come from the parameters because i get the error even if the parameter is replaced with a hardcoded value !


    Plus, my 4 other projects are working perfectly and I am using the same approach for all of them. The only difference : the one in case here does not run. And those 4 project have parameters as well.


    I might have a new lead on the problem : this report is the only one where I do an UNION in the source query. Could this, with a RPX report, be the cause of my error ?


    Thanks !

  • Replied 4 August 2017, 3:13 pm EST

    Thank you for your reply. In my testing you will need to format the parameter value as it is formatted in the DataBase. Do you experience the desired results when formatting the parameter as it is formatted in the database?

    Thanks,
        Eric

  • Replied 4 August 2017, 3:13 pm EST

    Thank you for your reply. In my testing using a # in my query to signify the parameter was a date worked well. Please let me know if you do not receive similar results.

    Ex:
        rpt.DataControl1.Source = "select * from orders where orderDate = #<%OD%># and shippeddate = #<%SD%>#"
        rpt.Parameters("OD").Value = "8/4/94"
        rpt.Parameters("SD").Value = "8/16/94"

    Thanks,
        Eric

  • Replied 4 August 2017, 3:13 pm EST

    Hi,


    In order to check if the params are the cause of my problem, I tried to remove all the parameters of my report's source and I still get the missing expression error !


    What else could cause this error except the parameters ?


     


    Edit :


    Byt he way, an older version of the project with the exactly same query (but with a .DSR report instead of .RPX) was working. Now that i have converted my VB code behind in VB script and saved my RPX report, the error shows up.


    I've done this conversion from DSR to RPX four times earlier today with other projects and they work fine ! There is only this one that does not. And I've tried converting it twice.


    Please help me with this cause i don't have any clue why it's not working like the other 4 projects i did !

  • Replied 4 August 2017, 3:13 pm EST

    The date fields are of type DATE.


     


    The query is the following (sorry for the french names)  :


    SELECT   UA.NOM_UNITE_ADMNS "Nom UA",
             CATG.DES_COMPL "Catégorie",
             SCAT.DES_COMPL "Sous-catégorie",
             COUNT(PLT.DAT_RECPT) "Nb reçus",
             COUNT(CASE STAT.COD_TYPE_STAT
                      WHEN 'FE' THEN
                         STAT.DAT_DEBUT_APPLQ
                      ELSE
                         NULL
                   END)
             "Nb fermés",
             AVG(F_GPLB_OBTNR_DELAI_REPNS (PLT.DAT_RECPT,
                                           CASE STAT.COD_TYPE_STAT
                                              WHEN 'FE' THEN
                                                 STAT.DAT_DEBUT_APPLQ
                                              ELSE
                                                 NULL
                                           END,
                                          '<%fin%>') ) "Délai de fermeture"
            
    FROM     BGR_V_UNITE_ADMNS UA,
             GPL_PLANT PLT,
             GPL_STAT_PLANT STAT,
             GPL_TYPE_CATGR_DEM_RENSG CATG,
             GPL_TYPE_CATGR_DEM_RENSG SCAT
            
    WHERE    (UA.COD_NIV_HIERC_1 = <%cod_niv_1%> OR <%cod_niv_1%> IS NULL)
      AND    (UA.COD_NIV_HIERC_2 = <%cod_niv_2%> OR <%cod_niv_2%> IS NULL)
      AND    (UA.COD_NIV_HIERC_3 = <%cod_niv_3%> OR <%cod_niv_3%> IS NULL)
      AND    (UA.COD_NIV_HIERC_4 = <%cod_niv_4%> OR <%cod_niv_4%> IS NULL)
      AND    PLT.IDE_TYPE_DEM = 4
      AND    PLT.DAT_RECPT >= '<%debut%>'
      AND    PLT.DAT_RECPT <= '<%fin%>'
      AND    PLT.COD_NIV_HIERC_1_EVENM = UA.COD_NIV_HIERC_1
      AND    PLT.COD_NIV_HIERC_2_EVENM = UA.COD_NIV_HIERC_2
      AND    PLT.COD_NIV_HIERC_3_EVENM = UA.COD_NIV_HIERC_3
      AND    PLT.COD_NIV_HIERC_4_EVENM = UA.COD_NIV_HIERC_4
      AND    PLT.DAT_UNITE_ADMNS_EVENM = UA.DAT_DEBUT_APPLQ
      AND    PLT.IDE_PLANT = STAT.IDE_PLANT
      AND    STAT.DAT_DEBUT_APPLQ <= TRUNC(SYSDATE)
      AND    (STAT.DAT_FIN_APPLQ IS NULL OR STAT.DAT_FIN_APPLQ > TRUNC(SYSDATE))
     
      AND    (
                (
                   -- Dans le cas où la plainte = catégorie supérieure.
                   PLT.COD_TYPE_CATGR_DEM_RENSG = SCAT.COD_TYPE_CATGR_DEM_RENSG
                   AND
                   PLT.COD_TYPE_CATGR_DEM_RENSG = CATG.COD_TYPE_CATGR_DEM_RENSG
                   AND
                   SCAT.COD_TYPE_CATGR_DEM_SUPR IS NULL
                )
                OR
                (
                   -- Dans le cas où la plainte = sous-catégorie.
                   PLT.COD_TYPE_CATGR_DEM_RENSG = SCAT.COD_TYPE_CATGR_DEM_RENSG
                   AND
                   CATG.COD_TYPE_CATGR_DEM_RENSG = SCAT.COD_TYPE_CATGR_DEM_SUPR
                )
             )
     
    GROUP BY UA.NOM_UNITE_ADMNS,
             CATG.DES_COMPL,
             SCAT.DES_COMPL,
             STAT.COD_TYPE_STAT
            
    UNION


    SELECT   UA.NOM_UNITE_ADMNS "Nom UA",
             'Aucune (PES)' "Catégorie",
             'Aucune (PES)' "Sous-catégorie",
             COUNT(PLT.DAT_RECPT) "Nb reçus",
             COUNT(CASE STAT.COD_TYPE_STAT
                      WHEN 'FE' THEN
                         STAT.DAT_DEBUT_APPLQ
                      ELSE
                         NULL
                   END)
             "Nb fermés",
             AVG(F_GPLB_OBTNR_DELAI_REPNS (PLT.DAT_RECPT,
                                           CASE STAT.COD_TYPE_STAT
                                              WHEN 'FE' THEN
                                                 STAT.DAT_DEBUT_APPLQ
                                              ELSE
                                                 NULL
                                           END,
                                          '<%fin%>') ) "Délai de fermeture"
            
    FROM     BGR_V_UNITE_ADMNS UA,
             GPL_PLANT PLT,
             GPL_STAT_PLANT STAT
            
    WHERE    (UA.COD_NIV_HIERC_1 = <%cod_niv_1%> OR <%cod_niv_1%> IS NULL)
      AND    (UA.COD_NIV_HIERC_2 = <%cod_niv_2%> OR <%cod_niv_2%> IS NULL)
      AND    (UA.COD_NIV_HIERC_3 = <%cod_niv_3%> OR <%cod_niv_3%> IS NULL)
      AND    (UA.COD_NIV_HIERC_4 = <%cod_niv_4%> OR <%cod_niv_4%> IS NULL)
      AND    PLT.IDE_TYPE_DEM = 4
      AND    PLT.DAT_RECPT >= '<%debut%>'
      AND    PLT.DAT_RECPT <= '<%fin%>'
      AND    PLT.COD_NIV_HIERC_1_EVENM = UA.COD_NIV_HIERC_1
      AND    PLT.COD_NIV_HIERC_2_EVENM = UA.COD_NIV_HIERC_2
      AND    PLT.COD_NIV_HIERC_3_EVENM = UA.COD_NIV_HIERC_3
      AND    PLT.COD_NIV_HIERC_4_EVENM = UA.COD_NIV_HIERC_4
      AND    PLT.DAT_UNITE_ADMNS_EVENM = UA.DAT_DEBUT_APPLQ
      AND    PLT.IDE_PLANT = STAT.IDE_PLANT
      AND    STAT.DAT_DEBUT_APPLQ <= TRUNC(SYSDATE)
      AND    (STAT.DAT_FIN_APPLQ IS NULL OR STAT.DAT_FIN_APPLQ > TRUNC(SYSDATE))
      AND    PLT.COD_TYPE_CATGR_DEM_RENSG IS NULL
     
    GROUP BY UA.NOM_UNITE_ADMNS,
             'Aucune catégorie',
             'Aucune sous-catégorie'
            
    ORDER BY 1,
             2,
             3

  • Replied 4 August 2017, 3:13 pm EST

    Thanks for the tip about DBMS trace ! I could get the cause of the error with that.


    My DBA activated a trace and I was able to get the final query of the report.


    The error was never in the parameters themselves. It is even more stupid : As you can see in the posted query, there are comments made with -- and the query stands on many lines. When i got the trace from the DBA, the query was standing in a single line. This means the comments were hiding the end of the query hence the Missing Expression !


    In short, it looks like :


    In editor (on many lines) :


    SELECT *
    --Get values in table 1
    FROM table


    While executing (on a single line) :


    SELECT * --Get values in table 1 FROM table
    Missing expression after the *

  • Replied 4 August 2017, 3:13 pm EST

    Thank you for your reply. Would it be possible to examine the logs of your DBMS to see what the text of the incorrect passed query was? Once you have this text you should be able to run it independently against your database to determine what area of the query the issue resides. Also, please note all ActiveReports does when using parameters is simple string substitution before the query is run against the database.

    Alternatively you may want to try populating an ADO recordset (taking ActiveReports out of the data connectivity picture) to ensure the query will indeed return the desired results. If successful you could then assign the recordset to the recordset property of the ActiveReports data control.

    Thanks,
        Eric

  • Replied 4 August 2017, 3:13 pm EST

    Thank you for your reply. What data type is your date field in your database? Also could you please provide me with the query you are attempting to run?

    Thanks,
        Eric

  • Replied 4 August 2017, 3:13 pm EST

    Thank you for your reply. I’m happy to hear you have diagnosed the cause of the issue. Please let me know if I can be of further assistance. Thank you!

        Eric

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels