Date Input Parameter in scripted Calc view results no data

[Former Member]

Dear Experts,

I am passing a input parameter of datatype as DATE to a scripted calculation view which will return the data from Start of this Year

till the date provided by the user, Scripted calc view looks like -

where INPUT_END_DATE is input parameter defined as

This scripted calculation view is validated/activated with No error but when I do the data preview and Select today's date from shown

calendar then no result is displayed.However if I replace the INPUT_END_DATE with CURRENT_DATE (which returns todays date as per HANA sql function) then the result is displayed. I tried to keep input parameter as :INPUT_END_DATE but still no result.

Can you please help me to know whats not correct while passing Date as input parameter.

Thanks & Regards,

Jitendra

[lucas_oliveira]

Hi,

Paralelly: did you check if you're using Session Client for your view (Field 'Default Client' on Semantics -> View Properties)?

If that's set and your user does not have the right Client set (one with actual data) you won't any results indeed. If so you can either set the client for your user (ask your DBA for that) or set the view to Cross Client. Pretty common mistake.

It all gets easier to figure when you generate a planviz for your query

BRs,

Lucas de Oliveira

[Former Member]

Thanks Lucas !

I am using a simple scripted calculation view and I could not see the Default Client option in Scripted Calculation view, This option is available for graphical calc view's semantics.

Also I can see the data if I use CURRENT_DATE instead of input parameter in script query.

Thanks & Regards,

Jitendra

[justin_molenaur2]

I also agree with Lucas this is likely the issue. Check the SEMANTICS node of the scripted calc view and you should see this.

Please validate it is set for cross client.

Regards,

Justin

[Former Member]

Thanks Justin for looking into this. For my scripted calc view I could see only following options-

Default Schema

Data Category

Apply Privileges

Its not showing option for Default client in scripted calc view.

In same I could see data if I use equality operator (like POSTING_DATE = :INPUT_END_DATE) Or if I take BETWEEN operator and pass CURRENT_DATE instead :INPUT_END_DATE then also its showing data.

Its just that is I select any date or Current Date from input parameter calendar option (For Date datatype) in INPUT_END_DATE parameter then no values are returned.

Is it because input parameter only work with Equality operato (Please see the reference on my reply on 15 Sep) ?

Regards,

Jitendra

[justin_molenaur2]

What version are you using for Studio and HDB? That option should absolutely be there.

Regards,

Justin

[Former Member]

Hi Lucas/Justin

I am using version 1.85.1 and have only Developer access on used HANA landscape., It returns no rows if I pass input parameter value as current date, however it returns data if I change the code in where clause as POSTING_DATE =CURRENT_DATE;

I think input parameter with Between clause is not working (Please see the reference on my reply on 15 Sep).

Thanks an Regards,

Jitendra

[justin_molenaur2]

Ah, I am following you now - clearly the client setting is not an issue.

I remember having a problem like this and solved it by assigning the value of the input parameter to another scalar variable in the view. I'm not saying this is ideal or correct, but I think there was some issue with dynamic usage with the BETWEEN operator. I can't remember if I opened an OSS message on this or not, but it worked.

Try this
- add a new scalar

- assign the value of your original IP to it

- use the new scalar in the BETWEEN clause.

Example

temp_date DATE;

BEGIN

temp_date = :INPUT_END_DATE;

...code....

BETWEEN X and :temp_date

Regards,

Justin

[lucas_oliveira]

Parallely to what Justin added, generate the planviz for that execution and check which filters are being applied within your script.

I tried a simplified test with a similar scenario and I could also get the results. However, instead of a BETWEEN predicate, I could see two Basic Predicates being applied to the table I'm filtering: one for the entries <= IP_DATETO and another, feed by the result of the later, for entries with date >= first day of the current year. Here are the details:

--query: 
SELECT TOP 200 "CARRNAME", "FLDATE", sum("LOCCURAM") AS "LOCCURAM"
FROM "_SYS_BIC"."modelingtests.sqlscripting/CS_INPUTPARTEST"
('PLACEHOLDER' = ('$$IP_DATETO$$', '2015-09-23')) GROUP BY "CARRNAME", "FLDATE"

--CV sqlscript code:

 BEGIN 
  var_out = select C.CARRNAME, B.FLDATE, B.LOCCURAM from 
  SFLIGHT.SBOOK_TEST B, SFLIGHT.SCARR C
  where 
     B.CARRID = C.CARRID and 
  FLDATE between 
  add_days(current_date, (dayofyear(CURRENT_DATE)*-1)+1) and 
  :IP_DATETO;
END;

Filters in Planviz:

I know for a fact that there are known issues related to filter pushdown in revision85.01 (see SAP Note 2123714). I don't have the details though, so I can't confirm they are related to the scenario you're going through. My current tests were performed on a rev 97 system.

BRs,

Lucas de Oliveira

[Former Member]

Thanks Lucas !

Your CV sql script code is exactly same where only one input parameter is passed and using BETWEEN operator. As you mentioned that you are getting data but i my CV is not returning data. I observed with more analysis that

- if i take both dates for BETWEEN operator from input parameter ( by selecting value 01012015 for first and selecting todays date for second input via calender picker ) then CV returns data.

- If i put condition for BETWEEN operator  like posting_date BETWEEN (Formula code to get 01 jan 2015 based on Current date.) AND CURRENT_DATE. then also i am getting data.

-- If i pass only one input parameter (of datatype DATE) and select Today's date from calender picker

    then it do not return data. I guess because one date is autocalculated and second one is manullay passed in my CV and somewhere in conversion/comparision of dates it do not meet condition to return data. I checked again the table holding POSTING DATE and its of DATE type. I am not able to reach where it goes wrong with this scenario.

Any guess what could lead this.

I highly appriciate your time and help !

Regards,

Jitendra

[Former Member]

Thanks Justin !

I tried the approach you mentioned but its not returning the result. I have explained few scenarios in reply to Lucas. i think it could be related to version 1.85.1 but not really sure. would love to hear back your thoughts on scenarios i mentioned.

Regards,

Jitendra

[lucas_oliveira]

Hi Jitendra,

Don't guess. Just use planviz and check what's going on with the filters in each case.

If you're not familiar with that, there are dozens of articles explaining how to get those. I've even created one about that recently:

Since you have data retrieved when using CURRENT_DATE instead of the input parameter, the issue seems to be in the input parameter processing itself. Provide the planviz for this scenario and lets see what's in there.

BRs,

Lucas de Oliveira

[Former Member]

Hi Lucas,

i am sorry for my delayed response. I did the Vizplan on query and it looks like below -

I ran this query by hardcoding input parameter as todays date and the expression used by engine is -

And when I expand the topmost Projection node , It shows 0(10) rows returned -

what it refers by 0(10) rows, in column search itself it shows 0 rows. Please explain. I appreciate your guidance !!

Regards,

Jitendra

[lucas_oliveira]

Hello,

Did you execute the plan or just visualized it? It seems you only generated the visualization. I don't think that will help that much.

If you're lost on how to generate Planviz properly, go through the blog post below first:

Once you got this right, you should be able to see the basic predicates operating over the tables involved and certainly the filters applied as explained in my blog post.

Look for similar basic predicates as shown in the pictures I added to this discussion.

BRs,

Lucas de Oliveira

[Former Member]

Thanks Lucas ! I will go through blogs you mentioned and will check the generated plans . I will update you soon my observations on this.

Regards,

Jitendra

[Former Member]

Thank you Lucas for these wonderful Blogs. I did the predicate search and seems like BETWEEN operator is not taking the second range ( Input parameter in this scripted calc view)..

As you have mentioned in your blog I think pure graphical view could solve this if I understood  and traced this correctly. Waiting for your inputs/thoughts on the same.

Best Regards,

Jitendra

[lucas_oliveira]

Hi,

I'm glad they helped.

Yep, the input parameter is clearly not being considered here. That's why you're not seeing any resulting row from the basic predicate.

I'd try to use the following tests before anything:

1. Run the script with
   
   ...
   BETWEEN ADD_DAYS...
   AND
   :INPUT_END_DATE
   ...
   
   Notice the ':' in the front of the input parameter.
   
   
2. In my scenario, the BETWEEN predicate got changed to '<=' and '>='. Try changing those upfront and see how the planviz behaves. That may be a workaround as well.

I think the graphical calc view *could* be a workaround also but I have not tested it in this scenario.

Lastly, as explained before, there are know issues with input parameters in the revision you're in. If you happen to have access to a system with higher revision, test it out there and confirm the behavior. Big chance that's what we're facing.

Other than that the only thing I can suggest you is to create an SAP incident to take a closer look and either confirm what we suspected, or point out another option. Meanwhile, workarounds above might help.

BRs,

Lucas de Oliveira

[Former Member]

Thanks Lucas ! I have tried your suggested options but its not working with scripted Calc view.

I will open up a SAP incident to see if its the version issue, will update you the outcome.

Thank you for guidance here.

I would love to get your expert opinion on one of my thread which is still open to get response-

http://scn.sap.com/thread/3798120

Regards,

Jitendra

[Former Member]

Attached the sample code file. Please modify/Simplify this with your table name (you can choose only one column in select to keep it simple) and create a input variable of DATE type.

Thanks & Regards,

Jitendra

Script_Calc.txt.zip

[Former Member]

Hi,

Can you alter the script in the below way and try, Cause it worked for me with some sample tables and data.

SELECT  

        REGION_ID,<<SCHEMA_NAME>>.SALES.PRODUCT_ID,

        <<SCHEMA_NAME>>.SALES.PRODUCT_NAME,SUB_REGION_NAME,

        POSTING_DATE,PRODUCT_SIZE,

        SALES_AMOUNT

FROM

        <<SCHEMA_NAME>>.SALES INNER JOIN <<SCHEMA_NAME>>.PRODUCT

ON <<SCHEMA_NAME>>.SALES.PRODUCT_ID = <<SCHEMA_NAME>>.PRODUCT.PRODUCT_ID

WHERE POSTING_DATE

        BETWEEN

          ADD_DAYS(CURRENT_DATE, (DAYOFYEAR(CURRENT_DATE) * -1 )+1   )

        AND :INPUT_END_DATE;

Or you can also filter the data before and store in a separate temporary table type variable , like one of the post says to reduce the data before going to join operation. I believe that should also work.

Note : Please check whether data is there for that respective date ranges and the INPUT_END_DATE is a DATE datatype input variable.

[Former Member]

http://scn.sap.com/docs/DOC-27676

Hi Anil,

I tried code you suggested but no Luck. I have taken Input parameter of type DATE and I am selecting Todays date and there is data in table to return from 01 jan 15 till Today's date. I could see data if I replace :INPUT_END_DATE with CURRENT_DATE. However selecting Date from calendar from input parameter is not retruning Data.

One interesting point that I read on documentation on link mentioned above on Page no. 4 that Input parameter only work with Equality Operator and I am using here BETWEEN operator. I i simply use condition WHERE POSTING DATE := INPUT_END_DATE then it shows the value.

But As you have mentioned that its returning data for you so i am really not sure if the statement in document is correct.

Please provide your thoughts.

Thanks & Regards,

Jitendra

[Former Member]

Hi,

I believe the input parameters should be referred using ":".  So in your case it would be :INPUT_END_DATE.

Regards,

Anil

[Former Member]

Thanks Anil ! I tried this by using :INPUT_END_DATE but still its not showing any result. I have attached the sample code file below, Can you please try this at your system to understand the root cause.

I Appreciate your time and effort.

Regards,

Jitendra

[former_member1334]

Hi,

Can you try by replacing the below code of the where condition:

AND

(("POSTING_DATE"

BETWEEN

ADD_DAYS(CURRENT_DATE,(DAYOFYEAR(CURRENT_DATE) * -1) + 1)

AND ("POSTING_DATE",$$INPUT_END_DATE$$))

Regards,

Charles

[Former Member]

Hi Charles,

Thank you for looking into this. The code is giving error. Can you please try with attached sample code in thread below. It may help debug it.

Regards,

Jitendra