Advanced SQL: How to remove column with empty values

Advanced SQL: How to remove column with empty values

  

Hi All,

There is a possibility that there is a empty value for the column. So i would like to check how do i remove the row with empty value?

Thanks

Hello Jace,

Not enough information. What do you mean by EMPTY?

Are you saying that the field in a row can be NULL?

By default, in OutSystems tables, the only way to a NULL to appear as the value of a field is if you are doing a LEFT JOIN and there is no match in the table being joined. In this case, during the execution of the query, the fields of that entity for the lines were there isn't match will be NULL. 

In this case, if you want to filter out those lines, use the IS NOT NULL operator on the entity id in the WHERE clause.

If you are just talking about records that where inserted without some values, than you must filter against the default value, the one you provided in the Default property of the attribute, or the data type default values, as when inserting records, the platform will give values if you don't provide one, like 0 to integers, "" to text, False to booleans, etc.

Cheers

Eduardo Jauch

Eduardo Jauch wrote:

Hello Jace,

Not enough information. What do you mean by EMPTY?

Are you saying that the field in a row can be NULL?

By default, in OutSystems tables, the only way to a NULL to appear as the value of a field is if you are doing a LEFT JOIN and there is no match in the table being joined. In this case, during the execution of the query, the fields of that entity for the lines were there isn't match will be NULL. 

In this case, if you want to filter out those lines, use the IS NOT NULL operator on the entity id in the WHERE clause.

If you are just talking about records that where inserted without some values, than you must filter against the default value, the one you provided in the Default property of the attribute, or the data type default values, as when inserting records, the platform will give values if you don't provide one, like 0 to integers, "" to text, False to booleans, etc.

Cheers

Eduardo Jauch

Hi,

I talking about records that where inserted with some values. I did not set any default value for the attribute. I did try to search by "" but it will still return me the empty row.

SELECT * FROM TEST
WHERE TestName <> ''


Can you show the exactly query you are doing, please?

Eduardo Jauch wrote:

Can you show the exactly query you are doing, please?

SyntaxEditor Code Snippet

WITH Staff_Name
AS (
SELECT DISTINCT REGEXP_SUBSTR(TeacherAssistantName, '[^|]+', 1, level) AS TName
FROM (
    SELECT LISTAGG(TeacherAssistName, '|') 
                   WITHIN GROUP (ORDER BY TeacherAssistName) AS TeacherAssistantName
    FROM (
          SELECT DISTINCT {School}.[TeacherName], {School}.[AssistantName] AS TAName
            FROM {School} 
            WHERE {School}.[SubjectCode] = @SubjectID
        )
    )
CONNECT BY REGEXP_SUBSTR(TeacherAssistantName, '[^|]+', 1, level) is not null
)

SELECT REPLACE(TName, '|', CHR(10))
FROM Staff_Name
ORDER BY TName DESC



The column for TeacherName and Assistant name might be empty in the table. Thatwise when i run this query, it will return empty row.

Hi Jace,

In Outsystems an empty/default string is a blank/space. Filter on an space, i.e. " ". Maybe beter is to supply a parameter, e.g. EmptyString with NullTextIdentifier().

Don't know if you use this query more then once, otherwise you could think of putting it in your Oracle database as a view.

Cheers,

Menno

hi Jace,

Check this link, the default values (empty text) can be different between Oracle and other DBMS.

https://www.outsystems.com/help/servicestudio/9.0/Using_Data/Default_Values_on_database.htm

regards,

IB

I Box wrote:

hi Jace,

Check this link, the default values (empty text) can be different between Oracle and other DBMS.

https://www.outsystems.com/help/servicestudio/9.0/Using_Data/Default_Values_on_database.htm

regards,

IB

Hello,

Thanks for the information. I manage to solve the problem


Jace Jace wrote:

I Box wrote:

hi Jace,

Check this link, the default values (empty text) can be different between Oracle and other DBMS.

https://www.outsystems.com/help/servicestudio/9.0/Using_Data/Default_Values_on_database.htm

regards,

IB

Hello,

Thanks for the information. I manage to solve the problem


Hi Jace,

Would you mind sharing your solution how you solved the problem? Nice as a reference for people who run into a similar problem.   

Cheers,

Menno

Solution

The solution to remove the row is as following:


WITH Staff_Name
AS (
SELECT DISTINCT REGEXP_SUBSTR(TeacherAssistantName, '[^|]+', 1, level) AS TName
FROM (
    SELECT LISTAGG(TeacherAssistName, '|') 
                   WITHIN GROUP (ORDER BY TeacherAssistName) AS TeacherAssistantName
    FROM (
          SELECT DISTINCT {School}.[TeacherName], {School}.[AssistantName] AS TAName
            FROM {School} 
            WHERE {School}.[SubjectCode] = @SubjectID 
        )
    )
CONNECT BY REGEXP_SUBSTR(TeacherAssistantName, '[^|]+', 1, level) is not null
)

SELECT REPLACE(TName, '|', CHR(10))
FROM Staff_Name
WHERE TNAME <> ' '
ORDER BY TName DESC
Solution