-3

I have a query which is working fine..

SELECT 
    WSDEFN.WORKSPACE_DISPLAY_NM,
    LAYDEFN.LAYOUT_DISPLAY_NM,
    WSLMAP.POSITION,
    LAYDEFN.LAYOUT,
    LAYDEFN.PROPORTION,
    LAYDEFN.LAYOUT_ID,
    WSDEFN.WORKSPACE_ID,
    LAYDEFN.BUNDLE_KEY 
FROM 
    WORKSPACE_DEFINITION WSDEFN,
    WORKSPACE_LAYOUT_MAP WSLMAP,
    LAYOUT_DEFINITION LAYDEFN
WHERE 
    WORKSPACE_ID = WSLMAP.WORKSPACE_ID
    AND WSLMAP.LAYOUT_ID = LAYDEFN.LAYOUT_ID 
    AND WSDEFN.OD_USER_NO = '-1' 
    AND WSDEFN.OD_GCIF = '-1'
ORDER BY 
    wsdefn.workspace_id, WSLMAP.POSITION

But I want to fetch the WSDEFN.WORKSPACE_ID as WORKSPACE_ID.

I tried this

WSDEFN.WORKSPACE_ID AS WORKSPACE_ID,

but I get an error

'column ambiguously defined'

Could you give me a solution?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
NageswaraRao .K
  • 53
  • 1
  • 1
  • 8
  • 1
    I don't follow, why do you want to give it an alias which is the same as its name? Also, you should define your `joins` explicitly using `join....on..` syntax – HoneyBadger Dec 31 '15 at 08:57
  • http://stackoverflow.com/questions/6233086/ora-00918-column-ambiguously-defined-in-select – maztt Dec 31 '15 at 09:00
  • the following will be added to the query by local framework UPPER(TRIM(WORKSPACE_ID)) LIKE '%' || UPPER(TRIM(?)) || '%' . i need it for that purpose. – NageswaraRao .K Dec 31 '15 at 09:01
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged – marc_s Dec 31 '15 at 10:15
  • hi marc_s, you are right. I 'm comfort new sql style only. But I am working with an enhancement application. – NageswaraRao .K Jan 07 '16 at 11:05

1 Answers1

3

It seems to be missing the table alias in the where clause.

Did you try this? 

            SELECT 
                WSDEFN.WORKSPACE_DISPLAY_NM,
                LAYDEFN.LAYOUT_DISPLAY_NM,
                WSLMAP.POSITION,
                LAYDEFN.LAYOUT,
                LAYDEFN.PROPORTION,
                LAYDEFN.LAYOUT_ID,
                WSDEFN.WORKSPACE_ID AS WORKSPACE_ID,  
                LAYDEFN.BUNDLE_KEY 
            FROM 
                WORKSPACE_DEFINITION WSDEFN,
                WORKSPACE_LAYOUT_MAP WSLMAP,
                LAYOUT_DEFINITION LAYDEFN
           WHERE 
               WSDEFN.WORKSPACE_ID = WSLMAP.WORKSPACE_ID
           AND 
                WSLMAP.LAYOUT_ID      = LAYDEFN.LAYOUT_ID 
           AND 
                WSDEFN.OD_USER_NO='-1' AND WSDEFN.OD_GCIF='-1'
           ORDER BY 
                wsdefn.workspace_id,WSLMAP.POSITION
antoniodvr
  • 1,259
  • 1
  • 14
  • 15
  • thanks working fine... :-) may know the reason. i did the same but not worked. may know why? – NageswaraRao .K Dec 31 '15 at 09:05
  • It's already written in my answer, the field `WORKSPACE_ID` in the `WHERE` clause exist probably with the same name in some other table involved in the `JOIN`; so you have to specify which of them you are referring in order to don't have ambiguity. – antoniodvr Dec 31 '15 at 09:10