Solved

Translating IIF into SQL Server / Oracle

Posted on 2007-03-26
29
1,167 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now