Re: SQL Statement

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 29 Aug 1998 17:18:12 GMT
Message-ID: <35ee3542.4451120@192.86.155.100>

A copy of this was sent to "Sal DiStefano" <sald_at_interflow.net> (if that email address didn't require changing) On Sat, 29 Aug 1998 08:59:30 -0500, you wrote:

>I am New to Oracle SQL and have a question as to how to convert this MSSQL
>statement to ORACLE.
>
>Any Help Would be appreciated.
>
>
>
>SELECT dbo.customer.pkid,"Name" = CASE WhEN ccompany = "" THEN
>cfirstname + " " + clastname Else ccompany END,
> dbo.customer.ccity,Vehicle = ( dbo.vehicle.cyear + " " +
>dbo.vehicle.cmake + " " + dbo.vehicle.cmodel),
> dbo.customer.PrevSalesperson
>FROM dbo.vehicle INNER JOIN dbo.customer ON dbo.vehicle.customerpk =
>dbo.customer.PkId
>
>WHERE (dbo.customer.salesperson = 20)
>
>ORDER BY dbo.customer.clastname
>

select customer.pkid,

       nvl( ccompany, cfirstname || ' ' || clastname ) name, 
       customer( ccity ), 
       vehicle.cyear || ' ' || vehicle.cmake || ' ' || vehicle.cmodel vehicle, 
       customer.prevSalesperson

from vehicle, customer
where vehicle.customerpk = customer.pkid and customer.salesperson = 20
order by customer.clastname
/

couple of notes for you

double quotes are for quoted identifiers in Oracle *not* character string constants. " " would be an identifier (eg: a column name) whose name was a space. Quoted identifiers are typically used to preserve case or spaces in identifiers, for example create table foo ( "A Column Name" int ); will create a table foo with a column name in mixed case with spaces in it.
an empty string is NULL in Oracle. For example:

SQL> create table foo ( str varchar2(5) ); Table created.

SQL> insert into foo values ( '' );
1 row created.

SQL> select nvl( str, 'I am Null' ) from foo;

NVL(STR,'

I am Null

so ccompany = '' will ALWAYS fail because a NULL is never equal (nor is it ever not equal) to anything -- including NULL. Hence, I turned your CASE into an NVL (when Null VaLue) call. In general CASE will be converted into an Oracle DECODE() call tho.

Oracle doesn't support INNER and OUTER join syntax. An inner join is performed just by stating:

from T1, T2
where ( t1.c1 = t2.c1 and t1.c2 = t2.c2 ... )

To do an outer join from T1 to T2 (so if a row exists in T1 it will appear in the result set) you will:

from T1, T2
where ( t1.c1 = t2.c1(+) and t1.c2 = t2.c2(+) ..... )

just move the (+) to go the other direction -- t1.c1(+) = t2.c1

>
>
>
>
>Thanks in advance
>
>
>Sal
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Aug 29 1998 - 12:18:12 CDT