Welcome Guest, Not a member yet? Register   Sign In
OCI8 table alias
#1

Hi.
Can anyone with Oracle knowledge audit the BaseBuilder::buildSubquery() method? And possibly add tests for the case.

I'm having doubts about the use of the "AS" keyword to specify an alias.
For most DBMSs, using "AS" is fine, but according to the link, Oracle doesn't use "AS" for tables.

PHP Code:
    protected function buildSubquery($builderbool $wrapped falsestring $alias ''): string
    
{
        if ($builder instanceof Closure) {
            $builder($builder $this->db->newQuery());
        }

        if ($builder === $this) {
            throw new DatabaseException('The subquery cannot be the same object as the main query object.');
        }

        $subquery strtr($builder->getCompiledSelect(), "\n"' ');

        if ($wrapped) {
            $subquery '(' $subquery ')';
            $alias    trim($alias);

            if ($alias !== '') {
                $subquery .= ' AS ' . ($this->db->protectIdentifiers $this->db->escapeIdentifiers($alias) : $alias);
            }
        }

        return $subquery;
    
Reply
#2

(This post was last modified: 05-15-2022, 01:10 PM by JustJohnQ.)

(05-15-2022, 06:14 AM)iRedds Wrote: Hi.
Can anyone with Oracle knowledge audit the BaseBuilder::buildSubquery() method? And possibly add tests for the case.

I'm having doubts about the use of the "AS" keyword to specify an alias.
For most DBMSs, using "AS" is fine, but according to the link, Oracle doesn't use "AS" for tables.

PHP Code:
    protected function buildSubquery($builderbool $wrapped falsestring $alias ''): string
    
{
        if ($builder instanceof Closure) {
            $builder($builder $this->db->newQuery());
        }

        if ($builder === $this) {
            throw new DatabaseException('The subquery cannot be the same object as the main query object.');
        }

        $subquery strtr($builder->getCompiledSelect(), "\n"' ');

        if ($wrapped) {
            $subquery '(' $subquery ')';
            $alias    trim($alias);

            if ($alias !== '') {
                $subquery .= ' AS ' . ($this->db->protectIdentifiers $this->db->escapeIdentifiers($alias) : $alias);
            }
        }

        return $subquery;
    

You can discard the 'AS' to specify an alias
This gives an error (ORA-00933: SQL command not properly ended):
Code:
select myUsers.Name from users as myUsers

This works just fine:

Code:
select myUsers.Name from users myUsers
One more note, once an alias is specified, it looks like you have to use the alias in the join:
Code:
SELECT PRN_SHIFTS.STARTED, machines.LOCAL_ID FROM PRN_SHIFTS
LEFT JOIN PRN_MACHINES machines ON PRN_SHIFTS.MACHINE = machines.ID
WHERE SHIFT_NUMBER = 1
Works fine
And this gives an error (ORA-00904: "PRN_MACHINES"."ID": invalid identifier):
Code:
SELECT PRN_SHIFTS.STARTED, machines.LOCAL_ID FROM PRN_SHIFTS
LEFT JOIN PRN_MACHINES machines ON PRN_SHIFTS.MACHINE = PRN_MACHINES.ID
WHERE SHIFT_NUMBER = 1
Reply




Theme © iAndrew 2016 - Forum software by © MyBB