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

 

 

 

 

Related Articles