Quering document lines in user define values

[ssierraSBI]

Hello! Im trying to get the "Quantity" field on the document lines in a "User defined values" query. 

SELECT $\[$38.11.0]
FROM DUMMY

I've been trying to use the previous query, and after reading through other questions on the subject I've tried:
Removing the backslash
Leaving only the select line
Changing "$38" for "RDR1"
Changing "0" for "number"
Changing "11" for "Quantity"

None of these have worked for me so far, and it either gives me internal error 3006 or syntax error near "$"

I'd appreciate any help on the subject

Thanks!

[mgregur]

Hi,

I don't know where you got this syntax, but you cannot use backslash. The correct version would be 

SELECT $[$38.11.0] FROM DUMMY

BR,

Matija

[ssierraSBI]

 Hi Matija, thanks for answering so quickly!

This syntax gives me internal error -3006 when trying to use this query for the user defined values in a lateral field.

[mgregur]

Hi,

I see from your last comment that you are trying to multiply the quantity. That is the reason for your syntax error, as the query I wrote only takes the field value, so it treats it as a string. To multiply with 24 you need this:

SELECT ($[$38.11.Number] * 24) FROM DUMMY

BR,

Matija

[ssierraSBI]

HI Matija,

As I said in my other comment, I wasn't trying to get the value to change yet since I'm still trying to get the query to return any value at all. 
However, my lateral field is of a numeric type so changing the type might help. I'm still getting internal error 3006 with this new query though.

Thanks!

Santiago

[mgregur]

Hi,

try using this FMS in an nvarchar field and see if it populates correctly. If yes, that means your result is coming back in a different type than expected, so most likely you need to cast it as integer or use substitute to change (usually its problems with comma(,) and dot(.) being misinterpreted).

BR,

Matija

[ssierraSBI]

Hi Matija,

I tried using it as it for a NVARCHAR field and it still giving me internal error 3006. 

Thanks again for the help!

Santiago

[mgregur]

Hi,

Just to clarify, are you using this query in the Query manager or manually triggering the FMS with the magnifying glass? Because this works for me (HANA version, FP2502).

BR,

Matija

[ssierraSBI]

Hi Matija,

I'm both using the shortcut Shift + F2 and manually using Tools > Queries > User Queries each time to see if any of those options returns any value.

Regards, 

Santiago

[mgregur]

Hi,

Did you make sure that the form you pull data from is open and the matrix is populated with data before running the query? Does your query window pull data from the matrix? Maybe it would be best if you try it now and post a screenshot when it gives you the error (blank out any sensitive data).

BR,

Matija

[ssierraSBI]

Hi Matija,

Here is a screenshot from the moment I get the error message. I'm trying to get data from the line ALQC-002, and the field is "Suma Asegurada" which has at the moment running the query the value 1000. I'm not sure what you mean by the form being open tho. And the field I'm running the query in is "Monto del Alquiler".

Regards,
Santiago