topic Re: selecting records in Application Development and Automation Discussions

selecting records

Former Member — Fri, 16 Nov 2007 13:32:51 GMT

hi all,

I have a transparent table ztab with fields kunnr , dptype ,etc..

I have internal table dtab with only field dptype.

In ztab,for kunnr = 1, i have 'n' no. of entries(records).

let number = 1.

for kunnr =1, i want all records from ztab to b appended into dtab.i have written the following code.i think its correct.but its taking only 1 record out of 'n' records

select dptype from ztab into dtab where kunnr = number.

append dtab.

endselect.

plz. help me.

Thanks,

Shivaa

Re: selecting records

Former Member — Fri, 16 Nov 2007 13:37:10 GMT

TRY USING

SELECT DPTYPE FROM ZTAB INTO TABLE DTAB WHERE KUNNR = NUMBER.

Re: selecting records

Former Member — Fri, 16 Nov 2007 13:41:51 GMT

this will work but for better performance it is advisable to fetch directly to internal table. rather than record by record

select dptype from ztab into dtab into table dtab where kunnr = number.

Re: selecting records

Former Member — Fri, 16 Nov 2007 13:47:06 GMT

Hi Shiva,

When the receiving data type and the transparent table type are different, you have to use "INTO CORRESPONDING FIELDS OF". This will ensure that fields with the same name in both structures are copied.

SELECT-ENDSELECT has very poor performance, because for every single record, the database server sends one table row to the application server.

This code wil work much better:

SELECT dptype

INTO CORRESPONDING FIELDS OF TABLE dtab

FROM ztab

WHERE kunnr = number

This will copy all rows of your database table and fill your internal table in one time.

have fun,

Peter

Re: selecting records

Former Member — Fri, 16 Nov 2007 13:49:14 GMT

Hi..

Check the data type u have given..

If it is type N means..

and u declared the Number variable as this type means ..

data number(5) type n.

U should assign the value like this.

Number = '00005'.

Then u may use ...

select dptype from ztab into dtab where kunnr = number.

append dtab.

endselect.

Hope it will helps.

Regards

Bala.

Re: selecting records

Former Member — Fri, 16 Nov 2007 13:52:43 GMT

Hi Peter,

I guess into corresponding fileds is not required in this case.

If he is using SELECT * from ztab,then only we need to use into corresponding fileds.But in this case he is selecting single filed

Re: selecting records

Former Member — Fri, 16 Nov 2007 13:56:47 GMT

hi all,

i used

SELECT DPTYPE FROM ZTAB INTO TABLE DTAB WHERE KUNNR = NUMBER,

but still only 1 out of 'n' records is getting inserted into dtab from ztab.

plz help.

Re: selecting records

Former Member — Fri, 16 Nov 2007 13:58:24 GMT

bcos kunnr = 1, has 'n' entries ,should i use anythinmg like 'ALLOWING DUPLICATES'.plz correct me if am wrong.

Re: selecting records

Former Member — Fri, 16 Nov 2007 14:01:57 GMT

the select query fills the internal table with data from database table that satisfies the where condition.how did you declare the internal table.

Re: selecting records

Former Member — Fri, 16 Nov 2007 14:05:59 GMT

data: begin of dtab occurs 0,

dptype like ztab-dptype,

end of dtab.

Re: selecting records

Former Member — Fri, 16 Nov 2007 14:13:06 GMT

Then it should work.Did you check in debugging mode.

Re: selecting records

Former Member — Fri, 16 Nov 2007 14:15:41 GMT

Hi,

try the following code,

select dptype from ztab appending table dtab where kunnr = number.

reward points if it helps..

Re: selecting records

Former Member — Fri, 16 Nov 2007 14:19:39 GMT

Hi shiva,

CHECK THIS..

IT SHOULD WORK...IF NOT THERE IS NO DATA IN ZTAB AGAINST NUMBER



SELECT dptype INTO CORRESPONDING FIELDS OF TABLE dtab FROM ztab
WHERE kunnr = number.

Re: selecting records

Former Member — Fri, 16 Nov 2007 14:54:28 GMT

Hi,

check it,

tables: ztab.

data: begin of dtab occurs 0,

dptype like ztab-dptype,

end of dtab.

data: number(10) type c.

number = '1'.

select dptype from ztab into table dtab where kunnr = number.

Re: selecting records

Former Member — Fri, 16 Nov 2007 16:32:23 GMT

hi, actually the code which i have written above is part of a 1000-line code.

i have used only the code --select dptype from ztab into table dtab where kunnr = number, in seperate pgm.here, its working.am getting all records.

but, when i use the above code in my 1000-line pgm., only 1 record is getting retrieved.

in 1000-lne pgm., i used thr 'select' stmt. inside a loop.does it make any difference?

Re: selecting records

Clemenss — Fri, 16 Nov 2007 17:05:59 GMT

Hi shiva,

may be your system is corrupted. KUNNR should have 10 digits like '0000000001'.

'ALLOWING DUPLICATES can be used for table inserts into database tables. This has nothing to do with your task.

Regards,

Clemens