Easy (I think) SQL Statement question

Easy (I think) SQL Statement question

Post by Daniel Prat » Wed, 09 Jun 1999 04:00:00



    In a query (it could also be a stored proc if necessary) I need to have
a column output as the result of an expression or 0 if the expression is
less than 0. Okay, well that was really confusing but if I was going to
write the query using Access it might look like this:

        SELECT IIf(([Field1] - [Field2]) < 0, 0 ,([Field1] - [Field2])) AS
Expr1
        FROM Table1;

    Can someone tell me how this can be done with T-SQL? Thanks for any
help.

-Dan

 
 
 

Easy (I think) SQL Statement question

Post by Frances Corazz » Wed, 09 Jun 1999 04:00:00


Daniel,

If I understand you correctly, you want to do this:

SELECT CASE WHEN field1-field2 < 0 THEN 0 ELSE field1-field2 END FROM table1

Frances

 
 
 

Easy (I think) SQL Statement question

Post by James Olse » Thu, 10 Jun 1999 04:00:00


You should write it like this

select case Field1 - Field2 when < 0 then 0 else Field1 - Field2 end
    from Table1

Quote:>     In a query (it could also be a stored proc if necessary) I need to
have
> a column output as the result of an expression or 0 if the expression is
> less than 0. Okay, well that was really confusing but if I was going to
> write the query using Access it might look like this:

>         SELECT IIf(([Field1] - [Field2]) < 0, 0 ,([Field1] - [Field2])) AS
> Expr1
>         FROM Table1;

>     Can someone tell me how this can be done with T-SQL? Thanks for any
> help.

> -Dan

 
 
 

1. Easy SQL*Plus question (I think)

Is there a simple way to set up the following SQL statement so I'm not
prompted 3 times for the &chaintab substitution variable? I've read the
Oracle online documentation on substitution variables and I bet someone
in this newgroup probably can answer my question a lot more clearly.

CREATE TABLE TMPP_&chaintab
   AS SELECT * FROM &chaintab
 WHERE ROWID IN
    (SELECT head_rowid
  FROM chained_rows
     WHERE table_name = '&chaintab');

The backdrop of my question: I am creating a script that will be used on
multiple tables with chained rows, in which I will copy these rows to a
temp table, delete them from the source table and then re-insert into
source table. So ideally I'd like to input the &chaintab variable or
something similar once and then have it used throughout the script in
various SQL and/or PL/SQL statements. Any suggestions for automating
this process also are appreciated!

Thanks,

Anne

2. Why "server does not exist" error?

3. Newbie - Easy (I think) SQL Syntax Question, 3 table join

4. smint versus int data type

5. Compound INSERT statement - easy (I think)

6. Problems connecting to Oracle

7. Small and easy question (I think) :)

8. Backup log with no_truncate appears to truncate transaction log file.

9. Think this is an easy query question

10. Stored procedure in select (easy question i think)

11. Easy ADO Question (I Think)

12. Easy question..I think

13. easy question...i think