cancel
Showing results for 
Search instead for 
Did you mean: 

HOW to Use Case statement in Where Clause of HANA SQL QUERY

govardan_raj
Contributor
0 Kudos

hi Experts,

I have a requirement, where i need to find the total count of records that matches few conditions.

I have two tables 1) CALL_REGISTER 2) CALL_RESOLVED.

When a service request is made on call, then we make a entry with call_no as key at CALL_REGISTER Table

Once that service request is resolved, then also we make a entry with call_no as key at CALL_RESOLVED Table

Now we have a requirement where i need to get the count of records for a Dealer xxx, with call Category i.e. CATEG as C1

and having time difference of less than 24 hours.

Time difference is considered based on status .

When status is Resolved, then consider Time difference in hours between call creation Date Time and Call Resolved Date Time. Where call resolved date is captured in JOB_DONE_DATE in CALL_RESOLVED TABLE.

When status is not resolved, then consider Time Difference in hours between Call Creation Date Time and Current Time Stamp.

Below is my Query , but am getting error as shown below after the code block.

SELECT COUNT(*)OVER()  AS TOTAL_C1_COUNT, A.STATUS FROM CALL_REGISTER  A LEFT JOIN CALL_RESOLVED  B ON A.CALL_NO = B.CALL_NO
WHERE A.DEALER_CODE IN ('XXX') AND A.CATEG IN ('C1') AND 
CASE  A.STATUS 
WHEN 'RESOLVED'
THEN 
SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT(A.CALL_CREATION_DT,' '),A.CALL_CREATION_TIME)),TO_TIMESTAMP(CONCAT(CONCAT(B.JOB_DONE_DATE,' '),B.JOB_DONE_TIME)))/(24*3600) BETWEEN 0 AND 24  
ELSE 
(SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT ( A.CALL_CREATION_DT,' '),A.CALL_CREATION_TIME ) ),CURRENT_TIMESTAMP)/(24*3600)) BETWEEN 0 AND 24
END


SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "BETWEEN": line 6 col 165 (at pos 400)

Kindly do the needful.

Accepted Solutions (1)

Accepted Solutions (1)

former_member358098
Participant
0 Kudos

Sorry , didn't get idea at first.

Answer is to put between out of case, case only return value depending on condition, comparison is not part of what case do

 SELECT COUNT(*)OVER()  AS TOTAL_C1_COUNT, A.STATUS FROM CALL_REGISTER  A LEFT JOIN CALL_RESOLVED  B ON A.CALL_NO = B.CALL_NO
WHERE A.DEALER_CODE IN ('XXX') AND A.CATEG IN ('C1') AND 
CASE  A.STATUS 
WHEN 'RESOLVED'
THEN 
SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT(A.CALL_CREATION_DT,' '),A.CALL_CREATION_TIME)),TO_TIMESTAMP(CONCAT(CONCAT(B.JOB_DONE_DATE,' '),B.JOB_DONE_TIME)))/(24*3600) 
ELSE 
(SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT ( A.CALL_CREATION_DT,' '),A.CALL_CREATION_TIME ) ),CURRENT_TIMESTAMP)/(24*3600))
END
 BETWEEN 0 AND 24 
govardan_raj
Contributor
0 Kudos

hi artemt

Thanks its is Working fine !!!!!!

nikunjmehta2290
Participant
0 Kudos

Please help me on query like

SELECT "DOC_ENTRY" , "DOC_TYPE", "IRN_NO", "ACK_NO" , "ACK_DATE", "QR_CODE_IMAGE"

from "@UTL_E_INV_TEST"

WHERE "DOC_ENTRY"= "DOC_TYPE"

I want to match with DocEntry with DocType

Help me on where condition where it's match with DOCENTRY AND DOCTYPE.

For ex.

AR Invoice DocEntry 111

AR Credit Memo DocEntry 110

How to match it? Please help me on Join or where condition.

Answers (1)

Answers (1)

venkateswaran_k
Active Contributor
0 Kudos

Hi

Try this Query and update what is the result

SELECT COUNT(*) AS TOTAL_C1_COUNT, A.STATUS ,
       CASE A.STATUS = 'RESOLVED' THEN 
                        SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT(A.CALL_CREATION_DT,' '),
                                         A.CALL_CREATION_TIME)),
                                         TO_TIMESTAMP(CONCAT(CONCAT(B.JOB_DONE_DATE,' '),
                                         B.JOB_DONE_TIME)))/(24*3600)
                        ELSE 0 END AS RESOLVED_TIME,
       CASE A.STATUS != 'RESOLVED' THEN 
                        (SECONDS_BETWEEN (TO_TIMESTAMP(CONCAT(CONCAT ( A.CALL_CREATION_DT,' '),
                                          A.CALL_CREATION_TIME ) ),
                                          CURRENT_TIMESTAMP)/(24*3600))
                        ELSE 0 END AS WORKING_TIME
FROM CALL_REGISTER  A LEFT JOIN CALL_RESOLVED  B ON A.CALL_NO = B.CALL_NO
WHERE A.DEALER_CODE IN ('XXX') AND A.CATEG IN ('C1');
govardan_raj
Contributor
0 Kudos

hi Venkat,

Thanks for the above query, but here i need count of records whose time duration is between 0 to 24 hours, that condition is not mentioned as well as the query above should have group by clause else it throws errors.

venkateswaran_k
Active Contributor
0 Kudos

Yes, Group by is needed,,

I just want to see the result - based on that i can proceed with - some kind of subquery

Can you please provide the result of that query