topic Re: SQL query syntax error ....... in Application Development and Automation Discussions

SQL query syntax error .......

Former Member — Mon, 02 Nov 2009 08:53:30 GMT

I am using following query

SELECT PERNR VORNA NACHN GBDAT FROM PA0002

INTO CORRESPONDING FIELDS OF TABLE ITAB

WHERE UPPER( VORNA ) = UPPER( STRU_USERIP-VORNA )

AND ENDDA >= SY-DATUM

AND BEGDA <= SY-DATUM .

and I am geting syntax error as --

vorna is not valid comparison operator .

can anyone plz suggest me solution over this problem .

Re: SQL query syntax error .......

former_member217544 — Mon, 02 Nov 2009 09:02:17 GMT

Hi,

Use this way:

SELECT PERNR VORNA NACHN GBDAT FROM PA0002

INTO CORRESPONDING FIELDS OF TABLE ITAB

WHERE VORNA = STRU_USERIP-VORNA

AND ENDDA >= SY-DATUM

AND BEGDA <= SY-DATUM .

Regards,

Swarna Munukoti

Re: SQL query syntax error .......

former_member222860 — Mon, 02 Nov 2009 09:04:37 GMT

You can't use UPPER in the Select Statement.

Re: SQL query syntax error .......

Former Member — Mon, 02 Nov 2009 09:04:57 GMT

Hi,

Remove the UPPER statement and check.

Regards,

Ginu

Re: SQL query syntax error .......

Former Member — Mon, 02 Nov 2009 09:05:41 GMT

Thanks for Replying ,

Query u replied works but I want ignore the case while compairing so I i cant remove the upper function

Is there any other solution for this ?

Edited by: RUSHI123 on Nov 2, 2009 10:06 AM

Edited by: RUSHI123 on Nov 2, 2009 10:07 AM

Re: SQL query syntax error .......

kesavadas_thekkillath — Mon, 02 Nov 2009 09:13:07 GMT

Hi this keyword is not possible in open sql ABAP query.

Possibilites to make it happen

Native Sql


tables:pa0002.

EXEC SQL.


  SELECT PERNR VORNA NACHN GBDA
  INTO :PA0002
  FROM  PA0002
  WHERE UPPER(VORNA)  =  :STRU_USERIP-VORNA

ENDEXEC.

You have to write a conversion exit for that field at database level.

Uncheck the lowercase checkbox in domain.

Re: SQL query syntax error .......

Former Member — Mon, 02 Nov 2009 09:15:28 GMT

This message was moderated.

Re: SQL query syntax error .......

kesavadas_thekkillath — Mon, 02 Nov 2009 09:15:37 GMT

Hi this keyword is not possible in open sql ABAP query.

Possibilites to make it happen

Native Sql


tables:pa0002.

EXEC SQL.


  SELECT PERNR VORNA NACHN GBDA
  INTO :PA0002
  FROM  PA0002
  WHERE UPPER(VORNA)  =  :STRU_USERIP-VORNA

ENDEXEC.

You have to write a conversion exit for that field at database level.

Uncheck the lowercase checkbox in domain.

since its a std db field only native sql you can try

Re: SQL query syntax error .......

Former Member — Mon, 02 Nov 2009 09:22:49 GMT

I am writing this query in controllers method of webdynpro abap application for retriving pernr of all records whose first name matches with inputed name .

Does above code lines will give internal table containing matching records ?

Your help will be highely appericiated .

Thks ,

Rushi

Re: SQL query syntax error .......

SuhaSaha — Mon, 02 Nov 2009 09:26:19 GMT


> Does above code lines will give internal  table containing matching records ?

Hello,

No it wont give you an internal table in return.

BR,

Suhas

Re: SQL query syntax error .......

kesavadas_thekkillath — Mon, 02 Nov 2009 09:31:14 GMT

hi,

Just try it once. It might work.

Since im not aware of concepts in webdynpro i cant assure you.

Re: SQL query syntax error .......

SuhaSaha — Mon, 02 Nov 2009 09:35:50 GMT

Hello Keshu,

Please read the SAP documention for EXEC SQL ... ENDEXEC.

To populate the internal table, you have to write it in a separate subroutine.

EXEC SQL PERFORMING SUBR. 
" Your SELECt stmt
ENDEXEC. 

FORM SUBR. 
" Append the work area to Internal Table 
ENDFORM.

I am not sure if you can use Native SQL in webdynpro.

BR,

Suhas

Re: SQL query syntax error .......

Former Member — Mon, 02 Nov 2009 09:36:55 GMT

Thks Keshu ,

Code u provided is just fine . It works in wdabap . It retrives only one matching record and put it into structutre variable :pa0002 .

In my case if there are more than one matching records then i want all of them in internal table so i can use them for further processesing .

can u suggest any solution over this .

Re: SQL query syntax error .......

SuhaSaha — Mon, 02 Nov 2009 09:42:32 GMT

Hello Rushi,

Do an F1 on EXEC SQL, read the SAP documentation. You get only one row for every EXEC SQL...ENDEXEC loop.

You have to populate the internal table in a separate subroutine.

BR,

Suhas

Re: SQL query syntax error .......

Former Member — Mon, 02 Nov 2009 10:03:03 GMT

Hello Suhas ,

suggestion u provided wont work in webdynpro abap . I closing this thres and similar in webdynpro abap categeory .

thks ,

Rushi

Re: SQL query syntax error .......

kesavadas_thekkillath — Mon, 02 Nov 2009 11:33:25 GMT

Hi just try this

You can build combinations of the name like

name = fra.

Fra

fRa

frA

FRa

fRA

FrA ....

the append it to ranges

and then use it in select query

Re: SQL query syntax error .......

Former Member — Mon, 02 Nov 2009 11:39:09 GMT

HI KESHU ,

How to get these combinations . is there any std method or i hv to implement some logic .

if you can elaborate , it would be really helpful .

thks ,

rushi

Re: SQL query syntax error .......

kesavadas_thekkillath — Mon, 02 Nov 2009 11:47:27 GMT

Logic must be written,

The below logic is incomplete

Break your head and modify it


TABLES:pa0002.
DATA:itab TYPE TABLE OF pa0002.
DATA:wa TYPE pa0002.
DATA:lv_vorna TYPE pa0002-vorna.
DATA:ra_vorna TYPE RANGE OF pa0002-vorna.
DATA:wa_vorna LIKE LINE OF ra_vorna .
DATA:lv_index TYPE i.
DATA:offset TYPE i.

DATA:pa_vorna TYPE pa0002-vorna VALUE 'Frank'.

DATA:len TYPE i.
len = STRLEN( pa_vorna ).
MOVE pa_vorna TO lv_vorna.
TRANSLATE lv_vorna TO LOWER CASE.
wa_vorna-low = lv_vorna.
APPEND wa_vorna TO ra_vorna.
MOVE pa_vorna TO lv_vorna.
TRANSLATE lv_vorna TO UPPER CASE.
wa_vorna-low = lv_vorna.
APPEND wa_vorna TO ra_vorna.
wa_vorna-low = PA_VORNA.
APPEND wa_vorna TO ra_vorna.

TRANSLATE PA_VORNA TO LOWER CASE.

DO len TIMES.
  lv_index = sy-index - 1.
  offset = lv_index + 1.
  DO len TIMES.
    MOVE pa_vorna TO lv_vorna.
    TRANSLATE lv_vorna+lv_index(offset) TO UPPER CASE.
    wa_vorna-low = lv_vorna.
    APPEND wa_vorna TO ra_vorna.
    ADD 1 TO lv_index.
  ENDDO.
ENDDO.