topic Re: Comparing fields in where condition from same ztable in Application Development and Automation Discussions

Comparing fields in where condition from same ztable

rahulnavandar — Tue, 15 May 2007 08:29:39 GMT

Hi All,

I have two fields (gr_date and gm_date) in ztable.

i have to compare the records(i.e gm_date > gr_date) in one select where condition and take that in internal table.

SELECT * FROM ztable

INTO TABLE it_mtolog_gm

WHERE werks = p_werks

AND <b>gm_qty < ztable-gr_qty</b>.

Is it possible? Bcoz when i tried this it was not fetching any value.

Or please give any other suggestion.

Re: Comparing fields in where condition from same ztable

Former Member — Tue, 15 May 2007 08:35:20 GMT

Hi,

SELECT * FROM ztable

INTO TABLE it_mtolog_gm

WHERE werks = p_werks

AND gm_qty < ( Select ztable-gr_qty from ZTABLE into l_qty where werks = p_werks).

Best regards,

Prashant

Re: Comparing fields in where condition from same ztable

Former Member — Tue, 15 May 2007 08:35:45 GMT

try this..

SELECT * FROM ztable
INTO TABLE it_mtolog_gm
WHERE werks = p_werks.

delete it_mtolog_gm  where gm_qty LE gr_qty.

Message was edited by:

Chandrasekhar Jagarlamudi

Re: Comparing fields in where condition from same ztable

Former Member — Tue, 15 May 2007 08:43:04 GMT

It would be better to select the entries in a table based on the WERKS condition alone, and then loop at the table to delete entries where gm_qty > gr_qty.


SELECT * FROM ztable
INTO TABLE it_mtolog_gm
WHERE werks = p_werks. 

LOOP AT it_mtolog_gm.
CHECK it_mtolog_gm-gm_qty < it_mtolog_gm-gr_qty.
DELETE it_mtolog_gm.
CONTINUE.
ENDLOOP.

Using select alone, you might first need to populate an itab with all the values in the DB, and then use FOR ALL ENTRIES to select again from the same table.

Hope this helps.

Sudha

Re: Comparing fields in where condition from same ztable

Former Member — Tue, 15 May 2007 09:00:40 GMT

HI rahul...

You don;t need to write the table name in the wherecondition if you are comparing the values from a single table... just write the fieldname....

I think that will do...

Regards,

Jayant

Re: Comparing fields in where condition from same ztable

Former Member — Tue, 15 May 2007 09:09:54 GMT

good

if it is not fetching the value than debug the codecheck the value coningin the ztable,i dont think there is any mistake in your qurey.

thanks

mrutyun^

Re: Comparing fields in where condition from same ztable

Former Member — Tue, 15 May 2007 09:17:18 GMT

TRY WITH THIS

SELECT * FROM ztable

INTO TABLE it_mtolog_gm

WHERE werks = p_werks

AND gm_qty < ztable<b>~</b>gr_qty.

REGARDS

SHIBA DUTTA

Re: Comparing fields in where condition from same ztable

Former Member — Tue, 15 May 2007 09:28:44 GMT

Hi,

Try this:

select <fld_list> from ztable as a inner join ztable as b on a~key fiedl= b~key_field
where a~werks = p_werks and a~gm_qty < b~gr_qty.

This will surely work.

Jogdand M B