ORA-00900 error

Toad for Oracle & DevOps Toolkit
1

*I know this error has been inquired on before, but tbh I really am not experienced enough to understand all the high-level discussions... and it often seems to end up not being what I'm trying to sort out anyway.

So that being said, I feel I need to begin from scratch...

I have an existing query and I want to add one more selection from a 3rd table in order to add more efficient use of the data. There is already an inner join in the script, which works perfectly, until I try to include this additional inner join.

I have gotten errors 904 and 918 errors as well, depending on what I have tried in order to rectify the issue. So, at this point, I think I just need to understand what may be wrong with my addition to the scripting.

Can you assist with this issue please?

Thank you.

2

Sounds like more of a SQL problem than a Toad problem, but we can try to help.

It would help if you showed us your query. If you can simplify it but still get the error, that would be good too.

Also, Toad should tell you the position (line/column) of the error in your query. So include that info if you post the SQL.

3

As Mr. Dorlon said, these ToadWorld forums are for questions about Quest product usage. However, we do try to help where we can, and yes, posting your query would be a huge help.

That said, I have gotten ORA-00918 errors in the past, and it's usually because the same column name exists in more than one of the tables referenced in the query. Just guessing here, but if that's the case, you should be able to avoid this error by either aliasing the column/s, or qualifying the tables/columns by their schema.

Hope this helps, and if not, then please post the query.

4

Thank you for responding, John and Gary... I only posted here because the error window always takes me to this forum to read other people's issues. Sometimes I find something that resolves my issue, but more often it is just not what I'm looking for. So, I appreciate you taking the time.

This is the original query:

SELECT PDBC_PFX,
BSDL_TYPE,
BSDE_DESC,
BSDL_NTWK_IND,
BSDL_EFF_DT,
BSDL_COPAY_AMT,
BSDL_DEDE_AMT,
BSDL_COIN_PCT,
BSDL_LTLT_AMT,
BSDL_LT_TYPE,
BSDL_LT_PERIOD,
BSDL_COV_IND,
BSDL_STOPLOSS_AMT,
BSDL_STOPLOSS_TYPE,
BSDL_USER_LABEL1,
BSDL_USER_DATA1,
BSDE_REC_TYPE
FROM FACETSDB.CMC_BSDL_DETAILS
INNER JOIN FACETSDB.CMC_BSDE_DESC ON (BSDL_TYPE = BSDE_TYPE)
WHERE (BSDE_REC_TYPE = 'BSDL')
AND cmc_bsdl_details.pdbc_pfx BETWEEN 'XXXXXX' AND 'XXXXXX'
AND cmc_bsdl_details.bsdl_type BETWEEN '1001' AND '1017'
OR (BSDE_REC_TYPE = 'BSDL')
AND cmc_bsdl_details.pdbc_pfx BETWEEN 'XXXXXX' AND 'XXXXXX'
OR cmc_bsdl_details.pdbc_pfx IN ('XXXXXX','XXXXXX','XXXXXX','XXXXXX','XXXXXX')
--AND cmc_bsdl_details.bsdl_type BETWEEN '1001' AND '1017'
--AND cmc_bsdl_details.bsdl_type IN '%1007%' and '%1017%'
ORDER BY cmc_bsdl_details.pdbc_pfx ASC, cmc_bsdl_details.bsdl_type ASC

Below is the new Inner Join formatting that I cannot get to function.

FROM FACETSDB.CMC_BSDE_DESC
INNER JOIN FACETSDB.CMC_BSBS_SUM ON (BSDE_DESC = BSBS_DESC)
INNER JOIN FACETSDB.CMC_BSDL_DETAILS
ON (CMC_BSBS_SUM.PDBC_PFX = CMC_BSDL_DETAILS.PDBC_PFX)

The error for this format is 00918.

As for the error, the window shows Line 2, Column 8. If you need more detail, you may have to tell me what to do to get it.

5

I think you just need add table aliases, and use them when referencing columns in your SQL, so Oracle knows which table a column comes from (if it happens to exist in both tables)

so instead of:
FROM FACETSDB.CMC_BSDL_DETAILS
INNER JOIN FACETSDB.CMC_BSDE_DESC

do
FROM FACETSDB.CMC_BSDL_DETAILS dt
INNER JOIN FACETSDB.CMC_BSDE_DESC dsc

Now, when you reference fields, you should include the table from whence they came by using the alias

SELECT dt.PDBC_PFX,
dsc.BSDL_TYPE,
dt.BSDE_DESC,
dt.BSDL_NTWK_IND,

The aliases can be (almost) whatever you want.

6

Hey John, I jumped in too quickly this morning and made a couple of mistakes in the example... Let me correct that... 1) Adding BSBS_DESC as second selection, and 2) First inner join should have been TYPE rather than DESC for both. But still gives me the same result...

SELECT PDBC_PFX,
BSBS_DESC,
BSDL_TYPE,
BSDE_DESC,
BSDL_NTWK_IND,
BSDL_EFF_DT,
BSDL_COPAY_AMT,
BSDL_DEDE_AMT,
BSDL_COIN_PCT,
BSDL_LTLT_AMT,
BSDL_LT_TYPE,
BSDL_LT_PERIOD,
BSDL_COV_IND,
BSDL_STOPLOSS_AMT,
BSDL_STOPLOSS_TYPE,
BSDL_USER_LABEL1,
BSDL_USER_DATA1,
BSDE_REC_TYPE
FROM FACETSDB.CMC_BSDE_DESC
INNER JOIN FACETSDB.CMC_BSBS_SUM ON (BSDE_TYPE = BSBS_TYPE)
INNER JOIN FACETSDB.CMC_BSDL_DETAILS
ON (CMC_BSBS_SUM.PDBC_PFX = CMC_BSDL_DETAILS.PDBC_PFX)
WHERE (BSDE_REC_TYPE = 'BSDL')
AND cmc_bsdl_details.pdbc_pfx BETWEEN 'XXXXXX' AND 'XXXXXX'
AND cmc_bsdl_details.bsdl_type BETWEEN '1001' AND '1017'
OR (BSDE_REC_TYPE = 'BSDL')
AND cmc_bsdl_details.pdbc_pfx BETWEEN 'JSJ6' AND 'JSJE'
OR cmc_bsdl_details.pdbc_pfx IN ('XXXXXX','XXXXXX','XXXXXX','XXXXXX','XXXXXX')
--AND cmc_bsdl_details.bsdl_type BETWEEN '1001' AND '1017'
--AND cmc_bsdl_details.bsdl_type IN '%1007%' and '%1017%'
ORDER BY cmc_bsdl_details.pdbc_pfx ASC, cmc_bsdl_details.bsdl_type ASC

How do I give something an alias?

7

You just make up the alias in your SQL like with my example above.

8

Starting to get what you're saying...

So I need to include the given alias for each selection then. IE: If that selection comes from BSDL_DETAILS, and I gave it an alias of dt, then add dt. to each selection from that table.

Does that sound like what you are saying?

9

Yes.

  1. make up distinct alias for each table in your query. Add it after the table name in the FROM clause. (ie: from my_awesome_table mat inner join my_other_table mot)
  2. any time you reference a column, include it's alias so Oracle knows which table it comes from.
    (ie select mat.column1, mot.column2, mat.column3...)
  3. also include aliases in the where clause:
    (ie: where mat.column1 = mot.column1)
10

That is fantastic...

One more before I dive in...

In doing this, does it need to be all or nothing, or can it just be for a given table? IE: Only do an alias for the BSDL_DETAILS table, but not for the other two?

11

At a minimum, you need to alias enough so that there is no ambiguity in your SQL.

Also, just because you add an alias to a table name, does not mean that you need to use the alias every single time you reference a column in that table. If the column is only in 1 of the tables, oracle can figure it out and won't give an error.

But It's really a good idea to use aliases everywhere. It will make your SQL a lot more readable to others (and yourself next month!)

12

Okay, that seemed a little ambiguous... JK LOL

I tried it with just the one that is giving me a headache and it gave a 904 error, so I'll just go through and do the alias for all columns to ensure it's consistent.

If it works, I will leave you be, but if it doesn't work should I continue the discussion or find another resource?

13

Clearly, the aliases did something to fix the ambiguity issue, but unfortunately now it's giving 904 and highlighting "CMC_BSDL_DETAILS.PDBC_PFX" as the invalid identifier (L22, C40). *ORA-00904: "CMC_BSDL_DETAILS"."PDBC_PFX": invalid identifier

PDBC_PFX is the same from the BSBS_SUM and BSDL_DETAILS tables and is one of the inner joins, so is this having an issue now with the aliases, since "PDBC_PFX" is in both tables? IE: Do I need to do something different with that first column?

14

I think, now that you have created the alias for that table, you need to use it there.

So instead of
ON (CMC_BSBS_SUM.PDBC_PFX = CMC_BSDL_DETAILS.PDBC_PFX)

do

ON (CMC_BSBS_SUM.PDBC_PFX = det.PDBC_PFX)

(and similarly with CMC_BSBS_SUM if you made an alias for that)

15

That makes sense, so I tried it. But it's still giving the 904, and now is showing that "BT"."CMC_BSDL_DETAILS"."PDBC_PFX": invalid identifier.

I feel we are onto something, but I just cannot figure it out.

16

What is BT? Is that your table alias for the CMC_BSDL_DETAILS table?

If so, you should do

ON (CMC_BSBS_SUM.PDBC_PFX = BT.PDBC_PFX)

(Notice I removed the table name and put the alias there instead)

17

That actually solved it. Although, I ended up doing it to all the inner joins, one by one until it finally stopped complaining.

Thank you!

1 Like