topic Re: SUBSTRING Operation not working for AMDP SQL INNER JOIN Condition. in Application Development and Automation Discussions

SUBSTRING Operation not working for AMDP SQL INNER JOIN Condition.

Sijin_Chandran — Wed, 13 Feb 2019 14:58:29 GMT

Hello ABAP Gurus,

I am not able to use SUBTRING SQL operation in WHERE Clause.

Attaching the screenshot of codes and Data,

Code snippet:

   METHOD get_changed_kunwe BY DATABASE PROCEDURE
                            FOR HDB
                            LANGUAGE SQLSCRIPT
                            OPTIONS READ-ONLY
                            USING cdhdr cdpos.
    ex_t_changd_kunwe = SELECT DISTINCT kunwe
                          FROM :im_t_zsd_catalog_pric AS zsd_catalog_pric
                         INNER JOIN cdhdr AS cdhdr
                            ON zsd_catalog_pric.kunwe = substring(cdhdr.objectid,1,10)
                         INNER JOIN cdpos AS cdpos
                            ON cdhdr.objectclas = cdpos.objectclas
                           AND cdhdr.objectid   = cdpos.objectid
                           AND cdhdr.changenr   = cdpos.changenr
                         WHERE cdhdr.mandant    = session_context('CLIENT')
                           AND cdhdr.objectclas = 'DEBI'
*                           AND (cdhdr.udate <= :im_from_date   --From Date
*                            AND cdhdr.utime <= :im_from_time   --From Time
*                            AND cdhdr.udate >= :im_to_date     --To Date
*                            AND cdhdr.utime >= :im_to_time)    --To Time
                            AND cdhdr.change_ind = 'U'
                            AND cdpos.tabname = 'KNA1'
                            AND cdpos.fname   = 'ZZ_CATALOG';
   ENDMETHOD."get_changed_kunwe

KUNWE is of size 10 ( CHAR10 ) and CDHDR~OBJECTID CHAR90.

I have checked data is there for the remaining WHERE Clause.

Its the JOIN where its failing

Can anyone please suggest where I am missing.

Thanks,

Sijin

Re: SUBSTRING Operation not working for AMDP SQL INNER JOIN Condition.

Sijin_Chandran — Wed, 13 Feb 2019 15:09:39 GMT

My desperation to find the answer or my luck I don't know.

I just now found the Solution 🙂

For INNER JOIN always use SUBSTR function instead of SUBSTRING.

   METHOD get_changed_kunwe BY DATABASE PROCEDURE
                            FOR HDB
                            LANGUAGE SQLSCRIPT
                            OPTIONS READ-ONLY
                            USING cdhdr cdpos.
    ex_t_changd_kunwe = SELECT DISTINCT kunwe
                          FROM :im_t_zsd_catalog_pric AS zsd_catalog_pric
                         INNER JOIN cdhdr AS cdhdr
                            ON zsd_catalog_pric.kunwe = substr(cdhdr.objectid,1,10)
                         INNER JOIN cdpos AS cdpos
                            ON cdhdr.objectclas = cdpos.objectclas
                           AND cdhdr.objectid   = cdpos.objectid
                           AND cdhdr.changenr   = cdpos.changenr
                         WHERE cdhdr.mandant    = session_context('CLIENT')
                           AND cdhdr.objectclas = 'DEBI'
*                           AND (cdhdr.udate <= :im_from_date   --From Date
*                            AND cdhdr.utime <= :im_from_time   --From Time
*                            AND cdhdr.udate >= :im_to_date     --To Date
*                            AND cdhdr.utime >= :im_to_time)    --To Time
                            AND cdhdr.change_ind = 'U'
                            AND cdpos.tabname = 'KNA1'
                            AND cdpos.fname   = 'ZZ_CATALOG';
   ENDMETHOD."get_changed_kunwe

Really strange, I was not even getting any Exceptions raised also for this, so it was difficult to find out that this was the actual error.

Hope it would be helpful for others.

Thanks,

Sijin