topic SQL Query using DATS data type in Application Development and Automation Discussions

SQL Query using DATS data type

Former Member — Thu, 17 May 2007 13:58:29 GMT

Hello people,

I have a table 'TableA' with a column 'xpto' that has DATS data type.

A lot of lines in this table has this field empty.

I have to create a SQL query to select those lines with this field not empty.

Select * from TableA

where xpto is not null

It does not work.... The result is all the lines in the table.

What is wrong?

Thanks!

Re: SQL Query using DATS data type

Former Member — Thu, 17 May 2007 14:00:27 GMT

select * from tableA where xpto = ''.

Re: SQL Query using DATS data type

Former Member — Thu, 17 May 2007 14:01:10 GMT

select * from tableA where xpto ne ''.

Re: SQL Query using DATS data type

Former Member — Thu, 17 May 2007 14:03:13 GMT

I have already tried

ne ''

<> ''

and those ones dont work too........

Re: SQL Query using DATS data type

Former Member — Thu, 17 May 2007 14:06:07 GMT

Easiest way to do this is :

constants l_xpto type tableA-xpto value is initial

Select * from TableA

where xpto ne l_xpto.

You can see the NULL/NOT NULL settings for the column through SE11

Utilities -> Database Object -> Display.

I tend to set the "Initial Value" flag when adding columns to tables, which creates the database column as NOT NULL with a default value (whatever SAP considers the initial value for that datatype). I think SAP does this by default, but I've had issues where it hasn't so I do this to ensure consistency

Chris Bain

Re: SQL Query using DATS data type

Former Member — Thu, 17 May 2007 14:15:49 GMT

This works:


DATA: dt LIKE sy-datum.

SELECT SINGLE * FROM bkpf WHERE budat <> dt.

Rob

Re: SQL Query using DATS data type

Former Member — Fri, 18 May 2007 19:35:03 GMT

Thanks a lot everybody!

When I create a blank variable, it works!