Oracle 8i – Optional Select Value

oracleoracle-8i

I'm writing query to select students with their Program, Country and Teacher. So far, I have the following query:

SELECT H.STUDENT_CODE,
       H.FIRSTNAME,
       H.LASTNAME,
       H.PROGRAM,
       H.TEACHER_CODE,
       H.COUNTRY_CODE,
       AC.COUNTRY_NAME,
       P.PROGRAM_NAME,
       T.TEACHER_NAME
    FROM HTUD H,
         PROGRAM P,
         TEACHER T,
         AP_COUNTRY AC
    WHERE H.FIRSTNAME LIKE '%ark%'
        AND H.PROGRAM = P.PROGRAM
        AND H.TEACHER_CODE = T.TEACHER_CODE
        AND H.COUNTRY_CODE = AC.COUNTRY_CODE
    ORDER BY H.STUDENT_CODE ASC

These two columns H.TEACHER_CODE and H.COUNTRY_CODE have some empty values. If I run the query above, it returns only students that have data in these two columns H.TEACHER_CODE and H.COUNTRY_CODE.

However, I want to select all students wether they have data in these two columns or not. If they have data in H.TEACHER_CODE and H.COUNTRY_CODE column, then return Teacher and Country also, if they don't have, just return students without Teacher or Country.

So, how do I select all students with or without Teacher or Country?

Best Answer

You want to make an outer join. In Oracle 8i one has to use the (+) notation.

SELECT H.STUDENT_CODE,
   H.FIRSTNAME,
   H.LASTNAME,
   H.PROGRAM,
   H.TEACHER_CODE,
   H.COUNTRY_CODE,
   AC.COUNTRY_NAME,
   P.PROGRAM_NAME,
   T.TEACHER_NAME
FROM HTUD H,
     PROGRAM P,
     TEACHER T,
     AP_COUNTRY AC
WHERE H.FIRSTNAME LIKE '%ark%'
    AND H.PROGRAM = P.PROGRAM
    AND H.TEACHER_CODE = T.TEACHER_CODE(+)
    AND H.COUNTRY_CODE = AC.COUNTRY_CODE(+)
ORDER BY H.STUDENT_CODE ASC

Starting with Oracle 10g xou can use the ANSI join syntax,too

SELECT H.STUDENT_CODE,
   H.FIRSTNAME,
   H.LASTNAME,
   H.PROGRAM,
   H.TEACHER_CODE,
   H.COUNTRY_CODE,
   AC.COUNTRY_NAME,
   P.PROGRAM_NAME,
   T.TEACHER_NAME
FROM HTUD H JOIN PROGRAM P ON (H.PROGRAM = P.PROGRAM)
    LEFT OUTER JOIN TEACHER T ON (H.TEACHER_CODE = T.TEACHER_CODE)
    LEFT OUTER JOIN AP_COUNTRY AC ON (H.COUNTRY_CODE = AC.COUNTRY_CODE)
WHERE H.FIRSTNAME LIKE '%ark%'
ORDER BY H.STUDENT_CODE ASC

Related Solutions

How to group results in batches of consecutive rows with the same ‘module’

The question is which of the pdates for a module do you want to use for ordering purposes?

If you are happy to use the largest, you could use:

select module from logging order by max(pdate);

--- EDIT

I think I get it now - you probably want something like this:

select module
from( select module, 
             sum(module_step) over (order by pdate rows unbounded preceding) 
                 as batch_number, 
             pdate
      from( select module, 
                   case lag(module) over (order by pdate) when module then 0 
                        else 1 end as module_step, 
                   pdate from logging) )
group by module, batch_number
order by batch_number;

SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

This uses the same basic technique as jonearles ⁽⁺¹⁾ (since removed) but eliminates the PIVOT and would require only one function that turns the string into a CASE statement.

SELECT substr(
      MIN (
         CASE WHEN Blank=1 THEN '1 Blank' 
              WHEN Error=1 THEN '2 Error' 
              WHEN InProgress=1 THEN '3 InProgress' 
              WHEN Completed=1 THEN '4 Completed' 
         END
         )
   ,3) state_code
FROM m_object mo
JOIN m_form mf ON mo.id = mf.id
WHERE mo.v = :2 AND mf.formlayout_id = :1;

I assume the data looks something like this:

drop table m_object;
create table m_object as 
   (select 10 id, 0 blank, 0 error, 0 inprogress, 1 completed, 99 v from dual);
insert into m_object values (11,1,0,0,0,99);
insert into m_object values (12,0,0,0,1,99);
insert into m_object values (13,0,1,1,0,99);

drop table m_form;
create table m_form as (select 10 id, 20 formlayout_id from dual);
insert into m_form values (11,21);
insert into m_form values (12,22);
insert into m_form values (13,20);

Here is what the function might look like if it were done in PL/SQL. It was just thrown together and is only meant to illustrate building the SQL statement and is not meant to be an example of good coding.

set serveroutput on format wrapped

DECLARE
vPassed Varchar2(500) := 'Blank,Error,InProgress,Completed';

   Function MakeCase(pOriginal In Varchar2) Return Varchar2 Is
      vBuilt     Varchar2(500);
      vWord      Varchar2(100);
      vChar      Char(1);
      vWordCount Number(1) := 1;
   Begin
      For vLoop In 1..Length(pOriginal) Loop
         vChar := substr(pOriginal,vLoop,1);
         If (vChar = ',') Then
            If (vBuilt IS NULL) Then
               vBuilt := 'CASE WHEN ';
            End If;
            vBuilt := vBuilt || vWord || '=1 THEN '''
               || to_char(vWordCount,'FM0') || ' ' || vWord || ''' WHEN ';
            vWord := '';
            vWordCount := vWordCount + 1;            
         Else                  
            vWord := vWord || vChar;            
         End If;
      End Loop;
      vBuilt := vBuilt || vWord || '=1 THEN ''' 
         || to_char(vWordCount,'FM0') || ' ' || vWord || ''' ';
      vBuilt := vBuilt || ' END ';
      Return vBuilt;
   End;

BEGIN
   vPassed := MakeCase(vPassed);
   DBMS_Output.Put_Line(vPassed);

   --Use vPassed here to build SQL statement.
END;
/

Best Answer

Related Solutions

How to group results in batches of consecutive rows with the same ‘module’

SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

Related Question