topic Re: SQL Calculated Where-Condition / EXEC SQL in Application Development and Automation Discussions

SQL Calculated Where-Condition / EXEC SQL

Former Member — Thu, 20 Oct 2016 15:36:51 GMT

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

Re: SQL Calculated Where-Condition / EXEC SQL

Former Member — Fri, 21 Oct 2016 12:24:00 GMT

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.

Re: SQL Calculated Where-Condition / EXEC SQL

former_member186338 — Fri, 21 Oct 2016 12:27:33 GMT

What SQL server you are talking about?

Re: SQL Calculated Where-Condition / EXEC SQL

former_member186338 — Fri, 21 Oct 2016 12:50:19 GMT

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

EXEC SQL

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

ENDEXEC