none
WHERE Predicate line works in Aqua Data Studio, not in Report Builder

    Question

  • I’m trying to get a query working in report builder which will pull data back when run in Aqua Data Studio.  It returns a NULL set when working with the same data source from report builder.  Here is the WHERE clause:

      --WHERE SYSDATE < :REM_DATE  -- troubleshooting line (=DateAdd("d", 3, Now()))

      WHERE SYSDATE < TO_DATE(TO_CHAR(TPOA.POST_DT, 'MM/DD/YYYY'), 'MM/DD/YYYY') + 3

        AND TPOA.DEST_OTHER IS NOT NULL

        AND TPOA.AUTH_STATUS_IND = 'A'

    The commented line works in Report Builder.  :REM_DATE has as its default expression =DateAdd(“d”, 3, Now()), used as a means of troubleshooting.  There isn’t a way to populate :REM_DATE before use because forward dependencies aren’t allowed.

    Also tried is this line for the first part of the WHERE:

    WHERE SYSDATE < TPOA.POST_DT + 3

    As well, it works in AD but not in RB.

    Here is the full query, which is the 2<sup>nd</sup> query in a UNION:

      SELECT DISTINCT

        TO_CHAR(TPOA.MATL_ASGN_DT, 'MM/DD/YYYY') AS MATL_ASGN_DT

        ,TPOA.TE_24_NBR

        ,TPOA.POST_DT AS TE24_DATE

        ,SUBSTR(TPOA.DEST_PRODR_SUPP_CD, 1, 5) AS DEST_PS_CD

        ,'N' AS DEST_PO_IND

        ,SUBSTR(TPOA.SRC_PRODR_SUPP_CD, 1, 5) AS SRC_PS_CD

        ,SUBSTR(TPOA.SRC_PRODR_SUPP_CD, 7, 2) AS SRC_PS_CD_SFX

        ,TPOA.LPA_IND

        ,SUBSTR(TPOA.DEST_PRODR_SUPP_CD, 7, 2) AS DEST_PS_CD_SFX

        ,SUBSTR(TPOA.DEST_CONT_ALT_ID, 1, 2) AS DEST_PROJ_YR

        ,SUBSTR(TPOA.DEST_CONT_ALT_ID, 3, 4) AS DEST_PROJ_SEQ

        ,TPOA.DEST_OTHER

        ,TPS.PRODR_SUPP_NM AS ABBR_NAME

        ,'SM' AS FRM_IND

      FROM VWARE.T_POST_ASG TPOA

        LEFT OUTER JOIN SMDBA.T_PRODR_SUPP TPS

            ON TPOA.SRC_PRODR_SUPP_CD = TPS.PRODR_SUPP_CD

      --WHERE SYSDATE < :REM_DATE  -- troubleshooting line (=DateAdd("d", 3, Now()))

      WHERE SYSDATE < TO_DATE(TO_CHAR(TPOA.POST_DT, 'MM/DD/YYYY'), 'MM/DD/YYYY') + 3

        AND TPOA.DEST_OTHER IS NOT NULL

        AND TPOA.AUTH_STATUS_IND = 'A'


    Michael Durthaler

    Friday, August 15, 2014 5:01 PM

All replies

  • how are you passing the parameters from the report?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, August 15, 2014 5:37 PM
  • Hi Michael,

    What's the version of your Report Builder? When you run the query in query designer of Report Builder, have you got any error/warning message?

    In this case, I would suggest you can try to SQL Server Profiler to capture some events do further investigation while run the query in Report Builder.

    If you have any feedback on our support, please click here.

    Regards,


    Elvis Long
    TechNet Community Support

    Tuesday, August 19, 2014 6:13 AM