How can I use variable value in inner join in SQLScript

[wjdsouza]

Hello,

Please direct me to use the value in the mat_table in the inner join in a different SQL.

The mat_table has the value /1MD/MD______038. I want to use the table name in the second query. Currently, I've hard coded. I like to replace with dynamic value.

Thank you,

William

mat_table =

1.       select physical_name

        from

        (

            select   physical_name,

            ROW_NUMBER() OVER ( PARTITION BY physical_name

                       ORDER BY version desc) RANK_COLUMN

            from mdg_gn_tgobj

            where logical_name = 'TCK_MM_MATERIAL' order by version desc

        ) where rank_column = 1;

2nd query as under

tusmd1213 =

        select

            usmd120c.usmd_crequest as usmd_crequest,

            usmd_seqnr,

            "/1MD/MMMATERIAL" as matnr,

            right(usmd_value,13) as usmd_value,

            right(usmd_value,1) as right_val,

            CASE

                when right(usmd_value,1) = '-'

                        then CONCAT(CONCAT( 'MM   USMDKMMMATERIAL               ',usmd120c.usmd_crequest),right(usmd_value,13))

                ELSE         CONCAT(CONCAT( 'MM   USMDKMMMATERIAL               ',usmd120c.usmd_crequest),right(usmd_value,12))

            END as objectid

        FROM usmd120c

--     Select material Only

        inner join usmd1213

        on    usmd1213.usmd_crequest = usmd120c.usmd_crequest

        and   usmd1213.usmd_entity      = 'MATERIAL'

        and   usmd1213.usmd_entity_obj  = 'MATERIAL'

--      Link WITH usmd110c to determine MM only

        inner join Usmd110C

        ON    usmd120c.usmd_creq_type = Usmd110C.usmd_creq_type

        AND   usmd110c.usmd_model   = 'MM'

        and   usmd110c.mandt        = :p_clnt

--      Get the material from the table

       inner join "/1MD/MD______038" as _md038   ( Need to change with dynamic value instead of hardcoded )

        on _md038.USMDKMMMATERIAL = right(usmd_value,13)

      where     usmd120c.usmd_crequest in (   '000000084178')

;