Re: Oracle NULL vs '' revisited

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 20 Aug 2007 15:24:02 -0700
Message-ID: <1187648640.151088@bubbleator.drizzle.com>

Tony Rogerson wrote:

>> Please look at TREAT (second link) and explain what you intend. I am 
>> confused by what appears to be a misunderstanding of what TREAT is and
>> does.

>
> I quoted the wrong line.
>
> Your page says this "Change declared type of an expression"
>
> In Oracle you say use TREAT.
>
> In SQL Server, to "Change declared type of an expression" we would use
> CAST.
>
> So, please change your statement of "no equivalent" to "CAST"

You are still misreading the page. The entry for TREAT: "Change declared type of an expression" is under the heading String Handling Functions and has nothing to do with what CAST does. TREAT is used specifically to deal with changing the type of expression with the word "type" referring to user defined types.

Under "Conversion Functions" I very clearly list Transact SQL's CONVERT and CAST as being roughly equivalent to Oracle's CAST. But this is using CAST as a conversion function which is different.

Further down the page, under Date-Time you will note that I show the fact that TSQL's CAST can be used in a manner similar to Oracle's TO_DATE.
> There are so many things wrong on your page is laughable,

I'm not laughing and neither have a lot of people who work for Microsoft.

So when you calm down send specific examples and links.

> You say we don't have date arithmetic in one part of your page and then
> in another you say we do - make your mind up;

No I don't. At least not on the page that is on my website. I very clearly indicate that DATEADD is equivalent to +.

And please knock of the "we" stuff. I'm not Oracle and you aren't Bill Gates.

> You say we don't have hierarchical operators - we've implemented the SQL
> Standard CTE's.

And the link is where?

> You say we don't have IN-OUT parameters, when we do - see CREATE PROC in
> bol.

"bol" being what? If you want me to fix the page try actually being helpful.

> You say we don't have materialised views - we do, "indexed views".

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

"With SQL Server 2000, the functionality of SQL Server views was expanded to provide system performance benefits. It is possible to create a unique clustered index on a view"

"In SQL Server 2000 and 2005, a view that has a unique clustered index is referred to as an indexed view."

and you think that is equivalent to this?

CREATE MATERIALIZED VIEW mv_complete
TABLESPACE uwdata
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT s.srvr_id, i.installstatus, COUNT(*) FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id, i.installstatus;

and this is a very simplistic example. Want to discuss refresh groups? Want to discuss query rewrite?

It is becoming increasingly obvious that other than being offended by the appearance of dissing SQL Server you really don't know the product as well as you think you do. And certainly not nearly as well as do most of my students.

> As soon as you correct your document to be factual around trigger usage
> then I'll help you "correct" your document with examples;

Correct what? I see nothing to correct. You made a comment about instead-of triggers irrelevant to what is on the page. And your point about statement triggers is that they are different. Whoopee. COMMIT is different too. Want me to point say that SQL Server has no equivalent to Oracle's COMMIT WRITE WAIT BATCH?

I will make changes were there are things that are factually incorrect. I will not make superfluous changes or changes that alter the nature of why the page was created and its value to my students.

So far you are batting 0 for 2.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Aug 20 2007 - 17:24:02 CDT