cancel
Showing results for 
Search instead for 
Did you mean: 

MYSQL Select query to HANA database select query

Former Member
0 Kudos
592


Hi HANA experts,

Kindly help me in converting the below MYSQL database sepecific query to HANA database query

select [MATERIAL], [PLANT], [COSTSTATUS], [COSTVAR],

                
convert(char,min(DATEFROM),112) min_datefrom,

                 [COMP_CODE]
,
[CCOMPSTRUC]
,

                
convert(char,max(DATETO),112) max_dateto

         
from (SELECT [MATERIAL], [PLANT], [COSTSTATUS],

                       [COSTVAR]
,[COMP_CODE], [CCOMPSTRUC],

                      
convert(datetime, [DATEFROM],112) DATEFROM ,

                      
convert(datetime, [DATETO]  ,112) DATETO,

                      
convert( integer,

                      
convert(datetime, [DATETO],112) -

                      
convert(datetime , [DATEFROM],112))+ 1 LP

               
FROM [TABLE_NAME] as t1 with (nolock)

               
where [COSTSTATUS] = 'FR'

                 
and (t1.[COSTVAR] = 'GSTD' or

                       t1
.[COSTVAR] = 'LSTD')

                 
and CCOMPSTRUC >=  '03'

                 
and DATETO <> '99991231'

                 
and DATETO <> '00000000'

                 
and DATEFROM <> '00000000') as q

         
group by [MATERIAL], [PLANT], [COSTSTATUS], [COSTVAR],

                   [COMP_CODE]
, [CCOMPSTRUC]

          having
convert( integer, max(DATETO) -

                         
min(DATEFROM) )+1 >  sum(LP).

I am new to both mysql and hana. Help is very much appreciated.

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Hi Maju,

your request is a what we call "do my work for me"-request.

It's certainly alright that you ask for help with a task that you are not familiar with.

But dumping your work onto the members of this community is not OK.

Where's your piece of work here? What have you done so far?

What is the obstacle that you cannot overcome?

Did you consult the usual documentation?

We're happy to help around here, but you have to work for yourself (after all, when you work with SAP HANA or SAP in general it's pretty safe to assume you're not doing it for free...).

- Lars

Former Member
0 Kudos

Hi Lars,

Completely agree with you. I admit the mistake that I havent posted whatever i tried. Please see below on what I tried,

After going through some documentation, I tried formulating the HANA query, but it was giving me short dump when executing(Syntax error).

EXEC SQL.


 
OPEN WITH HOLD C FOR

select MATERIAL PLANT COSTSTATUS COSTVAR

     TO_TIMESTAMP
(min(DATEFROM), 'YYYYMMDD') min_datefrom

                 COMP_CODE

                 CCOMPSTRUC

     TO_TIMESTAMP
(max(DATETO), 'YYYYMMDD') max_dateto

         
from (SELECT MATERIAL PLANT COSTSTATUS

                       COSTVAR COMP_CODE CCOMPSTRUC

TO_TIMESTAMP
('DATEFROM', 'YYYYMMDD') DATEFROM

TO_TIMESTAMP
('DATETO', 'YYYYMMDD') DATETO

TO_INTEGER
('TO_TIMESTAMP ('DATETO', 'YYYYMMDD') DATETO - TO_TIMESTAMP ('DATEFROM', 'YYYYMMDD') DATEFROM') +1 LP

               
FROM /bic/tabname as t1 with (nolock)

               
where COSTSTATUS = 'FR'

                 
and (t1.COSTVAR = 'GSTD' or

                       t1
.COSTVAR = 'LSTD')

                 
and CCOMPSTRUC >=  '03'

                 
and DATETO <> '99991231'

                 
and DATETO <> '00000000'

                 
and DATEFROM <> '00000000') as q

         
group by MATERIAL, PLANT, COSTSTATUS, COSTVAR,

                   COMP_CODE
, CCOMPSTRUC

having TO_INTEGER
('max(DATETO) - min(DATEFROM)')+1 > sum(LP).

ENDEXEC.

Former Member
0 Kudos

Short dump details:

Short Text
    SQL error 257 occurred while executing Native SQL.

What happened?
    Error 257 has occurred on the current database connection "DEFAULT".
    Database error text:
    Triggering SQL statement: "select MATERIAL PLANT COSTSTATUS COSTVAR
     TO_TIMESTAMP ( min ( DATEFROM ), 'YYYYMMDD' ) min_datefrom COMP_CODE
     CCOMPSTRUC TO_TIMESTAMP ( max ( DATETO ), 'YYYYMMDD' ) max_dateto from (
     SELECT MATERIAL PLANT COSTSTATUS COSTVAR COMP_CODE CCOMPSTRUC TO_TIMESTA"

Error analysis

    An exception has occurred which is explained in more detail below. The

    exception is assigned to class 'CX_SY_NATIVE_SQL_ERROR' and was not caught in

     procedure

Since the caller of the procedure could not have anticipated this
exception, the current program was terminated.
The reason for the exception is:
Database error text: " "

lbreddemann
Active Contributor
0 Kudos

Ok, now you're even coding in ABAP?

Why's that?

Anyhow, there are a couple of things wrong or odd with your SQL command.

* date arithmetic cannot be done with standard operators like +, - . Use the data calculation functions (add_seconds, add_days, days_between... ) instead

* WITH NO LOCK is not something you can specify in the FROM <table> clause. For SAP HANA it doesn't even make sense as reading processes don't set exclusive locks.

I propose you make your life easier by first figuring out your statement step by step in the SAP HANA Studio SQL console. Once it delivers you the desired result set it's easy to put it to work in the ABAP context.

Right now, it seems you're confusing yourself more with the additional technology layer.

- Lars

Former Member
0 Kudos

Hi Lars,

Thanks you so much for your reply.

I am basically a BW ABAP consultant working on functional migration project.

The old BW system was running on MYSQL database and the select query in my first post was used in a function module extractor.

Initially, I tried to use OPEN SQL in my new system however it was very poor in performace as the query fetched lot of records. Hence we thought of writing the HANA database specific query similar to the one used in old system for MYSQL.

Let me try if I can get access to the HANA studio SQL console and sort this out.

Regards,

Maju

lbreddemann
Active Contributor
0 Kudos

That's interesting - what BW system was that? SAP BW never ran on mysql, so what did you use there? To be honest, I never even heard about mysql being a good choice for a DWH system, so now I'm curious.

Former Member
0 Kudos

NW7.3.

I saw that query written inside a Function module extractor written by someone else.

We have the same function module to be written in our new system aswell.

lbreddemann
Active Contributor
0 Kudos

I'm pretty sure you're mistaken here.

NetWeaver  doesn't support mySQL as a database platform.

Do you maybe refer to MS SQL Server? That would also make sense with your initial select statement with the straight brackets around the column names.

Former Member
0 Kudos

You are absolutely right.. It is MS SQL.

I mistakenly wrote MY SQL instead of MS SQL..

really sorry..:(