IDT & UDT - SQL Difference

[Former Member]

IN BI4.0 - When we build the universe in IDT there is different SQL option for Single source and Multi Source.

Single source - Database specific SQL Syntax

Multi Source - Standard SQL-92 and SAP Business Objects SQL functions

What is the difference between this two?

[Former Member · Accepted solution]

Hi Satheesh,

Say you have a BO Object called TODAY, you can define it anyway as below:

• Database specific SQL syntax - syntax which will work only for a specific database

           SYSDATE - will work only for oracle

           GETDATE() - will work only for mssql

• Standard SQL-92 - syntax which will work in any database

          CURRENT_TIMESTAMP will work in any database (irrespective of oracle or mssql)

• SAP BO SQL Functions - BO will generate syntax based on the database

          curDate() will work for any db

In multisource since there could be 2 different databases involved it generally use sql-92 or SAP BO functions sql, if apart from these any specific function(like lead, lag in oracle or @@servername in mssql) needs to be used, they have to be defined as database specific sql.

[Former Member · Accepted solution]

Great explanation

[Former Member · Accepted solution]

Hi Jawahar - Yes i know those details

Hi Preetha - Thanks for the details and clarification. I hope if you use multi source universe and create universe with custom SQL thare also this SQL-92 standard only will work. am i correct?

[Former Member · Accepted solution]

Hi Satheesh - If you use multisource universe it use sql-92 syntax. If you are trying to create a custom sql (derived tbl/ col) that uses any specific function which is not in SQL-92 they are defined as database specific (shown in screenshots below).