Error Oracle :query block has incorrect number of result columns

I have this query :

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

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