Skip to Content
avatar image
Former Member

SQL Calculated Where-Condition / EXEC SQL

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Oct 21, 2016 at 12:24 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 21, 2016 at 12:50 PM

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

    EXEC SQL

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

    ENDEXEC

    Add comment
    10|10000 characters needed characters exceeded