| Paulo Raffaelli 2005-04-26, 8:23 pm |
| I found out what was causing the problem.
Apparently, the first subquery returning the same parameter twice was
it. Modifying both subqueries so that a parameter does not get returned
in the results fixed it. Strange! I posted it here in case someone else
is struggling with this issue.
- Paulo
David Fishburn wrote:
> "Paulo Raffaelli" <paulor@bearriver.com> wrote in
> news:420a8cd8$1@foru
ms-2-dub of sybase.public.sqlanywhere.ultralite:
>
> PR> Hi,
> PR>
> PR> I am porting an application using Ultralite for the Palm (C++ static
> PR> SQL), and one query which compiles under 8.0.2.4234 does not under
> PR> 9.0.1.1883. Breaking it up into two queries instead of two subqueries
> PR> with a UNION ALL between them will make it compile; I have other
> PR> queries with UNION ALL in them which don't cause problems. Has anyone
> PR> else seen this problem?
>
> When you have a SQL query that complicated, take a bit of time to narrow
> down where the problem may be.
>
> This will be as simple as removing column lists, or subqueries, and
> re-running ULGEN. If ULGEN runs successfully, add some complexity back.
>
> That should help us narrow down where the problem may be:
>
> select ''-''
> , cc.displayname
> , cc.DisplayPhone
> , A.Title
> , (
> case a.IndirectFlag
> when 1 then 0
> else A.PrimaryAffiliation
> end
> ) as PrimaryFlag
> , A.TakeCallFlag
> , A.FormularyCommittee
> , cc.palm_City
> , A.AffiliationId
> , :paramOrgId as OrgOrDeptUUID
> , :paramOrgId as OwningOrgUUID <--------- this line gets removed for fix
> , 0 as parentPrimaryFlag
> from affiliation a
> inner join contact cc
> on (
> a.childcontactid = cc.contactid
> AND cc.contacttypecode = ''P''
> )
> where a.parentcontactid = :paramOrgId
> and (a.IndirectFlag = 0)
> AND (
> (cc. palm_TerrFilterAffec
ts = 0)
> OR (:paramFilterByTerri
tory = 0)
> OR (0 < PATINDEX(:paramTerrN
br, cc.palm_TerritoryList ))
> )
> union all
> select l.lookupvalue
> , pro.displayname
> , pro.DisplayPhone
> , A2.Title
> , A2.PrimaryAffiliation
> , A2.TakeCallFlag
> , A2.FormularyCommittee
> , pro.palm_City
> , A2.AffiliationId
> , a.childcontactid as OrgOrDeptUUID
> , :paramOrgId as OwningOrgUUID <--------- this line gets removed for fix
> , a2.parentprimaryflag as parentPrimaryFlag
> from affiliation a
> inner join contact dept
> on (
> a.childcontactid = dept.contactid
> AND dept.contacttypecode = ''T''
> )
> inner join affiliation a2
> on ( a2.parentcontactid = a.childcontactid)
> inner join contact pro
> on (
> a2.childcontactid = pro.contactid
> AND pro.contacttypecode = ''P''
> )
> inner join lookup l
> on (l.lookupid = dept.contacttypeid)
> where a.parentcontactid = :paramOrgId
> AND (
> (pro. palm_TerrFilterAffec
ts = 0)
> OR (:paramFilterByTerri
tory = 0)
> OR (0 < PATINDEX( :paramTerrNbr, pro.palm_TerritoryList ))
> )
> order by 1, 2
> FOR READ ONLY
>
|