SQL JOIN query

[girish_talele]

Dear Experts,

In SAP, as shown below, we have following four tables, in which Key field is available in all tables.

And I want to fetch the records of third & fourth table if Creation / Change Date is yesterday’s date in first two tables.

Table	Key Field	Creation Date	Change Date
AFRU	AUFPL	ERSDA
CAUFV	AUFPL	ERDAT	AEDAT
AFVV	AUFPL
AFVC	AUFPL

Existing script is as given below, but it is taking long time to load.

Is there any better way to join tables?

AFRU:
Load AUFPL;
SQL SELECT DISTINCT AUFPL FROM AFRU WHERE ERSDA  >= '$(vDate)';

Join (AFRU)
CAUFV:
Load AUFPL;
SQL SELECT AUFPL FROM CAUFV WHERE (CAUFV~ERDAT >= '$(vDate)' OR CAUFV~AEDAT >= '$(vDate)');

LEFT JOIN (AFRU)
AFVV:
Load
AUFPL,
APLZL,
STEUS;
SQL SELECT AFVV~AUFPL AFVV~APLZL AFVC~AUFPL AFVC~APLZL AFVC~STEUS
FROM AFVV INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL;

Regards,

Girish

[maxgro]

but it is taking long time to load

which statement?

maybe an option could be to join in SQL the 3 tables using (in SQL) the vDate filter

[girish_talele]

Dear Maxgro,

Thanks for looking into,

Script is taking long time in Third & Fourth table, there are almost 1.5 Million records loaded (WHERE condition also not applicable),

whereas only 13 K records are matching with key field laded in first & second table. It takes almost 8-10 Min to load,

If I use separate left join i.e. (as given below)

1. 1. Left Join AFRU & AFVV,
2. 2. Left Join CAUFV & AFVV

Then the data is getting fetched in 1 Min only. But I want load the same in one go.

Regards,

Girish.

AFRU:
Load
AUFPL,
APLZL,
STEUS;
SQL SELECT DISTINCT CAUFV~AUFPL
AFVV~AUFPL AFVV~APLZL
AFVC~AUFPL AFVC~APLZL AFVC~STEUS

FROM CAUFV

LEFT JOIN AFVV ON  CAUFV~AUFPL = AFVV~AUFPL
INNER JOIN AFVC ON  AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL

WHERE (CAUFV~ERDAT >= '$(vLastLoadDate)' OR CAUFV~AEDAT >= '$(vLastLoadDate)');

//--------------------------------------------------------------------------------------------------------------

  AFRU:

Load

AUFPL,

APLZL,

STEUS;

SQL SELECT DISTINCT AFRU~AUFPL
AFVV~AUFPL AFVV~APLZL
AFVC~AUFPL AFVC~APLZL AFVC~STEUS

FROM AFRU
LEFT JOIN AFVV ON  AFRU~AUFPL = AFVV~AUFPL
INNER JOIN AFVC ON  AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL

WHERE ERSDA  >= '$(vLastLoadDate)';

[Anil_Babu_Samineni]

Might this problem you are facing is performance. The reason was you are getting data From different source. I would request you to store this Two table s into qvd and then try to reload. Check whether you will get reduced the elapsed time or not and let me know what's the file size and qvw file size

[maxgro]

I hope to understand.

If it takes long time in SQL (below), it could be the database and/or the network

One way to reduce (maybe the only one) is to reduce the record Qlik get from the database

SQL SELECT AFVV~AUFPL AFVV~APLZL AFVC~AUFPL AFVC~APLZL AFVC~STEUS
FROM AFVV INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL;

I think something like (check the syntax)

SQL SELECT AFVV~AUFPL AFVV~APLZL AFVC~AUFPL AFVC~APLZL AFVC~STEUS
FROM AFVV INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL

WHERE AFVV~AUFPL in

// this should be the same as your first 2 statements (see below, full ouer join ***)

(

     SELECT DISTINCT AUFPL FROM AFRU WHERE ERSDA  >= '$(vDate)'

     UNION

     SELECT AUFPL FROM CAUFV WHERE (CAUFV~ERDAT >= '$(vDate)' OR CAUFV~AEDAT >= '$(vDate)')

)

***

AFRU:
Load AUFPL;
SQL SELECT DISTINCT AUFPL FROM AFRU WHERE ERSDA  >= '$(vDate)';

Join (AFRU)
CAUFV:
Load AUFPL;
SQL SELECT AUFPL FROM CAUFV WHERE (CAUFV~ERDAT >= '$(vDate)' OR CAUFV~AEDAT >= '$(vDate)');

[girish_talele]

Dear Massimo,

Thanks again,

I got below error message while running the script.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 Incorrect expression "UNION" in logical condition.)

SQL SELECT AFVV~AUFPL AFVV~APLZL AFVC~AUFPL AFVC~APLZL AFVC~STEUS

FROM AFVV INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL

WHERE AFVV~AUFPL in

(

     SELECT DISTINCT AUFPL FROM AFRU WHERE ERSDA  >= '20161008'

     UNION

     SELECT AUFPL FROM CAUFV WHERE (CAUFV~ERDAT >= '20161008' OR CAUFV~AEDAT >= '20161008')

  )

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Modified your script as given below.

AFRU:
Load
AUFPL,
APLZL,
STEUS;
SQL SELECT AFVV~AUFPL AFVV~APLZL AFVC~AUFPL AFVC~APLZL AFVC~STEUS
FROM AFVV INNER JOIN AFVC ON AFVV~AUFPL = AFVC~AUFPL AND AFVV~APLZL = AFVC~APLZL
WHERE AFVV~AUFPL in
(SELECT DISTINCT AUFPL FROM AFRU WHERE ERSDA  >= '$(vDate)')

//     UNION
   
OR
AFVV~AUFPL in
(SELECT AUFPL FROM CAUFV WHERE (CAUFV~ERDAT >= '$(vDate)' OR CAUFV~AEDAT >= '$(vDate)'));

*** Instead of Union, I used OR statement & it worked fine.

Although with OR statement I am getting the results, had one query - What was the use of UNION in your script? How to remove the error.

Thanks & regards,

Girish.