Re: SQL-Server SQL to Oracle SQL

In article <34E3142F.93D70F2A_at_bjss.co.uk>, Andy Hall <andy.hall_at_bjss.co.uk> wrote:

>Are there any multi-lingual SQL guys who can tell me how to code the
>following SQL-Server code in Oracle.
>
>The view contains a column ("available") which reduces the values of
>the base table status column ("obsolete", "shipping", "beta", etc.) to a
>boolean in the view.
 

>CREATE VIEW vwTmp (name, id, available) AS
>Select name,
> id,
> case when status = 'shipping' then true
> else false
> end
> From product
>GO

I'm not multi-lingual (unless you count pig latin), but I'll give it a shot...

create VIEW VXTMP as
  select NAME, ID, decode (STATUS,'shipping',TRUE,FALSE) AVAILABLE

     from PRODUCT
/

Note that the view name and column names are case insensitive unless you double quote them. Strong opinion - don't go there!

Also, there is no Oracle7 boolean datatype within the database itself. If you want AVAILABLE to be numeric 0/1 then TRUE becomes 1 and FALSE becomes 0 in the above SQL. If AVAILABLE is char T/F or varchar2, then TRUE becomes 'T' and FALSE becomes 'F'. Strong opinion - don't use the long datatype for booleans! (I had to...) Received on Sat Feb 14 1998 - 00:00:00 CST