topic Re: JOIN IN SQL in QlikView

JOIN IN SQL

Prabhu1204 — Wed, 25 Nov 2020 16:16:04 GMT

Hello ,

I am very new to Qlikview Scriptinng. I am having an issue with my script.

Data:

SQL select

Account.Account key,
Account.Account NBR,
Account.vsc key,
Accoun.Date key

Department. Department NBR,
Department.Department Date key,

Sale.sale DT Key,
Sale.sale ID,
sum(sale) as Total

vendor Account .Account key,
Vendor Account. name,
Vendor Account. CD
vendor Account.vsc key

FROM

Account,
Department,
sale,
Vendor

where Account.Datekey = Department.date key

And Sale.sale DT key = Department.deaptment date key

And account. account key = vendor account. account key,

and Account. vsc key = vendor account.vsc key

and // Date condition

group by

all the fields my the above tables.

I believe the table Account and vendor has inner join by linking the field account key(Bold lines). so, wen there is no data in vendor the account data also getting excluded.

I want the Account data even when there is no vendor data. so I need to perform left join.

Could anyone help me on this.

Re: JOIN IN SQL

Kushal_Chawda — Sat, 18 Jan 2020 13:03:30 GMT

may be this

Data: SQL select Account.Account key, Account.Account NBR, Account.vsc key, Accoun.Date key Department. Department NBR, Department.Department Date key, Sale.sale DT Key, Sale.sale ID, sum(sale) as Total vendor Account .Account key, Vendor Account. name, Vendor Account. CD vendor Account.vsc key FROM Account inner join Department on Account.Datekey = Department.date key inner join Sales on Sale.sale DT key = Department.deaptment date key left join Vendor on account. account key = vendor account. account key left join Vendor on Account. vsc key = vendor account.vsc key

Re: JOIN IN SQL

Prabhu1204 — Mon, 20 Jan 2020 06:14:20 GMT

Thanks,,

I tried and the report is getting hung.. Wen I run the above same query in sql the error was "

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
01652. 00000 - "unable to extend temp segment by %s in tablespace %s"

May be this is the reason the Qlikview is getting hung

Re: JOIN IN SQL

Kushal_Chawda — Mon, 20 Jan 2020 09:47:10 GMT

this looks like the issue in DB settings.

https://stackoverflow.com/questions/25350703/ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-system-how-to-ext

Re: JOIN IN SQL

Prabhu1204 — Mon, 20 Jan 2020 10:28:30 GMT

is it so ??? Thank you.. Will check that

Re: JOIN IN SQL

Kushal_Chawda — Mon, 20 Jan 2020 10:38:10 GMT

Also check that all table name and key columns are joined on proper key field.

Re: JOIN IN SQL

Prabhu1204 — Mon, 20 Jan 2020 11:02:49 GMT

Yes I checked, But when i try to execute the script part by part. All inner joins are working fine.. But on implementing left join, i am getting the error msg 😞

Re: JOIN IN SQL

Kushal_Chawda — Mon, 20 Jan 2020 11:10:07 GMT

or try to change the order

FROM Account left join Vendor on account. account key = vendor account. account key left join Vendor on Account. vsc key = vendor account.vsc key inner join Department on Account.Datekey = Department.date key inner join Sales on Sale.sale DT key = Department.deaptment date key

Re: JOIN IN SQL

Prabhu1204 — Mon, 20 Jan 2020 11:28:56 GMT

Tried that too.. As you said it seems the issue is with DB only. because i excluded all other tables and ran only left join part table.. the same issue occurs..

Not able to make even a simple left join condition

Re: JOIN IN SQL

Kushal_Chawda — Mon, 20 Jan 2020 11:31:11 GMT

Yes. check this issue with your DBA

Re: JOIN IN SQL

Prabhu1204 — Mon, 20 Jan 2020 14:45:43 GMT

I tried the code in SQL developer and its fine.. Can we use sub query for joining.. ?

i tried and my syntax fails.

Re: JOIN IN SQL

Kushal_Chawda — Mon, 20 Jan 2020 14:48:39 GMT

We can use subqueries but try to run query in Qlikview with SQL statement

SQL Select * from join conditions....