topic Re: Subquery in Application Development and Automation Discussions

Subquery

Former Member — Thu, 21 Jun 2007 18:18:43 GMT

Can anybody tell me whats the problem with the following query.

SELECT * FROM /las/l_summar_1 AS sl_a

INTO TABLE gt_summ_ledger

FOR ALL ENTRIES IN lt_l_act_dtl

WHERE fin_id IN s_finid " lt_l_act_dtl-acct_fin_id

AND loan_num IN s_loan

AND acct_num EQ lt_l_act_dtl-acct_num

AND specified_value EQ lt_l_act_dtl-specified_value

AND sl_a~ilap_eop_dt EQ ( SELECT MAX( ilap_eop_dt )

FROM /las/l_summar_1 AS sl_b

WHERE sl_b_{fin_id in s_finid " EQ sl_a}fin_id

AND sl_b_{acct_num EQ sl_a}acct_num

AND sl_b_{loan_num EQ sl_a}loan_num

AND sl_b_{wb_ccy_code EQ sl_a}wb_ccy_code

AND sl_b_{specified_value EQ sl_a}specified_value

AND sl_b_{loan_sale_id EQ sl_a}loan_sale_id

AND sl_b_{acct_pymt_dt EQ sl_a}acct_pymt_dt

AND sl_b_{tranche_id EQ sl_a}tranche_id

AND sl_b~ilap_eop_dt LE gv_cut_off_dt

Its bit urgent.

-Priya

Re: Subquery

RichHeilman — Thu, 21 Jun 2007 18:24:07 GMT

I'm pretty sure you can do a sub query like that, subqueries are simply to see if an entry exists in another table or not.

Sort of like this.



  select afko~aufnr aufk~werks
         afko~gamng afko~plnbez afko~getri
             into corresponding fields of table iafko
                from afko
                  inner join aufk
                     on afko~aufnr = aufk~aufnr
                              where afko~getri in s_getri
                                and afko~gltrp in s_gltrp
                                and aufk~auart = 'PP01'
                                and exists ( select * from mvke
                                               where matnr = afko~plnbez
                                                 and mvgr1 in s_mvgr1 ).

Regards,

RIch Heilman

Re: Subquery

Former Member — Thu, 21 Jun 2007 18:29:14 GMT

See the below code and i modified :

SELECT * FROM /las/l_summar_1 AS sl_a

INTO TABLE gt_summ_ledger

FOR ALL ENTRIES IN lt_l_act_dtl

WHERE fin_id IN s_finid " lt_l_act_dtl-acct_fin_id

AND loan_num IN s_loan

AND acct_num EQ lt_l_act_dtl-acct_num

AND specified_value EQ lt_l_act_dtl-specified_value

AND sl_a~ilap_eop_dt EQ ( SELECT MAX( ilap_eop_dt )

FROM /las/l_summar_1 AS sl_b

WHERE sl_b_{fin_id in s_finid " EQ sl_a}fin_id

AND sl_b_{acct_num EQ sl_a}acct_num

AND sl_b_{loan_num EQ sl_a}loan_num

AND sl_b_{wb_ccy_code EQ sl_a}wb_ccy_code

AND sl_b_{specified_value EQ sl_a}specified_value

AND sl_b_{loan_sale_id EQ sl_a}loan_sale_id

AND sl_b_{acct_pymt_dt EQ sl_a}acct_pymt_dt

AND sl_b_{tranche_id EQ sl_a}tranche_id

AND sl_b~ilap_eop_dt LE gv_cut_off_dt ).

I did not test it and if you get any issue do compare with below query

REPORT demo_select_subquery_2.

DATA: carr_id TYPE spfli-carrid VALUE 'LH',

conn_id TYPE spfli-connid VALUE '0400'.

DATA: city TYPE sgeocity-city,

lati TYPE p DECIMALS 2,

longi TYPE p DECIMALS 2.

SELECT SINGLE city latitude longitude

INTO (city, lati, longi)

FROM sgeocity

WHERE city IN ( select cityfrom

FROM spfli

WHERE carrid = carr_id AND

connid = conn_id ).

WRITE: city, lati, longi.