Understanding Oracle Aliasing - Why Isn't an Alias Not Recognized in a Query Unless Wrapped in a Second Query

Understanding Oracle aliasing - why isn't an alias not recognized in a query unless wrapped in a second query?

The simple answer is that the AS clause defines what the column will be called in the result, which is a different scope than the query itself.

In your example, using the HAVING clause would work best:

SELECT COUNT(*) AS "CNT",
       imei
FROM   devices
GROUP  BY imei
HAVING COUNT(*) > 1

oracle pl/sql how to call back this alias created for a parameter?

Just put the original query part into there where instead of the alias name... i.e.,

WHERE TUTORIAL_CLASSES_MASTER.STUDY_INTAKE || ' ' || STUDENT_ATTENDENTS.CLASS_CODE = :CLASS_DODE

Why is that in Oracle when we fetch values from an alias we cant able to specify column as aliases?

Because as soon as you start enclosing names in double quotes like "Mgrid" they becone case-sensitive. So don't. Do this:

select Manager, Mgrid 
from 
    (select distinct b.lname||' '||b.fname as Manager,b.EMPID as Mgrid 
    from EMPLOYEE1) a, 
    (select lname,fname,empid 
    from EMPLOYEE1) b 
where b.EMPID=a.MGRID)

or if you really like case-sensitive names you must do this:

select "Manager", "Mgrid" 
from 
    (select distinct b.lname||' '||b.fname as "Manager",b.EMPID as "Mgrid" 
    from EMPLOYEE1) a, 
    (select lname,fname,empid 
    from EMPLOYEE1) b 
where b.EMPID=a."Mgrid")

ORA-00904 Invalid identifier” for an identifier in a group by clause

You can't refer to a column alias in the same level of SQL, except in the order by clause.

From the documentation (emphasis added):

You can use a column alias, c_alias, to label the immediately preceding expression in the select list so that the column is displayed with a new heading. The alias effectively renames the select list item for the duration of the query. The alias can be used in the ORDER BY clause, but not other clauses in the query.

When you refer to QTYLIV in the GROUP BY cluase the select list hasn't been evaluated yet and the alias doesn't exist. This is just how the query is parsed and executed.

When you have complicated expressions in the select list it's often simplest to wrap that in an outer select and do the grouping afterwards:

SELECT *
FROM (
  SELECT p.name AS design,
    p.M_PRODUCT_CATEGORY_ID,
    il.PRICEACTUAL   AS price,
    bp.C_BPARTNER_ID AS idpartner,
    CASE
  ...
    (SELECT qtyinvoiced
    FROM C_InvoiceLine il
    WHERE bp.ISCUSTOMER ='Y'
    AND bp.C_BPARTNER_ID= 18888
    )               AS qtyliv,
  ...
    i.DATEINVOICED AS dat
  FROM C_InvoiceLine il
  INNER JOIN M_PRODUCT p
  ...
  ON (oi.c_location_id=loc2.c_location_id)
    --WHERE i.DateInvoiced BETWEEN $P{Date1} AND $P{Date2}
    --AND
    --i.DocStatus in ('CO','CL')
    --AND i.IsSoTrx = 'Y'
    --AND   p.isstocked='Y'
)
GROUP BY name ,
  M_PRODUCT_CATEGORY_ID,
  QTYINVOICED,
  PRICEACTUAL,
...
  qtyliv,
  qtydepot
ORDER BY name ,
  dateinvoiced ;

Notice that you don't use the original table aliases in the GROUP BY or ORDER BY clauses in the outer select, as those are no longer in scope.

Why does Oracle SQL update query return invalid identifier on existing column?

You can't reference a table alias in a subquery of a subquery; the alias doesn't apply (or doesn't exist, or isn't in scope, depending on how you prefer to look at it). With the code you posted the error is reported against line 11 character 24, which is:

        (SELECT type FROM
            (SELECT d.type as type FROM table_2 d
             WHERE d.s_id = t1.s_id AND d.sv_id = t1.sv_id
                                                  ^^^^^^^^

If you change the t1.s_id reference on the same line to something invalid then the error doesn't change and is still reported as ORA-00904: "T1"."SV_ID": invalid identifier. But if you change the same reference on line 5 instead to something like

        ((SELECT COUNT(dateCheck.id) FROM table_2 dateCheck
          WHERE dateCheck.s_id = t1.s_idXXX AND dateCheck.sv_id = t1.sv_id) = 0)

... then the error changes to ORA-00904: "T1"."S_IDXXX": invalid identifier. This is down to how the statement is being parsed. In your original version the subquery in the WHEN clause is value, and you only break it by changing that identifier. The subquery in the ELSE is also OK. But the nested subquery in the ELSE has the problem, and changing the t1.s_id in that doesn't make any difference because the parser reads that part of the statement backwards (I don't know, or can't remember, why!).

So you have to eliminate the nested subquery. A general approach would be to make the whole CASE an inline view which you can then join using s_id and sv_id, but that's complicated as there may be no matching table_2 record (based on your count); and there may be no s_id value to match against as that isn't being checked in table_3.

It isn't clear if there will always be a table_3 record even then there is a table_2 record, or if they're mutually exclusive. If I've understood what the CASE is doing then I think you can use an outer join between those two tables and compare the combined data with the row you're updating, but because of that ambiguity it needs to be a full outer join. I think.

Here's a stab at using that construct with a MERGE instead of an update.

MERGE INTO table_1 t1
USING (
  SELECT t2.s_id,
    coalesce(t2.sv_id, t3.id) as sv_id,
    coalesce(t2.type, t3.type) as type,
    row_number() over (partition by t2.s_id, t2.sv_id
      order by t2.creationtimestamp) as rn
  FROM table_2 t2
  FULL OUTER JOIN table_3 t3
  ON t3.id = t2.sv_id
) tmp
ON ((tmp.s_id is null OR tmp.s_id = t1.s_id) AND tmp.sv_id = t1.sv_id AND tmp.rn = 1)
WHEN MATCHED THEN UPDATE SET t1.type = tmp.type;

If there will always be a table_3 record then you could use that as the driver and have a left outer join to table_2 instead, but hard to tell which might be appropriate. So this is really just a starting point.

SQL Fiddle with some made-up data that I believe would have hit both branches of your case. More realistic data would expose the flaws and misunderstandings, and suggest a more robust (or just more correct) approach...

---

Related Topics