query select with more than 3 join: grid sql doesn't work

Applications Grid / Reports
#1

Scriptcase 8,

In a grid I have a complex query with 5 join.

When I save the sql page, Scriptcase creates the fields regularly, but when I generate the source-code, the compilators give the error in the log: “fields not defined” select with more than 2 query, deleted 3.

Somebody could help me?

Thanks

#2

It would help to see the query here…
You might want to ad some extra ( ) here and there, just to be on the safe side.

#3

Thank you for your reply rr.

This is the query:

SELECT
list.Id,
list.Photo,
list.City,
list.Country,
concat (list.City, ’ (’, list.Country, ‘)’) as City1,
list.Type,
list.Style,
prestige_stars.star,
list.Prestige_rating,
investment_stars.star,
list.Investment_rating,
list.ROI,
list.Investment,
un_lock.un_lock_ico,
un_lock.un_lock
FROM
list
LEFT OUTER JOIN
prestige_stars
ON
list.Prestige_rating=prestige_stars.rating
LEFT OUTER JOIN
investment_stars
ON
list.Investment_rating=investment_stars.rating
LEFT OUTER JOIN
user_property_code_match
ON
list.Id=user_property_code_match.property_id
INNER JOIN
un_lock
ON
un_lock.un_lock=user_property_code_match.un_locked
WHERE
user_property_code_match.user_id=’[UserLogin]’

This query works, but if I make a single change in script and I generate the source-code, the compilators give the error in the log: “fields not defined” select with more than 2 query, deleted 3.

#4

If the query becomes too complex for the sql parser then you can always consider using a view.

#5

Thanks Albert, but what do you mean for using a view?