Translating IIF into SQL Server / Oracle

SELECT a.AllegationID, CASE a.EntryDate WHEN < '1/1/1970' THEN '1/1/2000' ELSE a.EntryDate END
FROM dbo_tbl_Allegation a

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

That gives: incorrect syntax near <

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"

^%$£ I'm too tired for this ...

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

Thanks all.

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

>>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:
https://www.experts-exchange.com/Database/Oracle

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

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?

>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

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

cheers
Nickson

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.

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.

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

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')
)

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'))

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)

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

try changing the format model to "DD/MM/YYYY" - that should work for you...

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.

Oops you're right ... suppose it's too late to put that right?

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.

:-)

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

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

every day's a school day - thanks acperkins!

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!