topic Re: Report Error in inner join function in Application Development and Automation Discussions

Report Error in inner join function

Former Member — Wed, 19 Dec 2007 10:37:56 GMT

Hi friends,

Im getting runtime error in Inner Joint fuction at my select command.If u can help me....

just go check the code below for ur reference ,and let me know with ur modified stmts.....Pls do the needful.

getting dump/Errors in Select stmt.....

************coding part

REPORT zsd_incent_alvn_t.

TYPE-POOLS: slis.

TYPE-POOLS: list.

TABLES: vbak, vbpa, vbrp, kna1, lips.

*Internal tables

DATA: BEGIN OF i_incentivedetails OCCURS 0,

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

kunnr like vbak-kunnr,

name1 like kna1-name1,

vbeln1 LIKE vbrp-vbeln,

erdat1 LIKE vbrp-erdat,

kunrg like vbrk-kunrg,

fkimg LIKE vbrp-fkimg,

pernr(8) TYPE c, "LIKE VBPA-PERNR,

END OF i_incentivedetails.

DATA: BEGIN OF it_first OCCURS 0,

pernr(8) TYPE c, "LIKE VBPA-PERNR,

name LIKE pa0001-ename,

incentive(15) TYPE c, "TYPE P DECIMALS 2,

END OF it_first.

DATA: BEGIN OF it_second OCCURS 0,

vbeln LIKE vbak-vbeln,

erdat TYPE vbak-erdat,

name1 like kna1-name1,

vbeln1 LIKE vbrp-vbeln,

erdat1 LIKE vbrp-erdat,

kunrg like vbrk-kunrg,
kunnr like vbak-kunnr,

fkimg(15) TYPE c, "LIKE VBRP-FKIMG,

line_color(4) TYPE c,

END OF it_second.

*variables

DATA: tot LIKE vbrp-fkimg,

val LIKE vbrp-fkimg VALUE 0,

name LIKE pa0001-ename,

incen TYPE p DECIMALS 2,

incentive TYPE p DECIMALS 2,

var LIKE vbpa-pernr.

TYPES: slis_tabname(30) TYPE c,

slis_fieldname(30) TYPE c.

DATA: fcat TYPE slis_t_fieldcat_alv WITH HEADER LINE,

vcat TYPE slis_t_fieldcat_alv WITH HEADER LINE,

listhead TYPE slis_t_listheader WITH HEADER LINE,

event TYPE slis_t_event WITH HEADER LINE,

eventone TYPE slis_t_event WITH HEADER LINE,

i_layout TYPE slis_layout_alv.

*Selection Screen

SELECTION-SCREEN BEGIN OF BLOCK char WITH FRAME TITLE text-001.

SELECT-OPTIONS: l_vkorg FOR vbak-vkorg.

SELECT-OPTIONS: l_pernr FOR vbpa-pernr.

SELECTION-SCREEN END OF BLOCK char.

SELECTION-SCREEN BEGIN OF BLOCK period WITH FRAME TITLE text-002.

SELECT-OPTIONS l_erdat FOR vbak-erdat.

SELECTION-SCREEN END OF BLOCK period.

*selection

SELECT vbak_{vbeln vbak}erdat vbak_{kunnr kna1}name1 vbrp~vbeln

vbpa_{pernr vbrp}fkimg vbrp~erdat

INTO TABLE i_incentivedetails

FROM vbak

INNER JOIN vbpa ON vbak_{vbeln EQ vbpa}vbeln

INNER JOIN lips ON vbak_{vbeln EQ lips}vgbel

INNER JOIN vbrp ON lips_{vbeln EQ vbrp}vgbel

INNER JOIN vbrk on vbrp_{vbeln EQ vbrk}vbeln*

INNER JOIN kna1 on vbak_{kunnr EQ kna1}kunnr

WHERE vbpa_{parvw EQ 'AF' AND lips}posnr eq '000010' and vbpa_{pernr IN l_pernr AND vbak}vkorg IN l_vkorg AND vbak~erdat IN l_erdat.IF sy-subrc NE 0.

WRITE: 'NO VALUES FOUND FOR THE SPECIFIED CRITERIA'.

ENDIF.

SORT i_incentivedetails BY pernr vbeln.

LOOP AT i_incentivedetails.

AT NEW pernr.

CLEAR name.

SELECT SINGLE ename FROM pa0001 INTO name WHERE pernr EQ i_incentivedetails-pernr.

ENDAT.

val = val + i_incentivedetails-fkimg.

AT END OF pernr.

SELECT inc FROM zincnt INTO incen WHERE quantity_low <= val AND quantity_high >= val.

ENDSELECT.

incentive = val * incen.

MOVE i_incentivedetails-pernr TO it_first-pernr.

MOVE name TO it_first-name.

MOVE incentive TO it_first-incentive.

APPEND it_first.

CLEAR: val, incen, incentive.

ENDAT.

ENDLOOP.

IF sy-subrc EQ 0.

fcat-col_pos = 1.

fcat-fieldname = 'PERNR'.

fcat-ref_tabname = 'VBPA'.

fcat-emphasize = 'C211'.

fcat-hotspot = 'X'.

fcat-outputlen = '20'.

APPEND fcat.

fcat-col_pos = 2.

fcat-fieldname = 'NAME'.

fcat-ref_tabname = 'PA0001'.

fcat-seltext_l = 'EMPLOYEE NAME'.

fcat-emphasize = 'C412'.

fcat-hotspot = ' '.

fcat-outputlen = '40'.

APPEND fcat.

fcat-col_pos = 3.

fcat-fieldname = 'INCENTIVE'.

fcat-ref_tabname = 'I_INCENTIVEDETAILS'.

fcat-seltext_l = 'INCENTIVE(RS.)'.

fcat-emphasize = 'C512'.

fcat-hotspot = ' '.

fcat-outputlen = '20'.

APPEND fcat.

vcat-col_pos = 1.

vcat-fieldname = 'VBELN'.

vcat-ref_tabname = 'IT_SECOND'.

vcat-seltext_l = 'SALES ORDER'.

vcat-emphasize = 'C201'.

fcat-outputlen = '25'.

APPEND vcat.

vcat-col_pos = 3.

vcat-fieldname = 'VBELN1'.

vcat-ref_tabname = 'IT_SECOND'.

vcat-seltext_l = 'BILLING DOCUMENT'.

vcat-emphasize = 'C201'.

fcat-outputlen = '25'.

APPEND vcat.

vcat-col_pos = 5.

vcat-fieldname = 'NAME1'.

vcat-ref_tabname = 'IT_SECOND'.

vcat-seltext_l = 'Customer Name'.

vcat-no_zero = ' '.

vcat-do_sum = 'X'.

vcat-emphasize = 'C201'.

vcat-outputlen = '25'.

APPEND vcat.

vcat-col_pos = 6.

vcat-fieldname = 'FKIMG'.

vcat-ref_tabname = 'IT_SECOND'.

vcat-seltext_l = 'BILLED QUANTITY'.

vcat-no_zero = ' '.

vcat-do_sum = 'X'.

vcat-emphasize = 'C201'.

vcat-outputlen = '20'.

APPEND vcat.

vcat-col_pos = 2.

vcat-fieldname = 'ERDAT'.

vcat-ref_tabname = 'IT_SECOND'.

vcat-seltext_l = 'ORDER DATE'.

vcat-no_zero = ' '.

vcat-edit_mask = '__/__/____'.

vcat-emphasize = 'C402'.

vcat-outputlen = '15'.

APPEND vcat.

vcat-col_pos = 4.

vcat-fieldname = 'ERDAT1'.

vcat-ref_tabname = 'IT_SECOND'.

vcat-seltext_l = 'BILLING DATE'.

vcat-emphasize = 'C402'.

vcat-outputlen = '15'.

APPEND vcat.

event-name = 'TOP_OF_PAGE'.

event-form = 'TOPPAGE'.

APPEND event.

event-name = 'USER_COMMAND'.

event-form = 'USECOMM'.

APPEND event.

i_layout-window_titlebar = 'SURANA INDUSTRIES LIMITED'.

listhead-typ = 'H'.

listhead-info = 'SALES EMPLOYEES AND THEIR INCENTIVES'.

APPEND listhead.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = sy-cprog

i_background_id = 'TRVPICTURE22'

i_grid_title = 'SALES EMPLOYEE'

is_layout = i_layout

it_fieldcat = fcat[]

it_events = event[]

TABLES

t_outtab = it_first.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

&----

*& FORM TOPPAGE

&----

TEXT

----

FORM toppage.

***----

ALV FUNTION TO WRITE IN THE LIST HEADER

CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'

EXPORTING

it_list_commentary = listhead[]

i_logo = ' '.

ENDFORM. "TOPPAGE

&----

*& FORM USECOMM

&----

TEXT

----

-->UCOMM TEXT
-->FIELDS TEXT

----

FORM usecomm USING ucomm LIKE sy-ucomm fields TYPE slis_selfield.

CLEAR: it_second, tot.

REFRESH it_second.

READ TABLE it_first INDEX fields-tabindex.

var = it_first-pernr.

LOOP AT i_incentivedetails WHERE pernr = var.

MOVE i_incentivedetails-vbeln TO it_second-vbeln.

MOVE i_incentivedetails-erdat TO it_second-erdat.

MOVE i_incentivedetails-vbeln1 TO it_second-vbeln1.

MOVE i_incentivedetails-erdat1 TO it_second-erdat1.

MOVE i_incentivedetails-name1 to it_second-name1.

MOVE i_incentivedetails-fkimg TO it_second-fkimg.

ADD i_incentivedetails-fkimg TO tot.

APPEND it_second.

ENDLOOP.

CLEAR it_second.

it_second-vbeln = 'TOTAL'.

it_second-erdat = ' '.

it_second-erdat1 = ' '.

it_second-line_color = 'C610'.

MOVE tot TO it_second-fkimg. "WHERE TABIX = SY-TABIX.

APPEND it_second.

i_layout-no_input = 'X'.

i_layout-colwidth_optimize = 'X'.

i_layout-info_fieldname = 'LINE_COLOR'.

***----

DISPLAY CUSTOMER CCODES LIST IN GRID

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = sy-cprog

i_structure_name = 'IT_SECOND'

i_background_id = 'TRVPICTURE05'

i_grid_title = 'ORDER DETAILS'

is_layout = i_layout

it_fieldcat = vcat[]

it_events = eventone[]

TABLES

t_outtab = it_second.

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

*REFRESH LISTKNB1.

ENDFORM. "USECOMM

*******************************************

And my Run-time error is....

*********

The exception ,which is assigned to a class 'CX_SY_OPEN_SQL_DB' was not caught and therefor caused a runtime error.

In a Select access, the read file could not be placed in the target field provided.Either the conversion is not supported for the type of the target field,the target field is too small to include the value, or the data doesnot have the format required the target field.

Waitinf for ur favourable reply.

thanks & regards

sankar.

Edited by: sankar babu on Dec 19, 2007 11:49 AM

Edited by: sankar babu on Dec 19, 2007 12:14 PM

Re: Report Error in inner join function

Former Member — Wed, 19 Dec 2007 10:40:17 GMT

First you have not mentioned what the runtime error is.

Secondly this comment 'just go thru the below code,rectify the errors and let me know with ur modified stmts' is just down right ignorant.

Maybe try changing the way you ask people for help.

Re: Report Error in inner join function

Vijay — Wed, 19 Dec 2007 10:49:09 GMT

you query is

SELECT vbak_{vbeln vbak}erdat vbak_{kunnr kna1}name1 vbrp~vbeln

vbpa_{pernr vbrp}fkimg vbrp~erdat

INTO TABLE i_incentivedetails

FROM vbak

INNER JOIN vbpa ON vbak_{vbeln EQ vbpa}vbeln

INNER JOIN lips ON vbak_{vbeln EQ lips}vgbel

INNER JOIN vbrp ON lips_{vbeln EQ vbrp}vgbel

INNER JOIN vbrk on vbrp_{vbeln EQ vbrk}vbeln

INNER JOIN kna1 on vbak_{kunnr EQ kna1}kunnr

here the error is lines which r underlined.

the reason is that u r trying to join vbak and lips which cannot be done bcoz a table can be joined only to just previously mentioned table , in this case vbpa.

the same is for the last line.

regards

vijay

reward points if helpfull

Re: Report Error in inner join function

Former Member — Wed, 19 Dec 2007 10:49:29 GMT

Oh, sorry man...i didnt mean it in a wrong way...

may be u r right....the word i've mentioned leads to irrespctive way...

sorry for that.

regards

sankar.

Re: Report Error in inner join function

Former Member — Wed, 19 Dec 2007 11:21:38 GMT

Hi Vijay sharma,

Pls check once again my posting...which has the clear and updated one with run-time error message also.I've already did one report for daily sales(billing) and i used the coding the same what i've mentioned above....for that,its working correctly but here i find some errors.....

If u possible,pls check once and let me know.

Thanks & regards

sankar.

Re: Report Error in inner join function

former_member188827 — Wed, 19 Dec 2007 11:27:36 GMT

SELECT vbak_{vbeln vbak}erdat vbak_{kunnr kna1}name1 vbrp~vbeln

vbpa_{pernr vbrp}fkimg vbrp~erdat

INTO CORRESPONDING FIELDS OF TABLE i_incentivedetails

FROM vbak

INNER JOIN vbpa ON vbak_{vbeln EQ vbpa}vbeln

INNER JOIN lips ON vbak_{vbeln EQ lips}vgbel

INNER JOIN vbrp ON lips_{vbeln EQ vbrp}vgbel

INNER JOIN vbrk on vbrp_{vbeln EQ vbrk}vbeln

INNER JOIN kna1 on vbak_{kunnr EQ kna1}kunnr.

USE CORRESPONDING FIELDS OF IN DA SELECT STMT.

PLZ REWARD POINTS IF DIS HELPS

Re: Report Error in inner join function

Former Member — Wed, 19 Dec 2007 11:34:14 GMT

Hi,

First check the order of internal table structure fields and the fields that your are fetching from select statement.

find the below my observations.

YOUR internal table structure

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

kunnr like vbak-kunnr,

name1 like kna1-name1,

vbeln1 LIKE vbrp-vbeln,

erdat1 LIKE vbrp-erdat,

kunrg like vbrk-kunrg,

fkimg LIKE vbrp-fkimg,

pernr(8) TYPE c,

Selection fields in your select statements

vbak~vbeln

vbak~erdat

vbak~kunnr

kna1~name1

vbrp~vbeln

vbpa~pernr------this field is misplaced

vbrp~fkimg----

this field is misplaced

vbrp~erdat

Your internal table structure should be

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

kunnr like vbak-kunnr,

name1 like kna1-name1,

vbeln1 LIKE vbrp-vbeln,

pernr(8) TYPE c,

fkimg LIKE vbrp-fkimg,

erdat1 LIKE vbrp-erdat,

kunrg like vbrk-kunrg,

and also make sure that the data types of the declared structure and the fetching fields should be same.

Reward if useful.

Thanks,

Sreeram.

Edited by: Sreeram Prasad on Dec 19, 2007 12:37 PM

Re: Report Error in inner join function

Former Member — Wed, 19 Dec 2007 11:40:12 GMT

i've solved my own...problem in my field declaration for internal table corressponding to my select stmt.

thanks & regards

sankar.