Solved

Translating IIF into SQL Server / Oracle

Posted on 2007-03-26
29
1,195 Views
Last Modified: 2008-01-09
In Access I can write this SQL:

SELECT a.AllegationID, IIf(a.EntryDate<#1/1/1970#,#1/1/2000#,a.EntryDate)
FROM dbo_tbl_Allegation AS a

How can I replicate the IIF statement in both SQL Server and Oracle?
0
Comment
Question by:looper8
  • 9
  • 7
  • 4
  • +5
29 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 18793745
SELECT a.AllegationID, CASE a.EntryDate WHEN < '1/1/1970' THEN '1/1/2000' ELSE a.EntryDate END
FROM dbo_tbl_Allegation a
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 18793790
not exactly,
should be
--for Oracle
SELECT a.AllegationID,
CASE WHEN a.EntryDate < '1/1/1970' THEN '1/1/2000' ELSE To_Char(a.EntryDate,'dd/mm/yyyy')  END
FROM dbo_tbl_Allegation a

-- for MS SQL
SELECT a.AllegationID,
CASE WHEN a.EntryDate < '1/1/1970' THEN '1/1/2000' ELSE cast(a.EntryDate AS VARCHAR) END
FROM dbo_tbl_Allegation a
0
 
LVL 1

Author Comment

by:looper8
ID: 18793796
That gives: incorrect syntax near <
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Assisted Solution

by:drydenhogg
drydenhogg earned 100 total points
ID: 18793801
SELECT a.AllegationID,
YourColumnName = CASE
WHEN  a.EntryDate < CONVERT(DateTime,'1/1/1970') THEN Convert(DateTime,'1/1/2000')
ELSE a.EntryDate
END
FROM dbo_tbl_Allegation AS a

Thats the SQL bit.
0
 
LVL 1

Author Comment

by:looper8
ID: 18793862
My comment referred to jimhorn's solution btw.

OMC2000 ... that seems closer, but perhaps I should give you the exact code I'm trying to get working at the moment.  I'm in SQL Server DTS importing some Oracle data and wanting to change some of it as it's imported.  Sorry, should have said that before!  I assume I should be using T-SAL at this point?  The query I'm creating now looks like this:

select "LOCATION_HISTORY"."PERSON_ID",
"LOCATION_HISTORY"."LOCATION_CODE",
CASE WHEN "LOCATION_HISTORY"."START_DATE" < '01/01/1970' THEN '01/01/2000' ELSE "LOCATION_HISTORY"."START_DATE",
"LOCATION_HISTORY"."END_DATE"
from "LOCATION_HISTORY"
0
 
LVL 1

Author Comment

by:looper8
ID: 18793898
^%$£ I'm too tired for this ...

and it gives the result: Unexpected Error Occurred, the provder returned an error result without an error message.
0
 
LVL 1

Author Comment

by:looper8
ID: 18793980
Thanks all.

Off home for a kip ... keep the ideas coming and I'll get back to you in the morning!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18794542
>>I'm in SQL Server DTS importing some Oracle data and wanting to change some of it as it's imported.<<
Than you may want to post in a more appropriate Zone such as:
http://www.experts-exchange.com/Database/Oracle
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18794558
This is how you would do it in T-SQL:
select      LOCATION_HISTORY.PERSON_ID,
      LOCATION_HISTORY.LOCATION_CODE,
      CASE
            WHEN LOCATION_HISTORY.START_DATE < '1970-01-01' THEN '2000-01-01'
            ELSE LOCATION_HISTORY.START_DATE
      END,
      LOCATION_HISTORY.END_DATE
from      LOCATION_HISTORY
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 18794980
just an IMPORTANT note: NEVER (read: NEVER EVER) use implicit data type conversion. especially, when working with dates.


>SELECT a.AllegationID, IIf(a.EntryDate<#1/1/1970#,#1/1/2000#,a.EntryDate)
>FROM dbo_tbl_Allegation AS a

SQL Server
SELECT a.AllegationID, CASE WHEN a.EntryDate< CONVERT(datetime, '1/1/1970', 101) THEN CONVERT( datetime, '1/1/2000', 101) ELSE a.EntryDate END Entrydate
FROM dbo_tbl_Allegation AS a

Oracle:
SELECT a.AllegationID, CASE WHEN a.EntryDate< TO_DATE('1/1/1970', 'D/M/YYYY') THEN TO_DATE('1/1/2000', 'D/M/YYYY') ELSE a.EntryDate END Entrydate
FROM dbo_tbl_Allegation AS a
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18797691
Do note that CASE WHEN syntax is only supported from Oracle 9i onwards. For older versions like 8i, you can sometimes overcome with the DECODE function when doing exact comparisons like EntryDate = '1-Jan-2005'. However, for this case, you cannot use DECODE because yours is a range comparison. To make sure SQL work in Oracle 8i, the only I can think of is as follows:

SELECT a.AllegationID, Entrydate
FROM (
         (select AllegationID, '1-Jan-2000' EntryDate From dbo_tbl_Allegation where EntryDate< '1-Jan-1970') Union
         (select AllegationID, EntryDate From dbo_tbl_Allegation where EntryDate>= '1-Jan-1970' )
         )

Also, notice how I use the date without any conversion and relying on the implicit data type conversion. This will work even for SQL Server. The reason why I do this is for faster coding and easier code reading and it has work 100% thus far.

AngelIII, I like to hear your comment if such a  method of implicit data conversion should also be avoided?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18797977
>AngelIII, I like to hear your comment if such a  method of implicit data conversion should also be avoided?
yes, that should be avoided.
take your code, and connect with a user that has got configured french NLS (oracle) or french language (sql server). your code will fail, because of the english month names. Well, JAN will not fail, so bad example, but FEB will fail (french = FEV) ...
now, you might be in the situation where it will be 99.99% sure that your code will never run on anything non-english.
I can only be 50% sure that this would never happen.

>The reason why I do this is for faster coding and easier code reading
faster coding is one of the worst excuses, sorry
easier code reading? for that, use comments and things like variables in sql server

see this code, that is perfectly readable, and will be 100% sure in all server/user settings:

-- in the query, any date before 1970 will be raised to 2000
DECLARE @thresh_hold_date
DECLARE @replacement_date
SET @thresh_hold_date = CONVERT( datetime, '1/1/1970', 101)
SET @replacement_date = CONVERT( datetime, '1/1/2000', 101)

SELECT a.AllegationID
, CASE WHEN a.EntryDate<@thresh_hold_date
            THEN @replacement_date
            ELSE a.EntryDate
     END Entrydate
FROM dbo_tbl_Allegation AS a

0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18797999

Thx AngelIII for the advise. Yes I work on English systems only but I will bear in mind of what u said : )

cheers
Nickson
0
 
LVL 1

Author Comment

by:looper8
ID: 18798472
acperkiins: For this question I choose the areas Oracle, SQL Server, and Access as I thought they all had relevance.  Was that wrong?  Your code just gives the same error message I mentioned.

AngelIII: As this is a one off bit of code I didn't bother worrying about conversions, in another situation I would.  OK, perhaps I should because I'm sure I could do with the practice ... !

NicksonKoh: Yes the Oracle box is using 8i.  But what I don't get is whether at the point I'm at in the DTS import I should be using Oracle SQL or T-SQL.
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18798513

The union SQL which I have provided should work regardless of whether you are connecting to MS SQL or Oracle because that SQL only use pure SQL syntax.

My bet is the Case When syntax should fail in this case because Oracle 8i is involved.
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18798566
>>But what I don't get is whether at the point I'm at in the DTS import I should be using Oracle SQL or T-SQL.

DTS when connected to a Oracle db needs Oracle syntax SQL passed to it.

for Oracle 8i, the CASE expression is not available to you. SQL Server solution is given above (eg. by angelIII)
You should try this DECODE/SIGN expression for your Oracle box:

SELECT a.AllegationID,
DECODE(SIGN(a.EntryDate - TO_DATE('1/1/1970', 'D/M/YYYY'),1, TO_DATE('1/1/2000', 'D/M/YYYY') , a.EntryDate) Entrydate
FROM dbo_tbl_Allegation AS a
0
 
LVL 18

Accepted Solution

by:
Sham Haque earned 300 total points
ID: 18798571
whoopsie - missed a closing parenthesis...

SELECT a.AllegationID,
DECODE(SIGN(a.EntryDate - TO_DATE('1/1/1970', 'D/M/YYYY')),1, TO_DATE('1/1/2000', 'D/M/YYYY') , a.EntryDate) Entrydate
FROM dbo_tbl_Allegation AS a
0
 
LVL 1

Author Comment

by:looper8
ID: 18798572
OK, thanks.  I'm now trying to use the following and still get the 'Unexpected Error' message.

SELECT LOCATION_HISTORY.PERSON_ID, LOCATION_HISTORY.START_DATE
from (
      (SELECT LOCATION_HISTORY.PERSON_ID, '1-jan-2000' START_DATE FROM Location_history where location_history.start_date < '1-Jan-1970')
      union
      (SELECT LOCATION_HISTORY.PERSON_ID, LOCATION_HISTORY.START_DATE From location_history where start_date >= '1-jan-1970')
)
0
 
LVL 1

Author Comment

by:looper8
ID: 18798643
Perhaps it is something to do with date conversion stuff, as when I run the union bit alone (in SQL+) I get 'Expression must have same type as corresponding expression' ... have tried putting TO_DATE in to no avail (gives "Date format not recognised')

      (SELECT LOCATION_HISTORY.PERSON_ID, TO_DATE('1/1/2000', 'D/M/YYYY') START_DATE FROM midasdba.Location_history where location_history.start_date < TO_DATE('1-Jan-1970', 'D/M/YYYY'))
      union
      (SELECT LOCATION_HISTORY.PERSON_ID, TO_DATE(LOCATION_HISTORY.START_DATE, 'D/M/YYYY') From location_history where start_date >= TO_DATE('1-jan-1970', 'D/M/YYYY'))
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18798734
hi looper8

did you try my suggestion using DECODE and SIGN?
 this syntax is an exact translation of your IIF from Access, just need to work around the limitations around decode (cannot call comparison operators, hence use SIGN to check if it's a positive or negative result from the date calculation)
0
 
LVL 1

Author Comment

by:looper8
ID: 18798958
I've changed it to the following, but get 'date format not recognised' when run on SQL+

SELECT location_history.person_id,
DECODE(SIGN(location_history.start_date - TO_DATE('1/1/1970', 'D/M/YYYY')),1, TO_DATE('1/1/2000', 'D/M/YYYY') , location_history.start_date) Start_date
FROM location_history
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18799176
try changing the format model to "DD/MM/YYYY" - that should work for you...
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18799244
thanks for ALL the points looper8 but....you should have split the points between myself and drydenhogg/angelIII, who gave you the T-SQL syntax.
0
 
LVL 1

Author Comment

by:looper8
ID: 18799309
Oops you're right ... suppose it's too late to put that right?
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18799384
the question is now closed (all such actions are irreversible on EE afaik).
you could post a new question with a title such as "points for angelIII and drydenhoog", wait for them to reply and split the points....or just remember for next time.

:-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18800375
>>all such actions are irreversible on EE afaik<<
Actually no.  See here:
I accepted the wrong answer. Now what?
http://www.experts-exchange.com/help.jsp#hi17

Alternatively, if you ask nicely angelIII (in a Page Editor capacity) can re-open the question.
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 18800418
every day's a school day - thanks acperkins!
0
 
LVL 17

Expert Comment

by:NicksonKoh
ID: 18804784
Hi,

Anyway just to correct you on your date conversion.

(SELECT LOCATION_HISTORY.PERSON_ID, TO_DATE('1/1/2000', 'D/M/YYYY') START_DATE FROM midasdba.Location_history where location_history.start_date < TO_DATE('1-Jan-1970', 'D-Mon-YYYY'))
      union
      (SELECT LOCATION_HISTORY.PERSON_ID, TO_DATE(LOCATION_HISTORY.START_DATE, 'D/M/YYYY') From location_history where start_date >= TO_DATE('1-jan-1970', 'DD-MON-YYYY'))

The Decode with Sign is a clever method!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question