topic Re: Problem with a Query in Application Development and Automation Discussions

Problem with a Query

sadasgreg — Fri, 11 Aug 2023 12:07:03 GMT

Hello,

I have a task and a Query that I've made to it but somehow it doesn't fill requirements, can you spot the mistake here? All what i need to do is filter the tax codes from those tables and based on that, fetch the data from BSEG/BSET table (which works fine) so my guess is, that my query for identyfing particular tax codes doesn't work.

       SELECT t~mwskz,tk~konts

      FROM t007a AS t
      INNER JOIN a003 AS a ON
      t~mwskz = a~mwskz

      INNER JOIN t683s AS ts ON
      a~kschl = ts~kschl

      INNER JOIN t030k AS tk ON
      ts~kvsl1 = tk~ktosl AND
      t~mwskz  = tk~mwskz

       WHERE t~kalsm = 'TAXUS'
       AND   t~mwart = 'V'
       AND   t~zmwsk <> '' 
       AND   a~kappl = 'TX' AND a~aland = 'US'
       AND    ts~kappl = 'TX' AND ts~kalsm = 'TAXUS'
       AND   tk~ktopl = 'CXDE'
      INTO CORRESPONDING FIELDS OF TABLE @it_tax_codes.

requirements:

STEP I – identification of CAM tax codes (T007A-MWSKZ):

• T007A-KALSM = TAXRO

• T007-MWART = V

• T007A-ZMWSK <> empty

STEP II – identification of G/L accounts

o Table A003-KSCHL – to obtain condition types (more than one for one tax code possible!)

Application (KAPPL) = TX

Country (ALAND) = US

Tax code (MWSKZ) = T007A-MWSKZ

Table T683S-KVSL1 – to obtain account key.

Application (KAPPL) = TX

Procedure (KALSM) = TAXUS

Condition type (KSCHL) = A003-KSCHL

Table T030K-KONTS – to obtain G/L accounts.

Processing key (KTOSL) = T683S-KVSL1

Chart of accounts (KTOPL) = CXDE

Re: Problem with a Query

thkolz — Fri, 11 Aug 2023 12:59:10 GMT

Can you replace

AND   t~zmwsk <> ''

AND   t~zmwsk IS NOT INITIAL

Or try to comment out the line and see if it works then.

Re: Problem with a Query

sadasgreg — Fri, 11 Aug 2023 13:53:52 GMT

Thanks for Your comment, actually i tried both and i've got same result. Initially, before i wrote this post i had :

AND   t~zmwsk IS NOT INITIAL

I was thinking to split this query and start with: fetching only mwskz field from t007a table and after that fetch gl accounts from t030k table but as far as i know, the result should be the same as this query anyway. Another part of the program that fetch the data based on Query i've wrote initially looks like this:

so_bukrs, p_monat are parameters taken from selection screen (works fine and show correct values):


    SELECT  bs~gjahr, bs~bukrs,bs~belnr,bs~h_bldat,
            bs~h_budat, bt~mwskz,bt~hwbas, bt~hwste,
            bt~fwste, bt~fwbas, bs~hkont

    FROM bseg AS bs

    INNER JOIN bset AS bt ON
    bs~bukrs = bt~bukrs AND
    bs~belnr = bt~belnr AND
    bs~gjahr = bt~gjahr

    INNER JOIN @it_tax_codes AS i ON
    bs~mwskz = i~mwskz  AND
    bs~hkont = i~konts

   WHERE bs~bukrs IN @so_bukrs
   AND   bt~bukrs IN @so_bukrs
   AND h_monat EQ @p_monat
   AND bs~gjahr EQ @p_gjahr
   AND ( bs~augdt IS INITIAL  OR  bs~augdt > @ld_newdate )

Re: Problem with a Query

Sandra_Rossi — Sat, 12 Aug 2023 11:27:29 GMT

If X is a character column, X IS NOT INITIAL (which exists since 7.53) is equivalent to X <> ''.

Re: Problem with a Query

Sandra_Rossi — Sat, 12 Aug 2023 15:55:04 GMT

Your "query doesn't work", sorry but what "doesn't work" exactly? (provide actual data versus expected data)