Subscribe to this thread
Home - General / All posts - mix alias or not forbidden
lionel

916 post(s)
#13-May-22 13:39

i read the old post that can be read not edit as alias in SQL (georeference.org)

A) if i use an alias i should use alias in all the SQL query text ?

B) in join there is 2 tables/drawings, it is nice that i don't have to use explicit column name by using Drawing/table name before column name

C) what s the default value of a cell where content has not been initialized ? which query should i use to select row that contain a column with this value ?

after insert a new value and then remove this value . remove seem to use cursor in the right end and use "retour arriere " key or del key , after save/update ... which query should i use to select row that contain a column with this value ? value that appear white color fill cell that is not NULL that appear in grey !!

I post a new join infography call SQL_join_mai2022.png better than the previous . redo this make me think again of what mean a cell text type that contain

-<null> ( in grey default)

-"<null>" black or "un" or any text ( value)

-"" (an empty cell with no space )

-" " ( an cell with 2 space or tab "

and how to come back from real content text ( white or black) to grey <NULL>

regard's

Attachments:
mix-Alias_forbidden.png
nomix_Alias_ok.png
shortname_SQL_OK.png
SQL_inner-intersect_v9.map
SQL_join_mai2022.png
textTable_test-different-value-cell.png


union, doc , APIand most important deepl & keyboard shortcut

lionel

916 post(s)
#13-May-22 14:00

for the infography about all the way to use join, because we need to use a common column to do the join thatwe can see than in the rendering table of SQL : because we explicit call the same column for each different table ; manifold rename it using an increment number (here 2) . Look at the FULL OUTER JOIN to see the 2 columns and theirs contents

Attachments:
fullOuterJoin.png


union, doc , APIand most important deepl & keyboard shortcut

adamw


9,956 post(s)
#13-May-22 14:58

A - if you use an alias for a table, you lose the ability to refer to fields from that table using the name of the table, have to use the alias.

B - referring to a field by its name without adding the name of the table works as long as the field name is unambiguous (if you join two tables P and Q which both have a field named ID, for example, 'ID' is ambiguous, you have to specify which exact field you mean by writing either 'P.ID' or 'Q.ID').

C - (1) the default value for a field in a new record is NULL, (2) if you want to select records with NULLs, use 'SELECT * FROM t WHERE f IS NULL', (3) to set some value in a record to NULL, right-click the value and select Delete.

lionel

916 post(s)
#16-May-22 12:07

thnak's a lot i test again and yes :

for delete cell content we use the contextual menu and for varchar , character content is replace by <NULL> grey

For delete row we don't have a contextual menu call delete but after selected the row we have to press on the key "del" on the keyboard and then confirm the delete process by click on ok button !!

i need to test to know what is the content of a blank cell ( inside black rectangle ). I

This cell is empty and contain no character inside it !!

SELECT ..WHERE colname = ?? ;

thank's

Attachments:
cellcontent_delete.png
cell_blank_value.png
row_delete.png


union, doc , APIand most important deepl & keyboard shortcut

adamw


9,956 post(s)
#16-May-22 14:51

Select all records with a NULL value in a field named 'pop':

--SQL9

SELECT * FROM [table] WHERE [pop] IS NULL;

Find a record with ID = 200 (as an example) and set its 'pop' to NULL:

--SQL9

UPDATE [table] SET [pop] = NULL WHERE [id] = 200;

You can also use the Select pane for the former and the Select pane + Delete on a single cell + Copy to Selection for the latter.

Hope this helps.

lionel

916 post(s)
#17-May-22 00:01

1) when we use nvarchar column , the cell value can be empty or <null> or contain real value like "<NULL>". So SQL we must use WHERE [texte] ='' ( 2 single quote with no values that mean empty) or [texte] IS NULL or [texte] ="<NULL>"

2) when we use int column ; the cell value can be empty so delete a value will give cell with content <NULL> ( the grey one that only manifold can insert if we enter no value string. enter 2,67 or 2.67 ll convert number to 2. so manifold reformat the value to be int . entrer nothing by delete the previous number ll make appear <NULL> in grey ...

So does text column should behave like int for empty value ? should a empty cell content for cell of column of type text should be automaticly convert by manifold to <NULL> grey for easy query result ?. In a way text with empty string or <NULL> grey should show in Label layout the same thing that is rendering blank both for '' or <empty> . But when use a label layer...does a label with an empty/no string behave like a <NULL> grey ? ( i think yes) . Is there a case where be able to know that cell content is "" or <NULL> is important and make sense ?. In both case the meaning seem the same , the cell is not initialized and no affectation occur !! So does it make sense to replace empty content for String column by <NULL> automaticly by manifold when it detect that there is no cell value ( user remove the string value so cell table appear blank but manifold will convert empty to <NULL>)


union, doc , APIand most important deepl & keyboard shortcut

adamw


9,956 post(s)
#17-May-22 14:13

For text types (VARCHAR / NVARCHAR), an empty string is different from a NULL value. Some databases do this differently, most notably, Oracle - in Oracle, setting a text value to an empty string is equivalent to setting it to a NULL. But most databases do it similarly to 9.

For numeric types, there is no concept of an empty numeric value, each numeric value is either a valid number (possibly 0) or a NULL.

There is an interesting case with binary values. In 9, there is currently no concept of an empty binary value, there is just a NULL. A binary value of zero length is interpreted as a NULL. Apparently, many databases make a distinction between empty binary values and NULL binary values similarly to strings. We will likely do the same in the future.

When a label references a field value and that field value is a NULL, regardless of its type, that NULL is inserted into label text as an empty string.

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