Scalar Subquery Expressions

السلام عليكم

Scalar Subquery Expressions

هو عباره عن subquery  يقوم  بأسترجاع قيمه واحده من عمود فى صف واحد وقيمه scalar subquery expression تأتى من جمله الـ SELECT...

 فى حاله ان scalar subquery يسترجع 0 اذن فـ قيمته تساوى null فى حاله scalar subquery قام بأسترجاع اكثر من صف -returns more than one row- تظهر اوركل خطأ.

يمكنك استخدام Scalar Subquery Expressions  فى الحالات التاليه:

The condition and expression part of DECODE and CASE

All clauses of SELECT except GROUP BY

The SET clause and WHERE clause of an UPDATE statement

ولا يمكنك استخدامه فى الحالات التاليه :

As default values for columns and hash expressions for clusters

In the RETURNING clause of DML statements

As the basis of a function-based index

In GROUP BY clauses, CHECK constraints, WHEN conditions

In CONNECT BY clauses

In statements that are unrelated to queries, such as CREATE PROFILE

بعض الأمثله على Scalar Subquery Expressions :

مثال 1 يوضح طريقه استخدامه مع Case expressions 

SELECT EMPLOYEE_ID
     , LAST_NAME
     ,(CASE
       WHEN DEPARTMENT_ID =
                          (SELECT DEPARTMENT_ID
                           FROM   DEPARTMENTS
                           WHERE  LOCATION_ID = 1800)
                           THEN 'Canada' ELSE 'USA' END) LOCATION
FROM EMPLOYEES;


EMPLOYEE_ID LAST_NAME                 LOCATION
----------- ------------------------- --------
        198 OConnell                  USA      
        199 Grant                     USA      
        200 Whalen                    USA      
        201 Hartstein                 Canada   
        202 Fay                       Canada   
        203 Mavris                    USA      
        204 Baer                      USA      
        205 Higgins                   USA      
        206 Gietz                     USA  

مثال 2 يوضح طريقه استخدامه مع Order by

SELECT EMPLOYEE_ID
     , LAST_NAME
FROM   EMPLOYEES E
ORDER BY (SELECT DEPARTMENT_NAME
          FROM   DEPARTMENTS D
          WHERE  E.DEPARTMENT_ID = D.DEPARTMENT_ID);

EMPLOYEE_ID LAST_NAME               
----------- -------------------------
        205 Higgins                   
        206 Gietz                     
        200 Whalen                    
        100 King                      
        101 Kochhar                   
        102 De Haan                   
        109 Faviet                    
        108 Greenberg                 
        112 Urman                     
        111 Sciarra  

-->