Skip to Content
0

How can we use analytical function in SQL Scripted CA View

Feb 16, 2017 at 08:24 AM

24

avatar image

Hello,

Can some one assist me here with the bellow error.

When i create a CA View (Scripted) with the SQL analytical function, the view gets activated successfully. But when i run the view it give me the below error.

With out the analytical function it give me the results.

Analytical function used:

min (case when type = 'Credits' then '9999-12-31' else "DUE_DATE" end ) over (partition by SERIAL_NO ) as due_date_min_serial

I am not doing any insert it just select the records.

Error message:

SAP DBTech JDBC: [274]: inserted value too large for column: [274] "_SYS_BIC"."XXXXX.XX.XX.XX.XXX/CA_XX_FAXXC_SEARCH_TEST/proc": line 353 col 1 (at pos 8308): [274] (range 3) inserted value too large for column exception: inserted value too large for column: Failed in "" column with the value End Customer (please check lines: 353, 358)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Feb 16, 2017 at 09:29 AM
0

What type has the result column of the "min" function in the calc. view output parameter description? I assume that it is something like NVARCHAR(8), das DUE_DATE seems to be date in format YYYYMMDD. If that is the case the value 9999-12-31 (which has a length of 10) does not fit into the result.

Regards,
Florian

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Florian,

Thanks for your response.

I am just using the result column to sort my partition. I will not output that one.

The DUE_DATE is defined as NVARCHAR(10) as "2016-10-21".

SQL Something like this

Selecting all the columns ( except due_date_min_serial) from

( Select min (case when type = 'Credits' then '9999-12-31' else "DUE_DATE" end ) over (partition by SERIAL_NO ) as due_date_min_serial,* from :temp_var_out ) order by due_date_min_serial asc, SERIAL_NO ,type desc , AMT_OWED desc ;

0

Ok, thx for the info. W/o more information (data, more logic of scripted view) it is hard to say what the issue could be. Are you able to share more information?

0

Let me know what other info your looking at.

This CA view pulls the data from a Stored procedure.(my requirements is to sort and group together on serial number)

0