Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Calculated Where-Condition / EXEC SQL

Former Member
0 Kudos

Hello all,

I would like to select database table entries with a calculated condition.

An content example:

SITE DATE        QUANTITY UNIT_OF_MEASURE
1000 12.10.10216 3        PCE
1010 13.10.10216 5        PCE
1010 16.10.10216 8        PCE
1020 18.10.10216 4        PCE
1420 19.10.10216 1        PCE
---

The where-condition should be defined in that way, that only entries will be selected with weekday eq <weekday>, e.g weekday is sunday

Is it possible to formulate a where-condition using EXEC SQL in that way?

Can it be implemented by a stored procedure?

SELECT * FROM MY_TABLE
WHERE calculate_weekdey(DATE) EQ 1. (1 = Sunday, 2 = Monday, etc.)

Thank you for your reply.

Best regards,

Kurt

3 REPLIES 3

former_member184795
Participant
0 Kudos

hi Kurt,

Do you have access to CDS and or AMDP in your environment?

The following link has some good examples of relevant sqlscript functions for AMDP usage.

https://blogs.sap.com/2016/06/23/valuable-sqlscript-functions-for-use-in-amdps/

Select posting_date, day_number, sum(day_number)
From (  Select map(dayname(posting_date),'MONDAY',1,
  'TUESDAY',2,
  'WEDNESDAY',3,
  'THURSDAY',4,
  'FRIDAY',5,
  'SATURDAY',6,
  '7'
   ) as day_number,
   posting_date
  From crmd_orderadm_h
  Where created_at >= to_dats(add_days(current_date, -7))||replace(to_time(current_timestamp),':','')
  )
Group by posting_date, day_number
order by 2

Above example of using a converting the columns during select, you can then apply a filter outside of Select if you so wished.

You can also create defined functions that could do the same thing in SQLScript, but it's hard to know what implementation would suit unless you tell us what env you are working in.

Thanks,

Sean.

former_member186338
Active Contributor
0 Kudos

What SQL server you are talking about?

former_member186338
Active Contributor
0 Kudos

If you are connecting to MS SQL server using native SQL:

EXEC SQL

SELECT QUANTITY FROM MY_TABLE WHERE DATEPART(weekday,DATE)=1

ENDEXEC