on 11-25-2011 12:54 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
> 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
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
>
> 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
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
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
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
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
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
Thanks every body for help.
The transaction DBACOCKPIT has a SQL Command Editor (Performance > Additional Functions).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
9 | |
9 | |
7 | |
7 | |
6 | |
6 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.