I have this query :
x
SELECT storeidint as Test_AUCH, storename as NAME
FROM (
SELECT ds.STORESID, ds.STORENAMES, u.ALL_STORES, alus.STORE_ID, ds.STOREIDINT , TO_NUMBER(COALESCE(STORE_ID,'0'))
FROM Test.Test u
LEFT JOIN test12 ds ON 1=1 AND ds.activ = 1
LEFT JOIN test 123 alus ON u.USER_ID = alus.USER_ID AND ds.STOREIDINT = alus.STORE_ID
WHERE U.USER_ID = :USER_ID AND ds.DATEOPEN IS NOT NULL AND ds.DATECLOSE >= TRUNC(SYSDATE) AND ds.STOREIDINT not like '131'
union all
select to_number(test_auch) as test_auch, NAME
from test.test2
where active = 1
ORDER BY ds.STOREIDINT ASC
)
I know in union need to have the same columns Idk where is the problem In oracle i have this error:
ORA-01789: query block has incorrect number of result columns
01789. 00000 - "query block has incorrect number of result columns"
*Cause:
*Action:
Error at Line: 3 Column: 9
Advertisement
Answer
Your union is between this query:
- “SELECT ds.STOREID, ds.STORENAME, u.ALL_STORES, alus.STORE_ID, ds.STOREIDINT , TO_NUMBER(COALESCE(STORE_ID,’0′))”
and this query:
- select to_number(AUCHAN_CODE) as AUCHAN_CODE , NAME
I believe you have a parenthesis out of place. Also, you can only sort by columns that are actually included in the union set. Try this:
SELECT storeidint as AUCHAN_CODE , storename as NAME
FROM (
SELECT ds.STOREID, ds.STORENAME, u.ALL_STORES, alus.STORE_ID, ds.STOREIDINT , TO_NUMBER(COALESCE(STORE_ID,'0'))
FROM ARRS.ARRS_USERS u
LEFT JOIN DWH_STORES ds ON 1=1 AND ds.activ = 1
LEFT JOIN ARRS_LNK_USERS_STORES alus ON u.USER_ID = alus.USER_ID AND ds.STOREIDINT = alus.STORE_ID
WHERE U.USER_ID = :USER_ID AND ds.DATEOPEN IS NOT NULL AND ds.DATECLOSE >= TRUNC(SYSDATE) AND ds.STOREIDINT not like '131'
)
union all
select to_number(AUCHAN_CODE) as AUCHAN_CODE , NAME
from ARRS_FR.NOM_SITES
where active = 1
ORDER BY AUCHAN_CODE ASC