Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Translating IIF into SQL Server / Oracle

Posted on 2007-03-26
29
Medium Priority
?
1,245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 4
  • +5
29 Comments
 
LVL 66

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 8

Assisted Solution

by:drydenhogg
drydenhogg earned 400 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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 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 143

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 1200 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

610 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