Problem in advance query with Oracle

Problem in advance query with Oracle

  
Hi guys,

The problem I'm going to describe, was already solved but I think it would be useful for you to know.

The following query use to work in SQL server but it failed in Oracle:

SELECT {LK_CONTRACT_NATURE_ML}.[Code], {LK_CONTRACT_NATURE_ML}.[Description], {LK_CONTRACT_NATURE}.[IsActive]
FROM {LK_CONTRACT_NATURE_ML}, {LK_CONTRACT_NATURE}
WHERE {LK_CONTRACT_NATURE_ML}.[Code] = {LK_CONTRACT_NATURE}.[Code]
AND {LK_CONTRACT_NATURE_ML}.[LocaleId] = @LocaleId
AND {LK_CONTRACT_NATURE}.[IsActive] = 1
UNION ALL
SELECT {LK_CONTRACT_NATURE_ML}.[Code], {LK_CONTRACT_NATURE_ML}.[Description], {LK_CONTRACT_NATURE}.[IsActive]
FROM {LK_CONTRACT_NATURE_ML}, {LK_CONTRACT_NATURE}
WHERE {LK_CONTRACT_NATURE_ML}.[Code] = {LK_CONTRACT_NATURE}.[Code]
AND {LK_CONTRACT_NATURE_ML}.[LocaleId] = @LocaleId
AND {LK_CONTRACT_NATURE}.[IsActive] = 0
AND {LK_CONTRACT_NATURE_ML}.[Code] = @ContractNatureCode
ORDER BY {LK_CONTRACT_NATURE}.[IsActive] DESC, {LK_CONTRACT_NATURE_ML}.[Description]

The problem was in the ORDER BY line that I had to replace by:

ORDER BY 3 DESC, 2

And now it's working without problems.
Regards
Pedro Alcântara
hello,

I have the same problem but I can't use this solution because if I change one entity by adding one attributed it stop working.

any solution?
Hi Diogo

Have you tried using aliases?

SELECT {ENTITY}.[Attribute1] as Att1, ...
UNION ALL
SELECT {ENTITY}.[Attribute1] as Att1, ...
GROUP BY Att1
Hello

alias has the same problem with SELECT Table.*. so I have to write all attributes in each query and if I add or remove any attribute I have to change it in all queries...
Alias work but in attribute, not in table...
What do you mean? The suggestion I had made was having aliases for attribute names, not for table names. Can you post a sample of the method you use to solve the problem on your side?
SELECT NULL , NULL , NULL , NULL , NULL , ENStaff.* , NULL as bool

FROM ({Staff} ENStaff Left JOIN {OrganizationRole} ON ({OrganizationRole}.[STAFFID] = ENStaff.[ID]))
WHERE ((ENStaff.[FIRSTLASTNAME] LIKE @Search)) AND (ENStaff.[ISDELETE] = 0) AND
ENStaff.[ID] NOT IN (SELECT ENStaff.[ID]
FROM ({Staff} ENStaff Inner JOIN {OrganizationRole} ON ({OrganizationRole}.[STAFFID] = ENStaff.[ID]))
WHERE ( ({OrganizationRole}.[ORGANIZATIONUNITID]= @OrganizationUnit_ID) and ({OrganizationRole}.[ORGANIZATIONUNITID] is not null) ) AND
((ENStaff.[FIRSTLASTNAME] LIKE @Search)))


Order By [ENSTAFF].[FIRSTLASTNAME]

this works fine, but with an UNION stops working...

SELECT NULL , NULL , NULL , NULL , NULL , ENStaff.* , NULL as bool

FROM ({Staff} ENStaff Left JOIN {OrganizationRole} ON ({OrganizationRole}.[STAFFID] = ENStaff.[ID]))
WHERE ((ENStaff.[FIRSTLASTNAME] LIKE @Search)) AND (ENStaff.[ISDELETE] = 0) AND
ENStaff.[ID] NOT IN (SELECT ENStaff.[ID]
FROM ({Staff} ENStaff Inner JOIN {OrganizationRole} ON ({OrganizationRole}.[STAFFID] = ENStaff.[ID]))
WHERE ( ({OrganizationRole}.[ORGANIZATIONUNITID]= @OrganizationUnit_ID) and ({OrganizationRole}.[ORGANIZATIONUNITID] is not null) ) AND
((ENStaff.[FIRSTLASTNAME] LIKE @Search)))

UNION


SELECT {OrganizationRole}.* , ENStaff.* ,'True' as bool
FROM ({Staff} ENStaff Inner JOIN {OrganizationRole} ON ({OrganizationRole}.[STAFFID] = ENStaff.[ID]))
WHERE ( ({OrganizationRole}.[ORGANIZATIONUNITID]= @OrganizationUnit_ID) and ({OrganizationRole}.[ORGANIZATIONUNITID] is not null) ) AND
((ENStaff.[FIRSTLASTNAME] LIKE @Search))


Order By [ENSTAFF].[FIRSTLASTNAME]

this, with union stops working... I can't understand! I can't find any solution that works in SQL an in ORACLE...

any suggestion?