topic Re: Help with Select query in Application Development and Automation Discussions

Help with Select query

Former Member — Mon, 05 Apr 2010 20:40:22 GMT

I have the below select query. The table it_dlv_item has fields od_docid and docid. I basically want to pull information based on od_docid if thats populated and docid if its not populated. I have snippet code below but i was wondering if there is an efficient way i could do this without using 2 select statements. Note: in both the docids is always populated. if the od_docid is populated i want to pull information using that else from the docid. Please let me know ur advice. Thanks!


if od_docid is not initial.
  SELECT *
   FROM XXX
INTO TABLE i_tab
FOR ALL ENTRIES IN it_dlv_item
  WHERE  docid EQ it_dlv_item-od_docid. 
else.
  SELECT *
   FROM XXX
INTO TABLE  i_tab
FOR ALL ENTRIES IN it_dlv_item
 WHERE  docid EQ it_dlv_item-docid.
endif.

Re: Help with Select query

Former Member — Mon, 05 Apr 2010 22:36:19 GMT

u can do it by constructing dynamic where condition.

Re: Help with Select query

Former Member — Mon, 05 Apr 2010 23:05:48 GMT

Please check for the below points.

1. In your internal table if od_docid is populated means, if it is same for all the entries, then simply read the first entry and code as you did now.

2. If it is different for few entries, then make use of another internal table, where just delete the entries with od_docid is initial and in the original table, delete entries where od_docid is not initial. Now select both and append to final internal table.

3. Though it is two different select statement in case of pont 1, it will execute only 1 select command. So do not think interms of performance. And do not make it complicate by using dynamic selects here. It is not required in this scenario.

Re: Help with Select query

Former Member — Mon, 12 Apr 2010 15:08:48 GMT

Can you please change that select query for me to understand clearly?

so basically i have to split the original table into 2 internal tables one which has od_docid and one which doesn have od_docid.

and do the selects accrodingly and append to the final table?

it will be helpful if you can change that select query. Thanks for the help.

Re: Help with Select query

former_member192854 — Fri, 15 Jun 2012 11:45:46 GMT

You can also try to do the following:

SELECT *

FROM XXX

INTO TABLE i_tab

FOR ALL ENTRIES IN it_dlv_item

WHERE ( docid EQ it_dlv_item-od_docid

OR docid EQ it_dlv_item-docid ).

Re: Help with Select query

Former Member — Fri, 15 Jun 2012 14:53:09 GMT

Hi,

Best way to solve this is to have 2 select statements

Assuming that rows in table it_dlv_item has atleast 1 of this od_docid docid populated.

1) Brake your internal table in 2 table , 1 with it_dlv_item-od_docid is not initial(it_div_item_1) & second where it_dlv_item-od_docid is initial(it_div_item_2).

SELECT *

FROM XXX

INTO TABLE i_tab_1

FOR ALL ENTRIES IN it_dlv_item_1

WHERE docid EQ it_dlv_item_1-od_docid.

SELECT *

FROM XXX

INTO TABLE i_tab_2

FOR ALL ENTRIES IN it_dlv_item_2

WHERE docid EQ it_dlv_item_2-docid.

now add records from table i_tab_1 & i_tab_2 in i_tab

(Note chances are there that you may get duplicate records in I_tab) please delete those according to your logic.)

Good luck

Raj

Re: Help with Select query

Clemenss — Fri, 15 Jun 2012 21:53:45 GMT

Hi,

I think if od_docid is not initial

is useless because od_docid must be a variable that can't say anyrthing about all records of it_dlv_item.

Maybe something like this:

SELECT *

FROM XXX

INTO CORRESPONDING FIELD OF TABLE i_tab

FOR ALL ENTRIES IN it_dlv_item

WHERE ( docid EQ it_dlv_item-od_docid

AND NOT EXISTS( SELECT * FROM XXX WHERE docid EQ it_dlv_item-docid ) )

OR docid EQ it_dlv_item-docid.

Regards,

Clemens Li