I have revised Y4SQL to act as a mini report writer.
Attached is Yes4SQL.txt the ABAP source code and Yes4SQL100.txt the Screen for Upload in SE80.
In SE80 create screen 100 and upload the screen text.
Manually create a GUI-title TITLEYES4SQL and GUI-status MAIN100
Status MAIN100 will have 2 buttons EXIT and EXEC.
In the program you will see 2 lines
SET
PF-STATUS 'MAIN100'.
SET TITLEBAR 'TITLEYES4SQL'.
WHEN 'EXIT'.
WHEN 'EXEC'.
Good Oracle/Native SQL skills needed; (your native could be DB2 or MSSQL);
Unfortunately Y4SQL error handling is limited - to avoid many ST22 dumps.
BEGIN "Report heading
Field type(optional) "Column Heading
Report heading is optional and max 50 characters;
Column Heading is optional and max 10 characters
-- if you omit Column Heading it will take from SAP Dictionary.
Here is brief write-up of Y4SQL
For simple things with 1 table SE16N is easier & better
SQL tool
for SAP ABAP Programmers - BOTH OPEN & NATIVE SQLs
very
light - approx. 20KB
Objective
- to see JOINS in SAP to confirm or discover relationships
and to see
Data side by Side
Read
"SAP Table and Field search strategies"
to navigate the cryptic tables & Columns of SAP
If you wish to see the relationships in DATA VISIBLE format NOTHING succeeds like JOINs
BEGIN maybe be followed by "Report heading
SQL Must
be SELECT
List of Columns Selected given in a BEGIN END nest
1st
Field 2nd type(optional) "Column
Heading(optional)
if type is omitted then type is taken to be same as
field
if you omit Column Heading it will take from SAP Dictionary
there should not be any line break by word wrap in text
control
SUM MIN
MAX AVG COUNT COUNT( *) supported
SUM( table~COLUMN )
OPEN SQL & NATIVE SQL syntax must be correct
If you use NATIVE SQL make sure you have :SY-MANDT filter in WHERE Clause
Program
generates c:\tmp\jnc.ab4
jnc.ab4 is the generated ABAP program for diagnostics and possible
reuse
JOINs and
SUBQUERIES are NOT ALLOWED for
Pooled Tables, Clustered Tables & Projection Views
Even AGGREGATE Functions are
NOT ALLOWED! -- these restrictions are inherent in SAP
So this
tool is more useful for TRANSPARENT TABLES only!
For Pool & Cluster preferably use SE16N.
Y4SQL is case insensitive.
Experience full power of Oracle's Native SQL - Typle Compare, CASE, in-line views etc.
Also ROLLUP CUBE GROUPING ....
Tips for SQL
1. Use Native
2. Use unambiguous SQL
3. Do not forget GROUP BY
4. Check spelling, commas, fullstops & brackets
5. Take CARE as you can easily forget & hog resources
ex. be sure of Joins & do not cause Cartesian products
6. Use YTABFLDS to paste definitions
7. Build your "library" of favourite SQLs in a text file
for subsequent reuse/enhancements
8. Do not forget to join on MANDT if needed
9. Check for :SY-MANDT and :SY-LANGU if required
Please realize that you need to be very careful about your joins.
Wrong Joins will result in Cartesian products that will create major performance problems
Use Y4SQL but be very careful about joins & restrict where clause to optimize what you need.
Example 1
Begin
"Compare Header &
Items Not Matching
hdrkey char8
"SID
werks werks_d
fkdat fkdat
"BillDate
kbdnr w_kbdnr
"Cashier
kasnam w_kasnam
"CshrName
kasnr w_kasnr
"POS-number
bonnr w_bonnr
"ReceiptNum
hdrtot w_umsvpo
"Payment
itmsrl
char2 "ItemSrlno
matnr matnr
itmtot w_umsvpo
"ItemAmount
end
select h.hdrkey,
h.werks,
h.fkdat,
h.kbdnr,
h.kasnam,
h.kasnr,
h.bonnr,
h.umsgvo,
i.itmsrl,
i.matnr,
i.umsgvo
from zposhdr h, zpositm i
where h.mandt = i.mandt
and h.hdrkey = i.hdrkey
and h.mandt = :sy-mandt
and h.werks = 'H008'
and h.hdrkey in (
select hdrkey
from zposhdr a
WHERE a.fkdat = '20070507'
AND a.werks =
'H008'
AND abs ( umsgvo -
( select sum( umsgvo )
from zpositm b
where b.hdrkey = a.hdrkey ) ) > 0.50
)
order by werks, fkdat, kbdnr, kasnr, bonnr, itmsrl
Example 2
BEGIN
"500 Production IDOCs in
Error
sndprn edi_sndprn
credat edi_ccrdat
idoctp edi_idoctp
docnum edi_docnum
status edi_status
descrp edi_text60
direct edi_direct
rcvpor edi_rcvpor
rcvprn edi_rcvprn
END
SELECT
i.sndprn,
i.credat,
i.idoctp,
i.docnum,
i.status,
t.descrp,
i.direct,
i.rcvpor,
i.rcvprn
FROM edidc i, teds2 t
WHERE i.mandt = :sy-mandt
AND i.status NOT IN ('53', '73', '03', '16')
AND i.status = t.status
AND t.langua = 'E'
AND ( i.sndprn in ( select werks
from t001w )
OR
i.rcvprn in ( select werks
from t001w )
)
Example 3
BEGIN
"StkVchr Analysis Dense
Ultimate & With Hierarchy!
werks werks_d
matnr
maktx
menge menge_d
dmbtr
mercat char200 "Mercat.
END
SELECT werks,
ilvw.matnr,
maktx,
creditqty - debitqty,
creditval - debitval,
tree.mercat
FROM
(
SELECT mseg.werks,
mseg.matnr,
SUM( CASE WHEN shkzg = 'H'
THEN menge
ELSE 0
END ) AS creditqty,
SUM( CASE WHEN shkzg = 'S'
THEN menge
ELSE 0
END ) AS debitqty,
SUM( CASE WHEN shkzg = 'H'
THEN dmbtr
ELSE 0
END ) AS creditval,
SUM( CASE WHEN shkzg = 'S'
THEN dmbtr
ELSE 0
END ) AS debitval
FROM mkpf
INNER JOIN mseg
ON mkpf.mandt = mseg.mandt
AND mkpf.mblnr = mseg.mblnr
AND mkpf.mjahr = mseg.mjahr
AND mseg.mandt = :sy-mandt
WHERE mseg.werks
IN ( 'D055', 'H003' )
AND mkpf.budat
<= '20070430'
AND mkpf.budat
>= '20070401'
AND
( mseg.bwart = '551'
OR mseg.bwart = '552' )
GROUP BY mseg.werks,
mseg.matnr
) ilvw,
(
SELECT child.class,
SYS_CONNECT_BY_PATH (swor.kschl, '<-') mercat
FROM kssk, swor, klah child, klah parent
WHERE kssk.objek
= swor.clint
AND kssk.mandt = swor.mandt
AND swor.spras = :sy-langu
AND kssk.objek = child.clint
AND kssk.mandt = child.mandt
AND kssk.clint = parent.clint
AND kssk.mandt = parent.mandt
AND kssk.mandt = :sy-mandt
START
with kssk.clint = ( SELECT clint
FROM klah
WHERE class = '100000000'
)
CONNECT BY PRIOR kssk.objek
= kssk.clint
) tree, makt, mara
WHERE ilvw.matnr = makt.matnr
AND makt.spras = :sy-langu
AND makt.mandt = :sy-mandt
AND makt.mandt = mara.mandt
AND makt.matnr = mara.matnr
AND mara.matkl = tree.class
ORDER BY werks, ilvw.matnr, maktx