Subscribe to this thread
Home - General / All posts - equivalent reserved word in mfd9 and SQL Database Programming 2015 Edition Chris Fehily
lionel

519 post(s)
#07-Jan-18 21:21

i test the book.map file ( don't know wich version) and the listing0522 i copy paste in another query since listing0522 is read only in my map file . I can avoid this behaviour by changing the property readOnly to False in the mfd_meta table ....for that sql file content .

SQL9 mfd 

SELECT au_fname, InStr(1,au_fname,'e') AS ["Pos e"], au_lname, InStr(1,au_lname,'ma') AS ["Pos ma"]

FROM authors;

Equivalenbt SQL Statement / argument for differents RDBMS : POSSTR ( DB2) , CHARINDEX ( MS SQL) , INSTR ( oracle) , InStr ( Access) , OVERLAY ( * PLACING * FROM * FOR *) ( standard) , LOCATE ( mySQL )

When i use the Query Builder filter that don't care of case and i enter InStr ,charindex, OVerlay ... i see no item that appear in the directory Statement !!

So my questions are :

-what is the equivalent of InStr in manifold V9 SQL ?

- does filter work on all reserveds words ( statement , operator ....) already available inside manifold 9 .

- Is there some syntaxes change to do before run the query ?

regard's

Attachments:
no_InStr.png


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

lionel

519 post(s)
#07-Jan-18 22:09

Hi

B) same question in http://www.georeference.org/forum/t138757.43 adam use " FROM CALL" .

SQL?

INSERT INTO t2 (b)

 SELECT (value DIV 2) * 5 FROM CALL ValueSequence(1, 100000, 1);

Where this Statement is locate in manifold 9 documentation ?

when i test manifold 9 query wizard i have not FROM CALL only EXECUTE WITH ...CALL

there is no CALL word in the Chris Fehily Edition after search in goole play book and i see there is no index inside the book ( manifold doc is still best ) !

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4008.htm

Regard's

Attachments:
manifold9_sql_CALL.png


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

Dimitri

5,119 post(s)
#08-Jan-18 05:56

Where this Statement is locate in manifold 9 documentation ?

It's not listed in the SQL Statements topic (should be... I've filed a request...) but it is in the Execute topic. CALL launches a query function, that is, a function which returns a table. You can find query functions listed in the SQL Functions topic.

when i test manifold 9 query wizard i have not FROM CALL only EXECUTE WITH ...CALL

See the SQL Functions topic for other examples.

there is no CALL word in the Chris Fehily Edition

and

- Is there some syntaxes change to do before run the query ?

Yes, of course. Fehily's book is a generic book on SQL. Part of his task in teaching SQL is explaining that various SQL implementations will differ from each other and that some adaptations are necessary from one system to another. CALL, for example, is a Manifold thing. His examples are aimed at some specific instances, so if you take his examples they will require some modifications, usually slight, for use in SQL implementations, for example Manifold, other than the specific system for which they were written.

None of that is unusual, since, of course, no one expects to take a sophisticated query written for PostgreSQL and use it with absolutely no modification in, say, Oracle. Same with Manifold.

Dimitri

5,119 post(s)
#08-Jan-18 06:03

what is the equivalent of InStr in manifold V9 SQL ?

Here is how to find it: Visit the SQL Functions topic and do a quick read of the various functions that begin with "String" in their names.

adamw


8,259 post(s)
#08-Jan-18 07:10

what is the equivalent of InStr in manifold V9 SQL ?

StringFind.

Example log:

#

> ? StringFind('lionel', 'e')

float64: 4

StringFindCollate is a variant for non-English languages.

does filter work on all reserveds words ( statement , operator ....) already available inside manifold 9 .

Are you asking if all reserved words / all available functions listed in the query builder lists on the left?

If so, yes. When we add a new keyword or a new function, it goes into the query builder immediately. Same for when we change or delete something.

lionel

519 post(s)
#08-Jan-18 10:39

I was thinking that manifold use already well know SQL term to define statement function and limit myself to this context . So yes manifold has it own term for statement and function !!! and yes search in html page documentation with predefine word ( find in common others SQL engine ) ll return nothing so need to use simple term relative to context !!

After first read the documentation about SQL i was thinking each term ll be discuss but see the term is discuss in it own context in relation with other SQL reserved word . this way is the same way tha SQL wizard !!

Thank's a lot !


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

lionel

519 post(s)
#09-Jan-18 00:34

Hi

Does simplify in mfd8 is the same that geomSmooth in mfd9 ?

Thank's


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

adamw


8,259 post(s)
#09-Jan-18 07:14

Yes. Save for an option to remove small branches which GeomSmooth does not have.

lionel

519 post(s)
#11-Jan-18 23:30

i try to concat to string column using SQL9 and table inside Fehily book listing0580

SQL9

SELECT [au_fname], [au_lname], [au_fname]   ||  [au_lname]  AS [Author name]

 FROM [authors] 

 WHERE [au_lname] = 'Hull'

 ORDER BY [au_lname] ASC, [au_fname] ASC; 

if i remove """[au_fname] || """ then the Code Run !!

i try using the SQL wizard but no reserved word but don't know wich item do concatenation .

other SQL engine use CONCAT( arg,arg) , + ....

after test it seem this code run

code

SELECT [au_fname], [au_lname], [au_fname]   + ' ' +  [au_lname]  AS [Author name]

 FROM [authors] 

 WHERE [au_lname] = 'Hull'

 ORDER BY [au_lname] ASC, [au_fname] ASC; 

So concat empty string = <spacebar> don't use double quote "<space> " but single like ' <space>'

So for concat use operator + but not operator ||


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

lionel

519 post(s)
#11-Jan-18 23:57

NB the manifold forum for style SQL code don't support empty value space inside single quote ' '


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

adamw


8,259 post(s)
#12-Jan-18 08:34

(Use --SQL9 with two leading dashes and not just SQL9 in the code section to tell the parser that the code is SQL.)

Dimitri

5,119 post(s)
#12-Jan-18 08:33

So for concat use operator +but not operator ||

For concatenation use the ampersand & operator. There is an example of that in the SQL Example: Using Selection Query Functions topic.

By the way, if you are ever puzzled how to do something in 9, launch the Transform pane and click on a transform that does something similar, then press the Edit Query button and take a look at the SQL it writes.

For example, to learn about concatenation, point the Transform panel at a string field and click on the Concatenate transform and then press Edit Query. Right away you'll see a fully-complete, real-life example of the use of the & operator. SQL Server style + also works to concatenate strings, but & is used in the templates so that is what I always use.

Forgot to mention: when learning SQL from Fehily, one of the big benefits of his books is how he discusses variations between different SQL engines. If you just try code from the book without reading the book you don't get the benefit of that. For example, in the DMBS differences guide where he discusses concatenating strings in the SQL: Visual Quickstart Guide he does a very good job of explaining how different systems do this differently, with Microsoft things like Access and SQL Server using +, with || meaning logical OR in MySQL, but also legal to use for concatenation.

lionel

519 post(s)
#11-Jan-18 23:52

is there a way to add space in SQL using something like """Chr(?)""" we find use in vbscript but for SQL

SQL9

CAST ('cstr(32)' AS varchar) AS [space] 

i was thinking i could use html code """&nbsp;""" that html engine render to """ """ ( a white space )

regard's

VBCrLfA carriage return and line feed [Chr(13) + Chr(10)]

vbBackA backspace character [Chr(8)]


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

adamw


8,259 post(s)
#12-Jan-18 08:41

You can use Chr or specify the Unicode value of the character using four hexadecimal digits: '\uXXXX'.

For example (SQL log, ignore the first line which is there to turn off the forum parser):

#

> ? 'Z' & Chr(246) & 'e'

nvarchar: Zöe

> ? 'Z\u00f6e'

nvarchar: Zöe

For new line characters, you can also use a built-in CrLf constant:

#

> ? 'Line 1' & Chr(13) & Chr(10) & 'Line 2'

nvarchar: Line 1

Line 2

> ? 'Line 1' & Cr & Lf & 'Line 2'

nvarchar: Line 1

Line 2

> ? 'Line 1' & CrLf & 'Line 2'

nvarchar: Line 1

Line 2

> ? 'Line 1\u000d\u000aLine 2'

nvarchar: Line 1

Line 2

Hope this helps.

lionel

519 post(s)
#11-Feb-18 17:57

i come back to the forum because need information about compute string . ..So your answers help a lot .

For each component both content.Select and content.transform help a lot to write code and is wonderfull.

The selection using drag area with +/- is also very usefull !!!

in content editor i take care now of query and command component and first line like -- $manifold$ or --SQL9 ( radian has goo article about this perhaps also manifold 9 )

Whatever the nature of my suggestions by mail , I appreciate more and more mfd9 !!


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

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