logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
sridharkannancel  
#1 Posted : Friday, December 8, 2023 5:26:13 AM(UTC)
sridharkannancel

Rank: Newbie

Groups: Registered
Joined: 2/4/2021(UTC)
Posts: 2
India
Location: Mumbai

I have a "Leads" table which contains 250 records and is having relationship to the following tables
1. ProductID column in Leads table -> ID column of Products table
2. ApplicantTypeID column in Leads table -> ID column of ApplicantType table
3. OwnerID column in Leads table -> ID column of Users table
4. AssignedToID column in Leads table -> ID column of Users table (Note that I have created 2 tables in server manager)

I have created joins between the tables in such a way that LEADS is always the LEFT table and the other tables are RIGHT. This means that even if the lead does not have a PRODUCTID, that lead should still be visible in the report.

However Seal Report has come up with the following query because of which 210 leads are displayed out of 250 records. Is there anyway I can force Seal to have the query starting with leads table and every other join succeeding this will be a LEFT JOIN


SELECT DISTINCT
Lead.LeadNumber AS C0,
ApplicantType.ApplicantTypeDisplayName AS C1,
Product.ProductDisplayName AS C2,
Owners.Name AS C3
FROM
((
select * from Dimension_Users
) Owners LEFT OUTER JOIN
((
select * from dimension_applicanttype
) ApplicantType RIGHT OUTER JOIN
((
select * from leads
) Lead LEFT OUTER JOIN (
select * from dimension_products
) Product
ON Lead.ProductID = Product.ID)
ON Lead.ApplicantTypeID = ApplicantType.ID)
ON Lead.OwnerID = Owners.ID)
ORDER BY Lead.LeadNumber ASC,ApplicantType.ApplicantTypeDisplayName ASC,Product.ProductDisplayName ASC,Owners.Name ASC

Edited by user Friday, December 8, 2023 6:29:48 AM(UTC)  | Reason: Not specified

epf  
#2 Posted : Friday, December 8, 2023 7:42:24 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
you can only play with:
join definition: type-> left,right, isbidirectional

then in the model, you can also re-write the join you want by using the property: 'From Clause'

otherwise, your case shows a typical star problem where it is difficult to start with the central table (as the engine does not know what is the table to start with).
we might add options to control this in the future version,
can you try to reproduce the problem with Northwind so we can study the case ?
sridharkannancel  
#3 Posted : Friday, December 8, 2023 7:52:42 AM(UTC)
sridharkannancel

Rank: Newbie

Groups: Registered
Joined: 2/4/2021(UTC)
Posts: 2
India
Location: Mumbai

Sure. Will try to reproduce the case with Northwinds
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.