Skip to Content
0

SAP HANA, very slow query and may not complete.

Mar 06, 2017 at 12:09 PM

102

avatar image
Former Member

I hope I have found the right place to post my question about SAP HANA coding issues?

I have a table that lists users and their location plus the time they were in that location. I am trying to build a table that counts the number of times two users meet based on location and a specified time range. Time range can be 120 sec (2 mins) for some locations or 600 sec for others. My code may or may not complete and build the table. If it fails it do so after a number of hours. The following is my code, I would appreciate help in trying to understand where I have gone wrong.

createCOLUMNTABLE"TABLES"."TBL_TEST_2014"as

(Select user1, user2, user1_mark, user2_mark, diff, meetings, meetings_A, meetings_S, meetings_m, meetings_t, meetings_c, meetings_l, meetings_li, meetings_r

From

(

Select

T1."USER"AS user1,

T2."USER"AS user2,

T3."MARK"AS user1_mark, --CHANGE

T4."MARK"as user2_mark, --CHANGE

ABS(T3." MARK" - T4." MARK") asdiff--Change

COUNT(*) AS meetings,

COUNT(CASEWHEN T1."Academic"='y'THEN 1 END) AS meetings_A,

COUNT(CASEWHEN T1."Academic"='n'THEN 1 END) AS meetings_S,

COUNT(CASEWHEN T1."type"='m'THEN 1 END) AS meetings_m,

COUNT(CASEWHEN T1."type"='t'THEN 1 END) AS meetings_t,

COUNT(CASEWHEN T1."type"='c'THEN 1 END) AS meetings_c,

COUNT(CASEWHEN T1."type"='l'THEN 1 END) AS meetings_l,

COUNT(CASEWHEN T1."type"='li'THEN 1 END) AS meetings_li,

COUNT(CASEWHEN T1."type"='r'THEN 1 END) AS meetings_r

FROM"WIFI_LOGS"."VIEW_WIFI_MEETING_LOGS" T1, -- change

"WIFI_LOGS"."VIEW_WIFI_MEETING_LOGS " T2, -- change

"WIFI_LOGS"."TBL_USER_MARKS" T3, -- RESULTS

"WIFI_LOGS"."TBL_USER_MARKS" T4 -- RESULTS

WHERE (T1."type" = 't'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 120)

or (T1."type" = 'l'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 600)

or (T1."type" = 'li'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 600)

or (T1."type" = 'm'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 600)

or (T1."type" = 'c'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 600)

or (T1."type" = 'r'andABS(T1."TIME_STAMP" - T2."TIME_STAMP") < 600)

and T1."USER" < T2."NAME"

AND T1."LOCATIO" = T2."LOCATION"

and T3."USER" = T1."USER"

and T4."USER" = T2."USER"

GROUPBY T1."USER",

T2."USER",

T3."MARK",

T4."MARK"

))

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers