Logical SQL in calculation validated ok but Query Error in Visualization.

Kirk Li

Hi Group,

I've tried a Logical Sql in calculation in OAC,

it showed validate OK,like below part1,

but have Query Error in visualization like below part2.

And if we use Valueof function,it shows the message "Cannot find the dataset in this workbook,

but actually it's the SQL i copied from developer mode.

please kindly suggest and help!

Part1:

SELECT
max(XSA('kirk.k.li@gmail.com'.'DS_TEST_LastSQL')."TestLastDate"."RTDate") FROM XSA('kirk.k.li@gmail.com'.'DS_TEST_LastSQL')

Part2:

Error Message:

	Error generating view. Error getting cursor in GenerateHead
	Error during query processing (SQLExecDirectW).
	State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
	(HY000)
	State: HY000. Code: 43275. [nQSError: 43275] Message returned from OBIS [ecid:9061c0e3-d36e-4e85-a76e-c36f9078d247-00141eb8,0:1:27 ts:2024-12-12T04:07:00.758+00:00].
	(HY000)
	State: HY000. Code: 27002. [nQSError: 27002] Near <SELECT>: Syntax error (HY000)
	State: HY000. Code: 26012. [nQSError: 26012] . (HY000)
	SQL Issued: {call NQSGetQueryColumnInfo('SELECT SELECT
	max(XSA(''kirk.k.li@gmail.com''.''DS_TEST_LastSQL'')."TestLastDate"."RTDate") FROM XSA(''kirk.k.li@gmail.com''.''DS_TEST_LastSQL'') FROM XSA(''kirk.k.li@gmail.com''.''DS_TEST_LastSQL'')', 'PRECISION_SCALE, TYPE_NAME')}
	SQL Issued: SELECT SELECT
	max(XSA('kirk.k.li@gmail.com'.'DS_TEST_LastSQL')."TestLastDate"."RTDate") FROM XSA('kirk.k.li@gmail.com'.'DS_TEST_LastSQL') FROM XSA('kirk.k.li@gmail.com'.'DS_TEST_LastSQL')

BalagurunathanBagavathy-Oracle

@Kirk Li

The functionality of VALUEOF() is different. It is used to reference BI Server variables. Please check the below:
Use the VALUEOF function in a filter to reference the value of an Oracle BI repository variable.
VALUEOF(expr)

expr Variables should be used as arguments of the VALUEOF function. Refer to static repository variables by name.

Example: SalesSubjectArea.Customer.Region = VALUEOF("Region Security"."REGION")

Can you tell us your requirement? You can create a calculation using MAX(expr)

GayathriAnand-Oracle

What exactly are you trying to accomplish? Based on formula there does not seem to a need to use valueof in the sql?

Kirk Li

Thanks!

My goal is to display transaction data filtered by a time dimension (Quarter, Month) to show the relevant information. However, one of the columns needs to display the value corresponding to the latest date across the entire time range.

Currently, I’ve added a "dummy" field to both datasets and used blending in the workbook, but the performance is very poor and it only shows the correct information when the matching criteria is ALL.

I’m wondering if there’s an alternative way to achieve this using SQL to directly select the last date from the dataset.

Gianni Ceresa

SQL can do everything you want, but SQL is a database language and it can only help you if your dataset is based on a database directly (and at the dataset level).

A workbook doesn't speak SQL but LSQL at best, and even there you can't write every kind of LSQL query, because it isn't a standalone query generally defined a column in the general LSQL query.

Because SQL and LSQL are different languages, did you really mean SQL?

Kirk Li

Thanks, I mean LSQL.