JOIN IN SQL

[Prabhu1204]

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.

[Kushal_Chawda]

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

[Prabhu1204]

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

[Kushal_Chawda]

this looks like the issue in DB settings.

https://stackoverflow.com/questions/25350703/ora-01652-unable-to-extend-temp-segment-by-128-in-table...

[Prabhu1204]

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

[Kushal_Chawda]

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

[Prabhu1204]

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 😞

[Kushal_Chawda]

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

[Prabhu1204]

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

[Kushal_Chawda]

Yes. check this issue with your DBA