Where condition alternative for is not initial

[Former Member]

Hello

Will Variant 1 work with all data types like Variant 2.
I don't want to use Variant 2 because the performance is important.

But im not sure if Variant 1 works with all data types or only with characters. Because for example Dates are saved with 0000000 and not '' in the Database.

Variant 1

  SELECT * FROM table INTO ls_table WHERE field <> ''.

    DO something.

 ENDSELECT.

Variant 2

 select * from table into ls_table.

    if field is not INITIAL. 

        do something. 

    endif. 

  endselect. 

[horst_keller]

Good question, I'll examine if we have a workaround for ABAP's IS INITIAL in Open SQL ...

[raghug]

That would be nice... I catch myself often writing IS INITIAL in my select statements!

[horst_keller]

You can use host expressions with VALUE #( ) as shown above from 7.50 on.

Maybe we'll offer a real IS INITIAL in Open SQL in an upcoming release.

[horst_keller]

If type inference works (I'm not logged on to the ABAP system), the following might do ...

... WHERE field <> @( VALUE #( ) )

[Former Member]

Thanks. Sadly i'm not on 7.4 i don't have the Value Operator. So i can't test it.

[horst_keller]

It works from 7.50 on, when host expressions were introduced

SELECT *
 FROM scarr
 WHERE carrid <> @( VALUE #( ) )
 INTO TABLE @DATA(result).

[fabianlupa]

Horst's solution does work (to my surprise):

SELECT * FROM t000 WHERE logsys <> @( VALUE #( ) ) INTO TABLE @DATA(gt_out).

If your release does not support it yet I suppose you can always create a dummy line and use that since all its values are initial.

DATA: ls_dummy  TYPE t000,
      lt_result TYPE STANDARD TABLE OF t000.
SELECT * FROM t000 INTO TABLE lt_result WHERE logsys <> ls_dummy-logsys.

I was looking for constants of initial values for each predefined type so you do not have to declare the dummy variable and make the code more readable but could not find them.

[michał_badura]

How about this?

CONSTANTS: lcv_dummy_logsys TYPE t000-logsys VALUE IS INITIAL.

[fabianlupa]

Sure, I was more looking for something like cl_abap_exceptional_values=>char_initial_value to avoid the helper variable all together. For just comparing one field your approach is the better option though!

[matt]

Given that usually you know the type, I've not had a problem. I use WHERE field EQ SPACE or WHERE field NE SPACE in most cases. However, where I've needed to check for initial date, I've declared

DATA initial_date TYPE d.

And used initial_date in the where clause. I didn't use a constant, because that requires setting a value.

[SuhaSaha]

Like Raghu i find myself writing IS INITIAL in Open SQL, until the Sytax-Check reminds me that i can't.

So i sometimes ask myself why is IS INITIAL not offered with Open SQL.

[horst_keller]

So i sometimes ask myself why is IS INITIAL not offered with Open SQL.

Cause nobody asked us before ...

Thanks to this thread here, the Open SQL team is considering it now, really ...

[matt]

We didn't know who to ask!

[horst_keller]

Yes, we'll offer a real IS INITIAL in Open SQL in an upcoming release.

I just documented it.

Thanks for asking for it!

[Tomas_Buryanek]

Great news! Thank you! 🙂

[J4NF31ST]

Hi @horst_keller is that "already" implemented?

Because it still doesn't work 6 years later in release 7700.1.10.3395

Best regards

[horst_keller]

https://help.sap.com/doc/abapdocu_latest_index_htm/latest/en-US/index.htm?file=abennews-753-abap_sql...

[Sandra_Rossi]

---

@J4NF31ST wrote:

it still doesn't work 6 years later in release 7700.1.10.3395

---

You are indicating the SAP GUI for Windows release, but your question is about "IS INITIAL" ABAP SQL, so what is your ABAP version? (see the link given by Horst, it's the release news about ABAP 7.53)

[J4NF31ST]

Thanks, I guess this can only be used as an ABAP SQL-cond and not in the SQL editor in the GUI?

Because using it in the GUI just returns: sql syntax error: incorrect syntax near "initial"

[horst_keller]

Of course, the ABAP SQL syntax cannot be transferred to the DB Cockpit. You enter native SQL in the SAP GUI SQL Editor. If the underlying DB does not support IS INITIAL, you cannot use it.

ABAP SQL's IS INTIAL depends on ABAP data types (built-in DDIC type) and must be translated by the DBI to a native SQL condition.

[J4NF31ST]

underlying DB is S4 HANA database release 755.

[Sandra_Rossi]

As Horst said, you seem to run HANA SQL, not ABAP SQL. "IS INITIAL" is only for ABAP SQL.