topic Re: Syntax for Nested Select Statement in Application Development and Automation Discussions

Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:06:17 GMT

Gday Guys,

I am having some dramas with this select statement. For the records in cust_info, when agencynum ne 0, I want to get the corresponding name field from stravelag into cust_info-name and write it. (along with all the other data in that row of cust_info)

I keep getting errors in the final select statement. Any ideas would be much appreciated.

Thanks

Stuart

at line-selection.

check not scustom-id is initial.

select fldate bookid carrid connid class agencynum from sbook

into cust_info

where customid = scustom-id

order by fldate.

if cust_info-agencynum = 0.

write:/ 'Agency Unknown',

40 cust_info-fldate,

55 cust_info-bookid,

70 cust_info-carrid,

85 cust_info-connid,

100 cust_info-class.

elseif cust_info-agencynum > 0.

select single name from stravelag

into cust_info

where agencynum = cust_info-agencynum.

write:/ cust_info-name.

endselect.

endif.

endselect.

write:/ sy-dbcnt.

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:10:15 GMT

welcome to SDN!!!!!

remove endselect for the second select as it is select single

modify ur code like this

Syntax for Nested Select Statement

Posted: May 5, 2006 4:06 AM Reply E-mail this post

Gday Guys,

I keep getting errors in the final select statement. Any ideas would be much appreciated.

Thanks

Stuart

at line-selection.

check not scustom-id is initial.

select fldate bookid carrid connid class agencynum from sbook

into cust_info

where customid = scustom-id

order by fldate.

if cust_info-agencynum = 0.

write:/ 'Agency Unknown',

40 cust_info-fldate,

55 cust_info-bookid,

70 cust_info-carrid,

85 cust_info-connid,

100 cust_info-class.

elseif cust_info-agencynum > 0.

select single name from stravelag

into cust_info

where agencynum = cust_info-agencynum.

write:/ cust_info-name.

removed endselect

endif.

endselect.

write:/ sy-dbcnt.

Reward points if helpful

Message was edited by: Sekhar

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:12:43 GMT

Hi Stuart,

U have mentioned endselect for the second select statement..

Do not use ENDSELECT for SELECT SINGLE query..

SELECT... ENDSELECT is used when u r using a loop generally for many records..

For SELECT SINGLE, do not use ENDSELECT.

Regard,

Tanveer.

Please mark helpful answers

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:14:00 GMT

sorry , remove single statement from select single

elseif cust_info-agencynum > 0.
select  name from stravelag
into cust_info
where agencynum = cust_info-agencynum.
write:/ cust_info-name.
endselect.

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:16:07 GMT

Just remove the First ENDSELECT (The one for the second SELECT statement inside the IF condition)

Regards,

Ravi

Note : Please close the thread if the issue is resolved

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:16:41 GMT

at line-selection.

check not scustom-id is initial.

select fldate bookid carrid connid class agencynum from sbook
into cust_info
where customid = scustom-id
order by fldate.

if cust_info-agencynum = 0.
write:/ 'Agency Unknown',
40 cust_info-fldate,
55 cust_info-bookid,
70 cust_info-carrid,
85 cust_info-connid,
100 cust_info-class.

elseif cust_info-agencynum > 0.
select single name from stravelag
into cust_info
where agencynum = cust_info-agencynum.
write:/ cust_info-name.
<b>*endselect.</b>
endif.
endselect.

write:/ sy-dbcnt.

don't give endselect for select single.

remove endselect ,then try

regards

vijay

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:18:00 GMT

hai Stuart

elseif cust_info-agencynum > 0.

select single name from stravelag

into cust_info

where agencynum = cust_info-agencynum.

write:/ cust_info-name.

endselect.----

> Not use this Endselect statement

endif.

Check the bellow code

data : begin of it_mara1 occurs 0,

matnr like mara-matnr,

end of it_mara1.

This is wrong

select single matnr from mara

into it_mara1

where mtart = 'ROH'.

write:/ it_mara1-matnr.

endselect.

This is write

select single matnr from mara

into it_mara1

where mtart = 'ROH'.

write:/ it_mara1-matnr.

Thanks & Regards

Sreenivasulu P

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:23:11 GMT

Thanks for your quick responses and suggestions.

I have changed my code to: (removed endselect and single).

how ever i am still getting an incorrect nesting error?

*newcode*

select fldate bookid carrid connid class agencynum from sbook

into cust_info

where customid = scustom-id

order by fldate.

if cust_info-agencynum = 0.

write:/ 'Agency Unknown',

40 cust_info-fldate,

55 cust_info-bookid,

70 cust_info-carrid,

85 cust_info-connid,

100 cust_info-class.

elseif cust_info-agencynum ne 0.

select name from stravelag

into cust_info

where agencynum = cust_info-agencynum.

write:/ cust_info-name.

endif.

endselect.

write:/ sy-dbcnt.

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:25:33 GMT

Hi stuart .

try this 

select name from stravelag
into cust_info
where agencynum = cust_info-agencynum.
write:/ cust_info-name.
endselect.

here u have to use select ... endselect bcoz u may get many agency numbers

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:26:14 GMT

The problem is you have taken out both SINGLE as well as ENDSELECT for the second SELECT.

You will have to retain one of them.

Regards,

Ravi

Note : Please mark the helpful answers

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:27:56 GMT

**newcode**
select fldate bookid carrid connid class agencynum from sbook
  into cust_info
  where customid = scustom-id
  order by fldate.

if cust_info-agencynum = 0.
write:/ 'Agency Unknown',
  40 cust_info-fldate,
  55 cust_info-bookid,
  70 cust_info-carrid,
  85 cust_info-connid,
  100 cust_info-class.

<b>else.</b>
  select single name from stravelag
    into cust_info
    where agencynum = cust_info-agencynum.
    write:/ cust_info-name.
endif.
endselect.

write:/ sy-dbcnt.

try the above code......

Message was edited by: Vijay Babu Dudla

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:39:14 GMT

Thanks for the tips.

I have modified the code and can now run the report, however another error occurs, the reason being:

The data read during the SELECT access could not be inserted into the target field. Either converstion is not supported for the target fields type or the target field is too short.

How can this be the case when I have defined cust_info as:

data: begin of cust_info occurs 0,

fldate like sbook-fldate,

bookid like sbook-bookid,

carrid like sbook-carrid,

connid like sbook-connid,

class like sbook-class,

agencynum like sbook-agencynum,

name like stravelag-name,

end of cust_info.

The target field is defined as like stravelag-name?

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:41:07 GMT

Change your statement to this

select fldate bookid carrid connid class agencynum from sbook

into corresponding fields of

cust_info

where customid = scustom-id

order by fldate.

Regards,

RAvi

Re: Syntax for Nested Select Statement

Former Member — Fri, 05 May 2006 08:45:09 GMT

Problem solved! Thanks team. As a rookie ABAPer your help is very much appreciated.