0

While execute below Query , its executed successfully.

But when execute only (SELECT CREDITDOCUMENTID FROM TBLTPAYMENT ) it is give error like "00904. 00000 - "%s: invalid identifier"".

Doesn't know how its work in oracle 12c database.

SELECT *
FROM TBLTCREDITCREDITDOCUMENTREL
WHERE CREDITDOCUMENTID IN
  (SELECT CREDITDOCUMENTID
  FROM TBLTCREDITDOCUMENT
  WHERE CREDITDOCUMENTID IN
    (SELECT CREDITDOCUMENTID FROM TBLTPAYMENT    )
  );
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
SamDPatel
  • 1
  • 1

1 Answers1

2

An educated guess (and I'm betting on it) would be that your column CREDITDOCUMENTID is not a column from the TBLTPAYMENT table, but from some other table that is in scope, e.g. in TBLTCREDITDOCUMENT. This is easy to check. Try running this query:

SELECT *
FROM TBLTCREDITCREDITDOCUMENTREL
WHERE CREDITDOCUMENTID IN
  (SELECT CREDITDOCUMENTID
  FROM TBLTCREDITDOCUMENT
  WHERE CREDITDOCUMENTID IN
    (SELECT TBLTPAYMENT.CREDITDOCUMENTID FROM TBLTPAYMENT)
  );

In case of which you qualify the TBLTPAYMENT.CREDITDOCUMENTID column. You'll get the same error as when you run the inner-most SELECT by itself.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • [Btw, I have discovered an interesting bug in Oracle 12.2.0.1 while researching this problem](https://twitter.com/lukaseder/status/912626775561207808) – Lukas Eder Sep 26 '17 at 10:41
  • Thanks Lukas for your response, you r right CREDITDOCUMENTID is not a column of TBLTPAYMENT table, But while execute your provided query it also execute successfully. – SamDPatel Oct 06 '17 at 08:45
  • @SamDPatel: Are you *sure*? That's a rather strong contradiction right there. – Lukas Eder Oct 06 '17 at 13:09