cancel
Showing results for 
Search instead for 
Did you mean: 

select count (*) from SAPSR3.snap where SEQNO ='000' and datum = 'sy-datum';

0 Kudos

Hello.

I am trying to get number of abap dumps from current date of system.

if i use manual input of date in yyyymmdd format. it giving me results for current date.

select count (*) from SAPSR3.snap where datum = '&3' and SEQNO ='000';

But if i try to automate it instead of giving date manually. i am getting error

select count (*) from SAPSR3.snap where datum = 'sy-datum' and SEQNO ='000' ;

datum ='sy-datum' is not working.

Please help.

Regards,

Gurprinder

chris_keating
Advisor
Advisor
0 Kudos

Is this issue occurring with SAP SQL Anywhere database system? If not you may want to retag with a more appropriate tag. You may want to include the error that you getting. My guess is the 'sy-datum' is being treated as the string sy-datum and not a variable value.

DoanManhQuynh
Active Contributor
0 Kudos

where did you write your query? if you put sy-datum in '' then it become a string of sy-datum, not its value.

Accepted Solutions (1)

Accepted Solutions (1)

Sandra_Rossi
Active Contributor
0 Kudos

If you enter directly a SQL query in the Oracle SQL console, as you said in one comment, then it's meaningless to use ABAP "sy-datum", it won't be recognized at all by Oracle! If you want to express "current date in YYYYMMDD format" with a Oracle SQL function, read the Oracle documentation or ask it in a Oracle forum.

The Oracle documentation says that a valid Oracle SQL would be something like:

select count (*) from SAPSR3.snap where datum = TO_CHAR( CURRENT_DATE, 'FXYYYYMMDD') and SEQNO ='000'

CURRENT_DATE: https://docs.oracle.com/database/121/SQLRF/functions051.htm#SQLRF00628

TO_CHAR: https://docs.oracle.com/database/121/SQLRF/functions216.htm#SQLRF06129

0 Kudos

Thanks you Sandra.

Answers (3)

Answers (3)

chris_keating
Advisor
Advisor
0 Kudos

I have no Oracle experience but the WITH appears to require a form:

with X as ( . . . )

You question has nothing to do with SQL Anywhere. You should retag this issue so that you question is visible to those with Oracle knowledge.

0 Kudos

ok i will retag. Thanks

0 Kudos

HI Kaushik,

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Below is the output of command. i think sy-datum date format is dd-mm-yyyy and sql format need yyyymmdd

SQL> select count (*) from SAPSR3.snap where SEQNO ='000' and datum = 'sy-datum'; COUNT(*)

---------- 0

SQL>

If i enter date manually i get below output

SQL> select count (*) from SAPSR3.snap where datum = '&3' and SEQNO ='000';

Enter value for 3: 20190902

old 1: select count (*) from SAPSR3.snap where datum = '&3' and SEQNO ='000'

new 1: select count (*) from SAPSR3.snap where datum = '20190902' and SEQNO ='000'

COUNT(*)

----------

15

SQL>

with "l_date = sy-datum";

SQL> select count (*) from SAPSR3.snap where SEQNO ='000' and "l_date = sy-datum";

select count (*) from SAPSR3.snap where SEQNO ='000' and "l_date = sy-datum" *

ERROR at line 1: ORA-00920: invalid relational operator

SQL>

Sandra_Rossi
Active Contributor
0 Kudos

Please use the COMMENT button for comments, questions, adding details, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area: "Before answering You should only submit an answer when you are proposing a solution to the poster's problem"

kaus19d
Active Contributor
0 Kudos

Hi Gurprinder Padda,

Could you please update us your database version? You mentioned by automating its failing, is it giving any failure SQL No. as reason or how did you automate, could you please ellaborate a bit for us to understand

Could you please check the Application Server & Database server timing if there is a difference

Could you please try with "sy-uzeit" or "l_date = sy-datum"

Please check the below,

https://blogs.sap.com/2014/04/14/how-to-format-sy-datum-in-various-routines-in-sap-bi/

Thanks,

Kaushik