Regarding Flat file and Select Statement

[Former Member]

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

[VijayaKrishnaG]

Hi Sai,

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

- Vijay

[former_member188827]

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

[former_member209120]

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
   

[mayur_priyan]

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.

[Former Member]

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.

[Former Member]

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&'.