Question Re: Executing SQL queries in SAP-GUI e.g. select * from but000 in Technology Q&A

Executing SQL queries in SAP-GUI e.g. select * from but000

Former Member — Fri, 25 Nov 2011 12:54:17 GMT

Hallo,

I am newbie in the SAP world. Is there a way to run select statements from SAP GUI? e.g. I want to know how many rows are returning from a join xyz.

select count() from tabA and tabB where tabA.id = tabB.id and tabA.Name is not null.*

Is it possible with SQVI (SQ01)?

Please help.

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

aena_dtiddsap — Fri, 25 Nov 2011 13:13:12 GMT

Hi,

I'm not using 4.7E, but I thinkl it could be in transaction ST04N --> Oracle Database Administration --> Additional Functions --> SQL Command Editor.

Regards,

Nere

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

stefan_koehler — Fri, 25 Nov 2011 14:18:59 GMT

Hello thewesen,

yes you can execute queries with the SQL command editor like mentioned by Nere before. The full navigation path is "Transaction DBACOCKPIT -- Performance -- Additional Functions -- SQL Command Editor".

By the way you don't need to specify any "IS NOT NULL" condition in your queries, because of in a SAP environment every column is defined with a NOT NULL constraint. The optimizer is aware of this, but to simplify your queries you can remove this stuff

Regards

Stefan

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

Former Member — Fri, 25 Nov 2011 14:43:07 GMT

Hi,


select count() from tabA and tabB where tabA.id = tabB.id and tabA.Name is not null.

You can either user Abap query (SQ01) or ABAP editor (SE38) to write respective piece of code and execute the same.

Regards,

Deepak Kori

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

volker_borowski2 — Fri, 25 Nov 2011 15:53:42 GMT


> By the way you don't need to specify any "IS NOT NULL" condition in your queries, because of in a SAP environment every column is defined with a NOT NULL constraint. The optimizer is aware of this, but to simplify your queries you can remove this stuff 
> 
> Regards
> Stefan

Sorry to correct you Stefan,

when you add a Column to an existing table, the DB conversion will execute

an "alter table add column" and the added column will not have a null constrataint.

The SAP Kernel is (sometimes ?) aware of this, so it (eventually ?) transforms querys in a way like


select field1, nvl(field2, ' ' ) as field2, ...

... so the query will in fact not return NULL to SAP, but the table may contain NULL.

I have seen such conversions in SQL-Traces and I have been wondering where this comes from.

So if you execute native queries, you might need to take NULL Colums into account.

And even more: You need to be extremely carefull, if you intend to use an "add column" field

in an index, because NULL Values will not be indexed.

And since version 11 it is again different, because now you can add a column with a not null constraint,

provided the fact that you define a DEFAULT value in addition. But there are gotchas as well:

The NULL VALUES are not updated with real data (otherwise the ADD COLUMN would take too long),

so changing the default value will have the same effect as updating all records that have NULL values.

I have not fully tested the stuff around this (I have this scheduled for the week between chrismas and new year )

but I'll come back with results on that.

Volker

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

stefan_koehler — Fri, 25 Nov 2011 16:10:12 GMT

Hello Volker,

Sorry to correct you Stefan,
when you add a Column to an existing table, the DB conversion will execute
an "alter table add column" and the added column will not have a null constrataint.

No problem at all ... but i was confused about your counterstatement, so i just created a table (with CHAR fields) and added a "non key" column (CHAR) without an initial value afterwards.


** BEFORE
SYS@<SID>:229> desc SAPSR3.ZZMYTEST;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEST					   NOT NULL VARCHAR2(9)
 TEST2					   NOT NULL VARCHAR2(117)

--- Now i added a new column called NEWCOL to the ABAP DDIC and activated the table.
--- The column NEWCOL was not a key column and the checkbox for an 
--- initial value was *NOT* checked

** AFTERWARDS
SYS@<SID>:229> desc SAPSR3.ZZMYTEST;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEST					   NOT NULL VARCHAR2(9)
 TEST2					   NOT NULL VARCHAR2(117)
 NEWCOL 				   NOT NULL VARCHAR2(117)

As you can see you always have a NOT NULL constraint on it - even for new added columns. My SAP system was an ECC 7.01 with 7.01 Kernel. I also have never seen the SAP kernel using the NVL() function - but that does not mean that the kernel doesn't do that of course )

Update: Now i see what you mean - i checked sapnote #1387135 ... so you are right too .. thanks for the correction )

Regards

Stefan

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

volker_borowski2 — Fri, 25 Nov 2011 16:41:03 GMT


> 
> Update: Now i see what you mean - i checked sapnote #1387135 ... so you are right too .. thanks for the correction ) 
>

yes, exactly this is the new feature.

Very valuable for big bw tables which you migh otherwise no be able to tackle at all.

But also very dangerous if you are not aware if this is in place.

The values on the DB will be still NULL Values and only a query will deliver data.

What happens if such a column is indexed ???

What happens if you change the default value ???

And what happens to old stuff that is still in from older versions?

I am already at home now, but I am going to do some work on the weekend,

may be I can throw in a version 10 example and some stuff around this.

Volker

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

volker_borowski2 — Sat, 26 Nov 2011 09:25:06 GMT

Hi again,

here I have a "tail" of "desc SAPSR3.BKPF" on a system that was installed with ERP 6.0 four years ago

and got some SPs meanwhile:


:
:
 SAMPLED                                   NOT NULL VARCHAR2(3)
 EXCLUDE_FLAG                              NOT NULL VARCHAR2(3)
 BLIND                                     NOT NULL VARCHAR2(3)
 OFFSET_STATUS                             NOT NULL VARCHAR2(6)
 OFFSET_REFER_DAT                          NOT NULL VARCHAR2(24)
 PENRC                                     NOT NULL VARCHAR2(6)
 KNUMV                                     NOT NULL VARCHAR2(30)
 VATDATE                                            VARCHAR2(24)
 XSPLIT                                             VARCHAR2(3)

SQL>

so the fields that did come in later do not have a NOT NULL (yet?).

This is one of our systems not yet upgraded to version 11.

Volker

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

stefan_koehler — Sat, 26 Nov 2011 22:08:05 GMT

Hello Volker,

thanks for that real life example. Until now i have never seen such a behavior on (our) SAP systems, but as this a wanted behavior by SAP for prior 11g R2 database releases (regarding sapnote #1387135) it could be titled as "works-as-designed".

It is even worse if you as a customer add some Z columns and want to index them in an OLTP environment (B*Tree indexes). It is some kind of blind flight, because of SAP is not continuous in this case.

I am pretty sure that the ABAP developers are not aware of that database internals (B*Tree indexes contain no NULL values, if NULLABLE columns are indexed only).

Thanks again for pointing out this case - pretty awesome that we can learn new stuff from each other here every day )

Regards

Stefan

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

volker_borowski2 — Sun, 27 Nov 2011 08:05:59 GMT

Testcase:


SQL> create table scott.testit 
     ( id number not null, 
       value1 varchar2(10) not null ) 
     tablespace DATA;
Table created.

SQL> desc scott.testit;
Name       Null?    Type
---------- -------- --------------------------------------------
 ID        NOT NULL NUMBER
 VALUE1    NOT NULL VARCHAR2(10)

SQL> insert into scott.testit (id,value1) values ( 1, 'Hello' );
1 row created.

SQL> commit;
Commit complete.

SQL> select * from scott.testit;
        ID VALUE1
---------- ----------
         1 Hello

ADD COLUMN, the old fashioned way


SQL> alter table scott.testit add  ( ADDFIELD1 varchar2(5) );
Table altered.

SQL> desc scott.testit;
Name        Null?    Type
----------- -------- --------------------------------------------
 ID         NOT NULL NUMBER
 VALUE1     NOT NULL VARCHAR2(10)
 ADDFIELD1           VARCHAR2(5)

SQL> select * from scott.testit where ADDFIELD1 is null;

        ID VALUE1     ADDFI
---------- ---------- -----
         1 Hello

Works as expected

Try to get NOT NULL and DEFAULT to work


SQL> alter table scott.testit modify ( ADDFIELD1 NOT NULL );
alter table scott.testit modify ( ADDFIELD1 NOT NULL )
*
ERROR at line 1:
ORA-02296: cannot enable (SCOTT.) - null values found

SQL> alter table scott.testit modify  ADDFIELD1 default '000';
Table altered.

SQL> alter table scott.testit modify ( ADDFIELD1 NOT NULL );
alter table scott.testit modify ( ADDFIELD1 NOT NULL )
*
ERROR at line 1:
ORA-02296: cannot enable (SCOTT.) - null values found

No suprise so far. You would usually need to update all NOT NULL

values to some values and you would be able to enable the NOT NULL constraint

allthough this may run for quite a while on big tables.

Now lets try the new stuff


SQL> alter table scott.testit drop column ADDFIELD1;
Table altered.

SQL> alter table scott.testit ADD ADDFIELD1 varchar2(3) DEFAULT '000' not null;
Table altered.

SQL> desc scott.testit
Name        Null?    Type
----------- -------- --------------------------------------------
 ID         NOT NULL NUMBER
 VALUE1     NOT NULL VARCHAR2(10)
 ADDFIELD1  NOT NULL VARCHAR2(3)     <<<< BING !!!

SQL> select * from scott.testit;
        ID VALUE1     ADD
---------- ---------- ---
         1 Hello      000            <<<< Default '000' is working

SQL> select * from scott.testit where ADDFIELD1 is NULL;
no rows selected                     <<<< NOW this might be suprising

SQL> insert into scott.testit (id,value1,addfield1) values (2,'Bye', '000');
1 row created.

SQL> commit;                         <<<< Trying to compare "real" '000' with DEFAULT '000'
Commit complete.

SQL> select * from scott.testit;
        ID VALUE1     ADD
---------- ---------- ---
         1 Hello      000            <<<< Added with default
         2 Bye        000            <<<< inserted as '000'

SQL> alter table scott.testit modify ADDFIELD1 default '111';
Table altered.


SQL> select * from scott.testit;     <<<< Now it gets exciting
        ID VALUE1     ADD
---------- ---------- ---
         1 Hello      000            <<<< WOA... How does this work?
         2 Bye        000


SQL> set longC 20000 long 20000
SQL> select dbms_metadata.get_ddl('TABLE','TESTIT','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','TESTIT','SCOTT')
----------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."TESTIT"
 (  "ID" NUMBER NOT NULL ENABLE,
    "VALUE1" VARCHAR2(10) NOT NULL ENABLE,
    "ADDFIELD1" VARCHAR2(3) DEFAULT '111' NOT NULL ENABLE           <<<< No '000' DEFAULT
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATA"

SQL>

Looks like Oracle is at least a whole lot more clever than I expected.

It must have stored the first Default value somewhere else, as the documentation

says, that the effective rows will NOT be updated (otherwise it would never work so fast).

I need to dig into how datablocks are dumped and read.

Just to finalize this:


SQL> alter table scott.testit modify ADDFIELD1 NULL;
Table altered.

SQL> select * from scott.testit;

        ID VALUE1     ADD
---------- ---------- ---
         1 Hello      000
         2 Bye        000

SQL> select * from scott.testit where addfield1 is null;
no rows selected

SQL>

So the change persists even if you revert the constraint allthough the data

should not been changed. Surely need to do a datablock dump of this.

Need to do additional tests with indexes.

But right now I am running out of time.

May be someone else likes to join the expedition.

Volker

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

Former Member — Mon, 28 Nov 2011 08:14:49 GMT

@Stefan:

I am pretty sure that the ABAP developers are not aware of that database internals (B*Tree indexes contain no NULL values, if NULLABLE columns are indexed only).

You absolutely right about that - and that is dangerous circumstance.

Thx a lot

Edited by: thewesen on Nov 28, 2011 9:15 AM

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

Former Member — Mon, 28 Nov 2011 08:38:53 GMT

Hi Volker and Stefan,

thanks a lot four your replays. You did not answer my question but this is due to unspecific character of my question. And on the other hand I am happy you didn't, then otherwise I wouldn't have had a chance to learn about the SAP and DB internals. So thanks again.

what I was actually looking for is a form of SQL command prompt in SAP CRM 7.1. I am looking for that because I am trying to validate CRM Segmentation( e.g. you got n Customers and want write email to only ones that are located in xyz, so you use the Segment Builder and select the Segment and so on...) so we are "selecting" (it is not SQL like selecting but a graphical user interface with small icons and boxes ) customers based on a field that can be empty or contain a char. We want go get all customers that do have a flag, so we select

field not ' '(blank)

because blank is representing the NULL value. My problem is that this selection returns wrong numbers. so to validate I am using the SQ01 but there I am also not sure what this program is actually doing on the DB side. So to get certainty I thought about running true-SQL against the tables.

I could not imagine that there isn't a way to comfortable run SQLs in SAP - by all the complexity.

Best regards

PS.

I work for a big company with tight security regulations - so I cannot access the DB directly.

Edited by: thewesen on Nov 28, 2011 9:40 AM

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

volker_borowski2 — Mon, 28 Nov 2011 11:04:22 GMT

Hi,

at least, you could do a SE14 -> Check DB Object

which will show you, if you MIGHT need to deal with real DB-NULL values or not.

If all columns are NOT NUL, you might have different Problems.

Volker

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

stefan_koehler — Mon, 28 Nov 2011 11:45:58 GMT

Hey

what I was actually looking for is a form of SQL command prompt in SAP CRM 7.1.

Well you got something like that .. from my previous reply: The full navigation path is "Transaction DBACOCKPIT -- Performance -- Additional Functions -- SQL Command Editor". You can write your own SQL like in SQL*Plus and run it on the database there.

If you don't have a NULL issue here - you maybe hit the "prefetching" bug - depending on the used JOIN method and oracle version of course )

Regards

Stefan

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

Former Member — Wed, 30 Nov 2011 13:03:45 GMT

You right DBACOCKPIT is the thing!

I assumed this will be only available for SAP DBAs.

that is GREAT!

Thanks a lot

Re: Executing SQL queries in SAP-GUI e.g. select * from but000

Former Member — Wed, 30 Nov 2011 13:08:30 GMT

Thanks every body for help.

The transaction DBACOCKPIT has a SQL Command Editor (Performance > Additional Functions).