topic Re: Regarding Flat file and Select Statement in Application Development and Automation Discussions

Regarding Flat file and Select Statement

Former Member — Sat, 19 Oct 2013 06:07:24 GMT

Hi,

I have a flat file structure like this with some inputs.

Flat file structure is

TYPES: BEGIN OF ty_uld,
            werks(4) TYPE c,     " Site
            lgort(4) TYPE c,       " Storage Location
            lgtyp(3) TYPE c,      " Storage Type
            lgpla(10) TYPE c,    " Storage Bin
            END OF ty_uld.

Now Flat file with Inputs.

Site Storage Location Storage Type Storage Bin

HW01 STOK EP

Hw01 STOK CP CP01

Now in the above flat file structure 'Storage Bin' is optional.It means 'Storage Bin' is may be blank for some entries and may not be blank.

Now i want to fetch the data from LQUA table ( Quants Table) based on Flat file inputs.

This is my select query for LQUA table

SELECT lgnum matnr werks lgtyp lgpla gesme meins lgort einme ausme verme
       FROM lqua
       INTO TABLE it_lqua
       FOR ALL ENTRIES IN it_uld
       WHERE werks = p_site
       AND lgtyp = it_uld-lgtyp
      AND lgpla = it_uld-lgpla
        and lgort = it_uld-lgort       " For Lgort
       %_HINTS ORACLE '&max_in_blocking_factor 100&'.

can anyone rectify the select statement for the above requirement.

Thanks

Re: Regarding Flat file and Select Statement

VijayaKrishnaG — Sat, 19 Oct 2013 06:21:15 GMT

Hi Sai,

Can you explain what exactly has to be rectified in your query?

- Vijay

Re: Regarding Flat file and Select Statement

former_member188827 — Sat, 19 Oct 2013 06:22:55 GMT

You can split the data from table it_uld into two internal tables, one internal table without storage bin value and other with storage bin values. Then you can write two select statements one in which lgpla is used in where clause and other without lgpla in where clause.

loop at it_uld into wa_uld.

if wa_uld-lgpla is not initial.

append wa_uld to it_uld_1.

else.

append wa_uld to it_uld_2.

endif.

endloop.

SELECT lgnum matnr werks lgtyp lgpla gesme meins lgort einme ausme verme

FROM lqua

INTO TABLE it_lqua

FOR ALL ENTRIES IN it_uld_1 "internal table with values in storage bin

WHERE werks = p_site

AND lgtyp = it_uld_1-lgtyp

AND lgpla = it_uld_1-lgpla

and lgort = it_uld_1-lgort " For Lgort

%_HINTS ORACLE '&max_in_blocking_factor 100&'.

SELECT lgnum matnr werks lgtyp lgpla gesme meins lgort einme ausme verme

FROM lqua

INTO TABLE it_lqua

FOR ALL ENTRIES IN it_uld_2 "internal table with values in storage bin

WHERE werks = p_site

AND lgtyp = it_uld_2-lgtyp

and lgort = it_uld_2-lgort " For Lgort

%_HINTS ORACLE '&max_in_blocking_factor 100&'.

Regards

Re: Regarding Flat file and Select Statement

former_member209120 — Sat, 19 Oct 2013 08:10:54 GMT

Hi Sai Ganesha.

Your flat file and structure like this..

Flat file structure is 
TYPES: BEGIN OF ty_uld,
            werks(4) TYPE c,     " Site
            lgort(4) TYPE c,       " Storage Location
            lgtyp(3) TYPE c,      " Storage Type
            lgpla(10) TYPE c,    " Storage Bin
            END OF ty_uld.
Now Flat file with Inputs.

Site         Storage Location        Storage Type         Storage Bin
HW01          STOK                         EP
Hw01           STOK                         CP                    CP01
''

SELECT lgnum matnr werks lgtyp lgpla gesme meins lgort einme ausme verme
       FROM lqua
       INTO TABLE it_lqua
       FOR ALL ENTRIES IN it_uld
     WHERE werks = p_site

       Where werks = it_uld-werks
       AND lgtyp = it_uld-lgtyp
      AND lgpla = it_uld-lgpla
        and lgort = it_uld-lgort .     " For Lgort

Re: Regarding Flat file and Select Statement

mayur_priyan — Sat, 19 Oct 2013 10:04:09 GMT

Hi,

It would be better if you could elaborate your query.

Though as far as I understand you are populating an internal table from a flat file into respective fields.

Then you are trying to use these values to fetch from values from table AQUA.

As you are using 'FOR ALL ENTRIES' along with 'AND' in your query the Select statement will fetch only the values which satisfies/resembles the data in the internal table. An if one of the the field in the internal table is blank then the Select statement fetches all possible values for this field.

Re: Regarding Flat file and Select Statement

Former Member — Sun, 20 Oct 2013 13:34:40 GMT

Hi,

If only bin field will be empty you can try it like this.

SELECT lgnum matnr werks lgtyp lgpla gesme meins lgort einme ausme verme
       FROM lqua
       INTO TABLE it_lqua
       FOR ALL ENTRIES IN it_uld
       WHERE werks = it_uld-werks
       AND lgtyp = it_uld-lgtyp
        and lgort = it_uld-lgort

loop at it_uld into wa_uld where not lgpla is initial.

read table it_lqua into wa_lqua WITH KEY werks = wa_uld-werks

lgtyp = wa_uld-lgtyp

lgort = wa_uld-lgort

lgpla = wa_uld-lgpla .

   if sy-subrc ne 0.
     delete TABLE it_lqua from wa_lqua.
   endif.
ENDLOOP.

Re: Regarding Flat file and Select Statement

Former Member — Mon, 21 Oct 2013 09:53:41 GMT

Hi,

It is very clear with your flat file & the SELECT query you are using that It will fetch the all the corresponding data from the table "lqua" relative to field "lgpla" whenever it will find the SPACE in your flat file.

So, its better you first segregate your flat file records in a two separate internal table based on the "lgpla" field.

Now You have to handle both Internal table separately.

SELECT lgnum matnr werks lgtyp lgpla gesme meins lgort einme ausme verme
       FROM lqua
       INTO TABLE it_lqua
       FOR ALL ENTRIES IN it_uld_01       " First Internal Table

       WHERE werks = p_site
       AND lgtyp = it_uld-lgtyp
      AND lgpla = it_uld-lgpla
        and lgort = it_uld-lgort       " For Lgort
       %_HINTS ORACLE '&max_in_blocking_factor 100&'.

SELECT lgnum matnr werks lgtyp lgpla gesme meins lgort einme ausme verme
       FROM lqua
       INTO TABLE it_lqua
       FOR ALL ENTRIES IN it_uld_02
       WHERE werks = p_site
       AND lgtyp = it_uld-lgtyp
      AND lgpla = it_uld-lgpla         " No need of it as it is blank
        and lgort = it_uld-lgort       " For Lgort
       %_HINTS ORACLE '&max_in_blocking_factor 100&'.