topic Re: Join syntax for SQL Query Editor in ST04 in Application Development and Automation Discussions

Join syntax for SQL Query Editor in ST04

former_member636171 — Mon, 05 Oct 2020 14:42:07 GMT

I was going to test a join in the SQL Query Editor in ST04 but I keep getting a syntax error.
Does anyone here know how the syntax goes in the editor?

This gives an error: "invalid column name: SKAT".

select * 
    from skat
    join skb1
        on skat-saknr = skb1-saknr
    where ktopl = 'CADT'
        and bukrs = '3660'

This gives an error: "sql syntax error: incorrect syntax near "~":".

select * 
    from skat
    join skb1
        on skat~saknr = skb1~saknr
    where ktopl = 'CADT'
        and bukrs = '3660'

This gives an error: "sql syntax error: incorrect syntax near "~":".

select * 
    from skat as t2
    join skb1 as t1
        on t1~saknr = t2~saknr
    where ktopl = 'CADT'
        and bukrs = '3660'

This gives an error: "Invalid column name: T1".

select * 
    from skat as t2
    join skb1 as t1
        on t1-saknr = t2-saknr
    where ktopl = 'CADT'
        and bukrs = '3660'

Thanks and regards,
Birgir Snorrason

Re: Join syntax for SQL Query Editor in ST04

FredericGirod — Mon, 05 Oct 2020 15:09:22 GMT

Maybe you have to specify the table in the WHERE clause

select * 
    from skat
    join skb1
        on skat-saknr = skb1-saknr
    where skb1~ktopl = 'CADT'
        and skb1~bukrs = '3660'

(I don't know if ~ is the good syntax)

Re: Join syntax for SQL Query Editor in ST04

former_member636171 — Mon, 05 Oct 2020 15:16:47 GMT

I get the same errors when I add the table in the where clause.

"sql syntax error: incorrect syntax near "~"" for:

select * 
    from skat
    join skb1
        on skat-saknr = skb1-saknr
    where skb1~ktopl = 'CADT'
        and skb1~bukrs = '3660'

and "invalid column name: SKAT" for:

select * 
    from skat
    join skb1
        on skat-saknr = skb1-saknr
    where skb1-ktopl = 'CADT'
        and skb1-bukrs = '3660'

Re: Join syntax for SQL Query Editor in ST04

Sandra_Rossi — Mon, 05 Oct 2020 17:58:16 GMT

SQL is to be in "native" form (your database system), not Open SQL.

The separator is probably a DOT, not a tilde, not a dash.

SELECT * 
    FROM SKAT
    JOIN SKB1
        ON SKAT.SAKNR = SKB1.SAKNR
    WHERE SKB1.KTOPL = 'CADT'
      AND SKB1.BUKRS = '3660'

I also prefer to use upper case, especially with HANA.

Re: Join syntax for SQL Query Editor in ST04

michael_piesche — Mon, 05 Oct 2020 21:20:34 GMT

In ST04 the available functions and options are based on the database connection. And as Sandra already mentioned, the "SQL Editor" for Hana or the "SQL Command Editor" for Oracle (or the SQL Editors for other DBs) need to be used in the corresponding native DB SQL language. OpenSQL cannot be applied. If unsure about the DBs native SQL syntax, use google, although the basic functions and many more advanced functions will be identical.

Alias is defined for tables without AS
Alias can be defined for select attributes with and without AS
Table is separated from attributes by dot '.'
Left and Inner-Join possible
When using Left-Join, restrictions need to be for join restrictions
Native functions like e.g. substr(string,start,length) or cast(value as type(options)) available
Multi-Line comments: /* .... */
In-Line comments: --
Following examples work on Oracle and Hana

/*              
Alias for tables without AS  
Alias for select attributes with and without AS possible 
Tables separated from attributes by dot '.'
Native functions like e.g. substr(string,start,length) or cast(value as type(options)) available
Multi-Line Comments start with slash and star and end with star and slash
*/
-- In-Line Comments start with dash dash
/* Inner-Join Example */
SELECT * 
  FROM skat at       
  INNER JOIN skb1 b1 
    ON b1.saknr = at.saknr
 WHERE at.ktopl = 'CADT'
   AND b1.bukrs = '3660'

/* Left-Join Example */              
SELECT at.*, b1.bukrs AS "Company Code"          
  FROM skat at       
  LEFT JOIN skb1 b1
    ON at.saknr = b1.saknr
   AND b1.bukrs = 'CADT'
 WHERE at.ktopl = 'YCOA'

PS: SKB1 does not have an attribute KTOPL

Re: Join syntax for SQL Query Editor in ST04

Sandra_Rossi — Tue, 06 Oct 2020 06:39:42 GMT

Link to HANA SQL reference: SAP HANA SQL and System Views Reference

Re: Join syntax for SQL Query Editor in ST04

former_member766248 — Tue, 10 Aug 2021 15:39:39 GMT

Maybe it's too late, but you can do it this way:

select * 
from skat a join skb1 b
on a.mandt = b.mandt and a.saknr = b.saknr
where b.ktopl ='CADT' and b.bukrs ='3660'

Note that if you are using ST04, you should specify field MANDT also in WHERE clause because the query is cross-client.