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
