topic Re: Regarding SQL ... in Application Development and Automation Discussions

Regarding SQL ...

Former Member — Thu, 22 Dec 2005 20:39:32 GMT

Hello ABAP gurus,

Please help me out in solving following doubt.

Fieldname: Tax-code1 (STCD1), Tax-code2( STCD2), Vendor number(LIFNR)

Table name: LFA1.

I am working on uniqueness for tax code verification for vendors.

Lets say that tax code1 (STCD1) value for a particular vendor is in v_tax1 and tax-code2 (STCD2) value for vendor is in v_tax2.

I want to check tax-code1 value for this vendor number (LIFNR) against tax-code1 values for all the other vendors numbers in table LFA1.

Hence in simple terms I want to see if value of v_tax1 exists for any other vendor's Tax-code1 scanning the whole LFA1 for all the other vendors and comparing with their respective taxcode1s (STCD1).

If an entry exists then raise error.

Same thing I want to do for tax-code2, tax-code3..

Please let me know how do I perform this using SQL ?

Relevant points will be awarded..

Re: Regarding SQL ...

Former Member — Thu, 22 Dec 2005 20:54:50 GMT

Any help will be appreciated...

Re: Regarding SQL ...

Former Member — Thu, 22 Dec 2005 20:55:29 GMT



DATA: BEGIN OF i_lfa1 OCCURS 0,
      lifnr LIKE lfa1-lifnr,
      stcd1 LIKE lfa1-stcd1,
      END OF i_lfa1,

      i_lfa1_dup LIKE i_lfa1 OCCURS 0.


SELECT lifnr stcd1
FROM lfa1
INTO TABLE i_lfa1.

i_lfa1_dup[] = i_lfa1[].

LOOP AT i_lfa1.

  LOOP AT i_lfa1_dup WHERE lifnr NE i_lfa1-lifnr.

    IF i_lfa1_dup-stcd1 = i_lfa1-stcd1.

      WRITE: / 'Duplicate Tax Code ',
               i_lfa1_dup-stcd1,
               ' for vendors ',
               i_lfa1-lifnr,
               ' and ',
               i_lfa1_dup-lifnr.
    ENDIF.

  ENDLOOP.

ENDLOOP.

Re: Regarding SQL ...

Former Member — Thu, 22 Dec 2005 20:56:16 GMT

But there is a better way than the above mentioned!

Message was edited by: Sam

Re: Regarding SQL ...

Former Member — Thu, 22 Dec 2005 20:57:25 GMT

Can you please let me know.

Re: Regarding SQL ...

Former Member — Thu, 22 Dec 2005 21:02:11 GMT

Ok I am working on that and will get back to you once I am sure it is efficient than the posted answer...

Re: Regarding SQL ...

Former Member — Thu, 22 Dec 2005 21:18:56 GMT

You can perhaps do this using a subquery in the select stmt.

select lifnr stcd1 from lfa1 as a

into table itab

where lifnr in s_lifnr

and exists ( select stcd1 from lfa1 as b

where b_{lifnr <> a}lifnr

and b_{stcd1 = a}stcd1 ).

Regards

Kalpana

Message was edited by: Kalpana Tyagi