SQL AS COMMAND

Question

Good night!

So guys, I have a table where it has some columns, among these: data_current and data_validity, as soon as a record is made an auxiliary column is generated through the AS command of sql, this column saves the information 'validate' or 'invalidate' depending on the result after the registration of the given ones, for example if data_current is less than dat_validity the column receives 'validate' otherwise it receives 'invalida' until then everything right, the bid ta at the time to search the output of the auxiliary column with the LIKE command, type at one time wanted to bring only the result 'validate' and in another 'invalida' however I am not getting, someone can give a force?

Answer 1

The sql AS command does not generate a new column. It only serves to create an alias (shortcut) for some attribute of your table or result of an expression in the querie. It is very useful when we have queries in two or more related tables to avoid collision between attributes with the same name. An example :

SELECT C.NOME_PAGINA,CAT.LINK_CATEGORIA,CAT.STATUS,C.ID_CONTEUDO,C.STATUS FROM 
CONTEUDO C INNER JOIN CATEGORIAS CAT ON CAT.ID_CATEGORIA = C.ID_CATEGORIA

In the above querie both tables have the attribute STATUS to avoid creating confusion we can do :

SELECT C.NOME_PAGINA,CAT.LINK_CATEGORIA,CAT.STATUS AS CAT_STATUS 
,C.ID_CONTEUDO,C.STATUS AS CONTEUDO_STATUS FROM 
CONTEUDO C INNER JOIN CATEGORIAS CAT ON CAT.ID_CATEGORIA = C.ID_CATEGORIA

Create a temporary dummy names CAT_STATUS and CONTEUDO_STATUS to access the data return. There are other situations that we also need to use the AS.

Porting within your table there should be the supposed attribute auxiliary mentioned in your question so that you can filter the results you want for it. Now if your alias Auxiliary is a conditional result generated by the querie and there is no attribute in the table and you want to filter it. Could solve as in the example below:

SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3)  AS Col1
FROM MyTable
WHERE SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) = 'MySearch'

Just repeating the expression on WHERE.