SAPGUI SQLCONSOLE Utility Hosted on github

This Blog post is to share a ABAP Utility Tcode YOSQL to test complex OpenSQLs from inside SAPGUI.

Most often you would use Eclipse ADT SQLCONSOLE but once in a while this will be very useful for 2 main reasons:
1. Excel download as it takes output using SALV grid
2. WITH  subqueries or CTE Common Table Expressions are fully supported

Another lateral objective is to popularize ABAPGIT a open source package I much admire. Still “open source” for on-premise but official SAP version for Cloud.

How to install my YOSQL:

This is published in github and needs you to install minimal ABAPGIT

Head over to abapGit documentation – Installation

Read only and Install only the standalone monolith version

Now visit my repository https://github.com/ojnc/yes4sql

Click on green Code button and download the zip.
yes4sql-master.zip in Downloads

Use tcode SE38 and execute ZABAPGIT_STANDALONE
Import Zip (my zip) into staging Area
Thereafter Pull the zip; this does the differential updates

How to runl my YOSQL utility:

Execute  tcode YOSQL and give pass word ojnc
Note YOSQL checks you can only do SELECT and WITH
DELETE INSERT UPDATE not allowed

For WITH  subqueries or CTEs; read
ABAP News for Release 7.51 – Common Table Expressions (CTE) in Open SQL | SAP Blogs
by Horst Keller

WITH 
  +connections AS ( 
    SELECT spfli~carrid, carrname, connid, cityfrom, cityto 
           FROM spfli 
           INNER JOIN scarr 
             ON scarr~carrid = spfli~carrid 
           WHERE spfli~carrid = 'LH' ), 
  +sum_seats AS ( 
    SELECT carrid, connid, SUM( seatsocc ) AS sum_seats 
           FROM sflight 
           WHERE sflight~carrid = 'LH' 
           GROUP BY carrid, connid ), 
  +result( name, connection, departure, arrival, occupied ) AS ( 
    SELECT carrname, c~connid, cityfrom, cityto, sum_seats 
           FROM +connections AS c 
             INNER JOIN +sum_seats AS s 
               ON c~carrid = s~carrid AND 
                  c~connid = s~connid ) 
  SELECT * 
         FROM +result 
         ORDER BY name, connection

Trick SQL for singleton count(*)

    WITH +a AS (
      SELECT
        t~formname,
        t~caption,
        d~ddtext
      FROM
        stxfadmt AS t
        INNER JOIN stxfadm AS f ON (
          t~formname = f~formname
          AND t~langu = 'E'
        )
        INNER JOIN DD07T AS d ON (
          f~formtype = d~domvalue_l
          AND d~domname = 'TDSFTYPE'
          AND d~ddlanguage = 'E'
        )
    ), 
    +result AS (
    SELECT
      COUNT(*) as kount FROM +a )
    SELECT *
      FROM +result

  WITH +a AS (
    SELECT
     COUNT(*) AS kount
    FROM
      stxfadmt )
    SELECT kount
      FROM +a

PATH EXPRESSION also works example

SELECT connid      as FlightConnectionNo,
       airpfrom    as AirportFrom,
\_sflight-fldate   as FlightDate,
\_sflight-price    as AirFare,
\_sflight-currency as LocalCurrency
 FROM DEMO_CDS_ASSOC_SPFLI
 WHERE airpfrom = 'SIN

_______________________________________________________

Second ABAP Utility is Tcode Y4SQL
This does both native and openSql
I used this a lot since 2005  in era when Opensql was not as powerful

Y4SQL needs BEGIN END to define ALV Grid as native SQL can have complex CASE and expressions besides Native SQL cannot use @DATA
People who use AMDP may find this useful

OpenSQL example

BEGIN "OPEN"
    NAME         S_CARRNAME
    CONNECTION   S_CONN_ID
    CITYFROM     S_FROM_CIT
    CITYTO       S_TO_CITY
    SEATSOCC     S_SEATSOCC "SUM SEATS
END
WITH 
  +connections AS ( 
    SELECT spfli~carrid, carrname, connid, cityfrom, cityto 
           FROM spfli 
           INNER JOIN scarr 
             ON scarr~carrid = spfli~carrid 
           WHERE spfli~carrid = 'LH' ), 
  +sum_seats AS ( 
    SELECT carrid, connid, SUM( seatsocc ) AS sum_seats 
           FROM sflight 
           WHERE sflight~carrid = 'LH' 
           GROUP BY carrid, connid ), 
  +result( name, connection, departure, arrival, occupied ) AS ( 
    SELECT carrname, c~connid, cityfrom, cityto, sum_seats 
           FROM +connections AS c 
             INNER JOIN +sum_seats AS s 
               ON c~carrid = s~carrid AND 
                  c~connid = s~connid ) 
  SELECT * 
         FROM +result 
         ORDER BY name, connection

Native Example

Native SQL with Oracle Database

begin "Tables Rows Usage Oracle Native
    tblnam      char30     "TableName
    numrows     int4       "NumRows
    avgrowlen   int4       "AvgRowLen
    lastdt      dats       "LastDate
    contflag    char1      "Type
end
             select a.table_name,
                    a.num_rows,
                    a.avg_row_len,
                    TO_CHAR(a.last_analyzed,'YYYYMMDD') As last_analyzed,
                    NVL(s.contflag,'A') as contflag
               from USER_tables a, DD02L s
              where a.table_name = s.tabname (+)
                and s.contflag not in ('S','W')
                and a.num_rows is not NULL
                and a.num_rows > 0
              order by num_rows desc, table_name

To summarize Y4SQL of 2005 was in era where OPEN SQL was “weak”
Now YOSQL is way to go with OPEN SQL very powerful

Y4SQL will be useful if you use AMDP or ECC6 with “old” ABAP version

_______________________________________________________

There have been so many SQL utilities but this new kid on the block will please!

I really look forward to your feedback and more SAP BLOGs writers sharing code via ABAPGIT.

ABAPGIT supports most of the SAP Technical objects like CDS SHLP
Full list in abapGit documentation – Supported Object Types