Hello, We have a customer fee table. I am trying to make a table that calculates based on the type of charge. If Memo = ‘C’ then charge should be Commission * Rate ELSE it should be 0. I thought I can do the following SELECT Customer, Commission, Fee, IIF(Memo=‘C’,(Commission Rate),0) as CommissionCharge, IIF(Memo2=‘C’,(Fee Rate),0) as CommissionFee FROM Customer_Charge; But this doesn’t work. Do you have any suggestions? Thanks,

@Ralph ,

Given that the last couple of lines of the post to which you are responding are:
ERROR at line 11:
ORA-00907: missing right parenthesis
I suspect that somebody was using MS Access syntax in an ORACLE database, rather than SQL Server.

What we have here is a hijacker inserting a post in the middle of a thread on a different topic, in the wrong forum. Sigh.

Michael S. Meyers-Jouan

This looks a whole lot like someone is trying to use MS Access SQL in a
SQL Server environment. Use CASE instead of IIF.

CASE WHEN B.DOC_TYPE = ‘RRI’
THEN B.NET_AMT_PAYABLE
ELSE 0
END Cash

Respectfully,

Ralph D. Wilson II
Sr. Programmer Analyst

9311 San Pedro Ave., Suite 600
San Antonio, TX 78216
(800) 527-0066 x7368 - Toll Free
(210) 321-7368 - Direct
(210) 387-7744 - Mobile

Visit our website at www.swbc.com

From: “USER_2129694 via sql-server-l”

( Case When Memo=‘C’ then Commission Rate Else 0) as
CommissionCharge,
( Case When Memo2=‘C’ Then (Fee Rate) Else 0 ) as
CommissionFee
FROM Customer_Charge;

SELECT
Customer,
Commission,
( Case Memo When ‘C’ then Commission Rate Else 0) as
CommissionCharge,
( Case Memo2 When ‘C’ Then (Fee Rate) Else 0 ) as CommissionFee

FROM Customer_Charge;

Use the CASE function to achieve your goal.
See BOL for usage.
Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: sqlusa.com - sqlusa Resources and Information.

I understand, but the original question was how to insert a NULL into a date
field. What you show is how to retrieve a date that already contains
1900-01-01, following a ‘’ value insert.

From: Mahabub Sikder via sql-server-l
[ mailto:[email protected] ]
Sent: Sunday, April 27, 2008 3:38 AM
To: css nico
Subject: RE: [sql-server-l] IF condition in SELECT statement

Posted by
Mahabub Sikder (SQL
SERVER BI/DW/Database Developer)
on 04/27/2008 10:22:00 AM

HI nico
You can try this as following
select case FieldName when ‘1900-01-01’ then ‘’ else FieldName from [Table
Name]
Thanks
Mahabub
css nico via sql-server-l

HI nico
You can try this as following
select case FieldName when ‘1900-01-01’ then ‘’ else FieldName from [Table Name]
Thanks
Mahabub
css nico via sql-server-l

while inserting with the blank value

So, instead of inserting a blank value, insert a NULL.

Respectfully,
Ralph D. Wilson II
Senior Programmer Analyst
Information Technology Department
9311 San Pedro Suite 600
San Antonio TX 78216
(800) 527-0066 x7368
(210) 321-7368 (direct)
[email protected]
“Make everything as simple as possible, but not simpler.” Albert
Einstein.

“Hitesh_chaudhary via sql-server-l”

set it to NULL (no quotes). Make sure that it allows NULLs.

From: Hitesh_chaudhary via sql-server-l
[ mailto:[email protected] ]
Sent: Thursday, April 24, 2008 4:19 AM
To: css nico
Subject: RE:[sql-server-l] IF condition in SELECT statement

Posted by
Hitesh Kumar
on 04/24/2008 07:20:00 AM

Hi All
I have a issuein my sql table ,i have a field with datatype datetime ,while
inserting with the blank value its taking 1900-01-01 with the time ,i dont
want this value to be inserted or if not possible while retriving can i
check if value is 1900-01-01 it should return null.

I am a beginner here! Now, I have created my tables in Access 2000 and
exported them to SQL server. They are mainly for data entry. I use VB6.0 to
buid forms for data entry and the data is taken to the tables. Now that I
have exported table sto SQL server, how do I use my vb to connect to the SQL
database. About 7 people will be doing data entry, i.e. maybe about 4 will
be inputing data to the same table, how is this going to happen, perfomance,
sharing?

Please help me. I am based in the Kingdom of Lesotho (Southern Africa)

Goodman Makojoa

Try this

SELECT Customer, Commission, Fee, memo=case when memo=‘C’ then
(Commission Rate) else 0 end,
memo2=case when memo2=‘C’ then (fee Rate) else 0 end
FROM Customer_Charge

Regards
Manoj
www.stonematrix.com

Try this…

(I m assuming that Memo2 is a field present in the table…n not a typing
mistake…)

SELECT
Customer
, Commission
, Fee
, IIF( t1.MEMO=‘C’, (Commission Rate), 0 ) AS [Commission Charge]
, IIF(t2.MEMO2=‘C’, (Fee Rate), 0 ) AS [Commission Fees]
Customer_Charge AS [c1]
, (SELECT memo AS [MEMO] FROM Customer_Charge) AS [t1]
, (SELECT memo2 [MEMO2] FROM Customer_Charge) AS [t2]
WHERE

Kalman, for what he is doing, CASE statements are fine. It is not sloppy
programming or poor design. I believe in proper relational design but I
also believe in the KISS principle.=20