Skip to Content
0

SQL Calculated Where-Condition / EXEC SQL

Oct 20, 2016 at 03:36 PM

70

avatar image

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

SQL
10 |10000 characters needed characters left characters exceeded

What SQL server you are talking about?

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Sean Holland Oct 21, 2016 at 12:24 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Oct 21, 2016 at 12:50 PM
0

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

EXEC SQL

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

ENDEXEC

Share
10 |10000 characters needed characters left characters exceeded