Join syntax for SQL Query Editor in ST04

[former_member636171]

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

[FredericGirod]

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)

[former_member636171]

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'

[Sandra_Rossi]

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.

[michael_piesche]

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

[Sandra_Rossi]

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

[former_member766248]

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.