Subscribe to this thread
Home - General / All posts - Explicit SQL alias of a command ? in a query
lionel

972 post(s)
#12-Jun-22 23:55

Hi

Is there a way to Show the value of a variable using plain SQL text ?

VALUE @input INT32 = 2;

VALUE @output INT32 = Square(2);

?@output ;

? Square(2);

SHOW COLUMNS FROM @input;

Thank's


INFOGRAPHY union , LINK doc , API, deepl & keyboard shortcut

hugh
194 post(s)
#13-Jun-22 02:49

Bonjour Lionel. Vous devez envoyer moins de messages sur le forum qui portent sur de nouveaux sujets lorsque vous n'avez pas résolu les questions que vous avez posées précédemment. La plupart du temps, cela implique de comprendre le SQL impliqué, donc les captures d'écran de l'interface ne sont pas utiles.

Je suis sur le forum depuis longtemps et mes compétences en SIG sont probablement au même niveau que les vôtres - mon travail ne me laisse pas le temps de les développer davantage. Lorsque je dois poser une question ou faire un commentaire, j'essaie de le faire de manière claire et brève puisque tant d'aspects importants de Manifold sont travaillés dans l'espace du forum,

Je ne parle pas français mais DeepL est merveilleux.

-- Hugh

hugh
194 post(s)
#14-Jun-22 02:00

Lionel, I apologize for my negative comment which misunderstands what you are doing in your posts. Your screenshots show issues at the level of working with the interface where they need to be resolved. If the answer to the problem has to involve SQL then the interface is probably not doing what it should. For me that is often being able to see what component has focus, something you usually show by drawing arrows.

apo
161 post(s)
#13-Jun-22 06:47

Dear Lionel

First of all the "?" rapid call of function or expression is not ended by a ";", meaning you have to use

VALUE @input INT32 = 2;

?@input

Second, the help on the name of the functions in SQL is really helpful and should be used as your first entry point. This means that square function is called "sqrt" and not square, but you might also use the more generic "^" exponent approach

VALUE @output FLOAT64 = @input^0.5;

VALUE @output FLOAT64 = sqrt(@INPUT);

?@output

?sqrt(2)

My final remark is on the non optimal use of INT32 type to store the result of a square operation, a float type fits better on my point of view

-- Abram

lionel

972 post(s)
#13-Jun-22 10:20

here a basic map file

Attachments:
ExternalScript.map


INFOGRAPHY union , LINK doc , API, deepl & keyboard shortcut

apo
161 post(s)
#15-Jun-22 11:47

Dear Lionel,

What is your expectation regarding the availability of this file, especially as it does not seem to incorporate the proposed corrections?

Ou en langue de Molière, quelle est ton attente vis-à-vis de cette mise à disposition de fichier, d'autant que celui-ci ne semble pas intégrer les corrections proposées ?

-- Abram

lionel

972 post(s)
#15-Jun-22 14:37

sorry I should have been more explicit about what I want to do and I often ask far-fetched questions and my English doesn't help (thanks DeepL)

tjhb use in a old post SQL code by avoid call a TABLE ( fake a TABLE) that use only text ( litteral ?)

I try to fake a TABLE ( n col x n row) by using a variable that behave like a "TABLE ( 1x1)

I want to Show ( SQL result) the content (value) of a variable using SQL.

the right value of the variable could be a "low level type" ( implicit cast ) or the return of a SQL function .

Does Manifold SQL engine can see @left like TABLE with a column call left that contain one row so one cell of value 2

memory eft = right ( declaration space? , initialisation new ? , affectation pointer )

VALUE @left INT32 = 2;

So i can write some SQL text but don't know howto write this SQL code ( ERROR Cannot parse query ) .

SELECT [left] FROM [left] ;

SELECT * FROM @left ;

SELECT * FROM CALL ( @left ) ;

SELECT * FROM TABLE ( @left ) ;

thank's


INFOGRAPHY union , LINK doc , API, deepl & keyboard shortcut

apo
161 post(s)
#15-Jun-22 15:06

is the use of a constant in VALUE a real need on your side ?

If not you can inject your constant value in a table instead

VALUE @left TABLE = (

SELECT * FROM (

VALUES (2)

)

);

which can be expanded to vectors to be used for example as a table or a vector in an IN ()

VALUE @left TABLE = (

SELECT * FROM (

VALUES (2),(1)

)

);

matrix...

VALUE @left TABLE = (

SELECT * FROM (

VALUES (2,3),(1,2)

)

);

adamw

10,011 post(s)
#15-Jun-22 15:37

Maybe this?

--SQL9

VALUE @left TABLE = (VALUES (1));

SELECT 2 FROM @left;

Or this?

--SQL9

VALUE @left TABLE = (VALUES (2) AS ([left]));

SELECT [left] FROM @left;

Is this what you are after?

lionel

972 post(s)
#16-Jun-22 03:27

the code proposed by adamw is the code I was looking for. many things are implicit or hidden when we code. would the use of "SELECT * FROM" be implicit in some cases ?

below a mix of apo and adamw code

VALUE @variable TABLE = (

SELECT * FROM (

VALUES (2) AS ([colname])

)

);

SELECT [colname] FROM @variable;

VALUES (manifold.net) return a TABLE like CALL with some restriction .

I will check if other Standalone statement SQLfunctions exist

This should work ? but not !!

VALUE @left TABLE = VALUES ('a', 'x'), ('b','b'), ('c', 'z') AS (ColumnA, ColumnB);

SELECT * FROM @left;


INFOGRAPHY union , LINK doc , API, deepl & keyboard shortcut

lionel

972 post(s)
#16-Jun-22 03:45

should work if "(...)"is use around "VALUES(....) AS (.....)" so we have "(VALUES(....) AS (.....))"

--SQL9 

VALUE @left TABLE = (VALUES ('a', 'x'), ('b','b'), ('c', 'z') AS (ColumnA, ColumnB));

SELECT * FROM @left;

.

--SQL9

 VALUE @left TABLE = (VALUES ('a''x'), ('b','b'), ('c''z'AS (ColumnA, ColumnB));

SELECT * FROM @left;

.


INFOGRAPHY union , LINK doc , API, deepl & keyboard shortcut

lionel

972 post(s)
#16-Jun-22 04:03

Does it make sense that Manifold SQL engine support this kind of implicit CAST or conversion or (marshalling ? ) not for type but also for structure, matrix ! I don't remenber the default column name manifold SQL engine ( MSE) use when column are uname . I know in some case like join MSE append a increment number for named column .

--SQL9 error

VALUE @variable = 2 ;

SELECT * FROM @variable;

about number of quote using 2 , '2' work not "2" inside "VALUE()"

--SQL9  ok 

VALUE @variable TABLE = ( VALUES ('2');

SELECT * FROM @variable;

instead of the explicit version bellow

--SQL9 ok 

VALUE @variable TABLE = (VALUES (2));

SELECT * FROM @variable;

first time i understand how "insert code" button work in manifold forum !!

MAnifold 9 gui really need color syntax for SQL .....the forum support it not manifold 9 GUI !!


INFOGRAPHY union , LINK doc , API, deepl & keyboard shortcut

adamw

10,011 post(s)
#16-Jun-22 08:04

Agree there is an inconsistency, indeed.

Are you arguing for allowing VALUE @var = 2; where we would automatically determine that @var is FLOAT64? Or for not allowing VALUE @var TABLE = (...); without the user somehow specifying what fields should @var have? We think the first option is cute, but leads to too many errors. The second option seems excessive, but maybe we should *allow* specifying the expected schema of a table without requiring it. So that if you write VALUE @var TABLE = (...); this works no matter which fields the right side produces, but if you write VALUE @var TABLE (name NVARCHAR) = (...); this works only if the right side produces a table with a single NVARCHAR field named 'name'.

lionel

972 post(s)
#16-Jun-22 05:10

about column name , have a look to my forum signature INFOGRAPHY union where ADVISOR_ID AVDISOR_ID 2 appear in the SQL join result .

what is the internal type of the column if we use 2 or '2' in the VALUES function ?

Attachments:
SQL_VALUES_test.map
typeofColumn.png


INFOGRAPHY union , LINK doc , API, deepl & keyboard shortcut

adamw

10,011 post(s)
#16-Jun-22 07:59

You can check the types of fields in the result table of a query using Edit - Schema. The types are also shown in the tooltips on field headers.

2 is FLOAT64. '2' is NVARCHAR. "2" is an identifier (as well as [2]). This works -- SELECT * FROM "mfd_root"

lionel

972 post(s)
#16-Jun-22 08:51

i was aware of that but not for the result of a query !! Thank's a lot ...

Attachments:
Howto-Know_Table_type_.png


INFOGRAPHY union , LINK doc , API, deepl & keyboard shortcut

adamw

10,011 post(s)
#16-Jun-22 11:32

...and when we are talking about a command window with no component associated with it, you can view the schema of a table that is currently shown in the Results tab using the Edit - Schema command in the main menu.

lionel

972 post(s)
#16-Jun-22 05:29

the default columns names return by VALUES are "result" with increment number of 1 that start begin with 2 for the others unamed columns : "result" , "result 2" , "result 3" ......for VALUES that have 3 unamed columns .


INFOGRAPHY union , LINK doc , API, deepl & keyboard shortcut

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.