IIf ..then...case...switch - I'm not sure

D

dchristo

I am creating a pass thru query in SQL

SELECT
dbo.flat_master1.id,dbo.flat_master1.filercvd,dbo.flat_master1.claim_type,dbo.flat_master2.saledate,dbo.master2.deliverdate,,dbo.master2.state,IIf([claim_type]
in('bike'),dbo.master2.saledate +60,IIf([Claim_type] Not in
('bike'),dbo.mistat2.deliverdate + 60)) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2 on dbo.flat_master1.muniq_id =
dbo.master2.uniq_id)

I cannot get the iif statement to work.

Any help would be appreciated.
 
S

Stefan Hoffmann

hi,
I am creating a pass thru query in SQL

SELECT
dbo.flat_master1.id,dbo.flat_master1.filercvd,dbo.flat_master1.claim_type,dbo.flat_master2.saledate,dbo.master2.deliverdate,,dbo.master2.state,IIf([claim_type]
in('bike'),dbo.master2.saledate +60,IIf([Claim_type] Not in
('bike'),dbo.mistat2.deliverdate + 60)) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2 on dbo.flat_master1.muniq_id =
dbo.master2.uniq_id)

I cannot get the iif statement to work.
You need to rewrite the IIF(), if the destination SQL dialect does not
support it.

If it's T-SQL you may use

CASE [Claim_type] = 'bike'
THEN 0
ELSE [deliverdate] + 60
END AS [Projected Date]


mfG
--> stefan <--
 
J

Jerry Whittle

I see four things:

1. After dbo.master2.deliverdate there seems to be two commas ,, .

2. I'm not sure that IIf's will work in a pass-thru.

3. "ondbo.mistat2.deliverdate" That looks like a typo as there is an extra
period and the table name doesn't match anythiing.

4. Looks like your second IIf is missing an argument. It doesn't what to do
if it isn't in 'bike'.
Actually the statement below is simpler and should do the job.

SELECT
dbo.flat_master1.id,
dbo.flat_master1.filercvd,
dbo.flat_master1.claim_type,
dbo.flat_master2.saledate,
dbo.master2.deliverdate,
dbo.master2.state,
IIf([claim_type] = 'bike', dbo.master2.saledate +60,
dbo.mistat2.deliverdate + 60) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2
on dbo.flat_master1.muniq_id = dbo.master2.uniq_id) ;

Another problem just might be with the single quotes. Some databases work
differently with single or double quotes.
 
J

Jerry Whittle

Looks like I made a typo. Try this:

SELECT
dbo.flat_master1.id,
dbo.flat_master1.filercvd,
dbo.flat_master1.claim_type,
dbo.flat_master2.saledate,
dbo.master2.deliverdate,
dbo.master2.state,
IIf([claim_type] = 'bike', dbo.master2.saledate +60, dbo.master2.deliverdate
+ 60) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2
on dbo.flat_master1.muniq_id = dbo.master2.uniq_id) ;
 
D

dchristo

Thank you for replying. I keep getting incorrect syntax near '='. (#170)

Jerry Whittle said:
Looks like I made a typo. Try this:

SELECT
dbo.flat_master1.id,
dbo.flat_master1.filercvd,
dbo.flat_master1.claim_type,
dbo.flat_master2.saledate,
dbo.master2.deliverdate,
dbo.master2.state,
IIf([claim_type] = 'bike', dbo.master2.saledate +60, dbo.master2.deliverdate
+ 60) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2
on dbo.flat_master1.muniq_id = dbo.master2.uniq_id) ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dchristo said:
I am creating a pass thru query in SQL

SELECT
dbo.flat_master1.id,dbo.flat_master1.filercvd,dbo.flat_master1.claim_type,dbo.flat_master2.saledate,dbo.master2.deliverdate,,dbo.master2.state,IIf([claim_type]
in('bike'),dbo.master2.saledate +60,IIf([Claim_type] Not in
('bike'),dbo.mistat2.deliverdate + 60)) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2 on dbo.flat_master1.muniq_id =
dbo.master2.uniq_id)

I cannot get the iif statement to work.

Any help would be appreciated.
 
J

John W. Vinson

Thank you for replying. I keep getting incorrect syntax near '='. (#170)

That's because IIF() is a builtin Access query engine function which is
unknown and unavailable to T/SQL (or to PL-SQL or to DB2 or the other database
engines to which you might be issuing passthrough queries... you haven't
said).
 
D

dchristo

The SQL Pass-Through query in Access

John W. Vinson said:
That's because IIF() is a builtin Access query engine function which is
unknown and unavailable to T/SQL (or to PL-SQL or to DB2 or the other database
engines to which you might be issuing passthrough queries... you haven't
said).
 
D

Daryl S

Try changing your AS from Projected Date to Projected_Date.

--
Daryl S


dchristo said:
Thank you for replying. I keep getting incorrect syntax near '='. (#170)

Jerry Whittle said:
Looks like I made a typo. Try this:

SELECT
dbo.flat_master1.id,
dbo.flat_master1.filercvd,
dbo.flat_master1.claim_type,
dbo.flat_master2.saledate,
dbo.master2.deliverdate,
dbo.master2.state,
IIf([claim_type] = 'bike', dbo.master2.saledate +60, dbo.master2.deliverdate
+ 60) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2
on dbo.flat_master1.muniq_id = dbo.master2.uniq_id) ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


dchristo said:
I am creating a pass thru query in SQL

SELECT
dbo.flat_master1.id,dbo.flat_master1.filercvd,dbo.flat_master1.claim_type,dbo.flat_master2.saledate,dbo.master2.deliverdate,,dbo.master2.state,IIf([claim_type]
in('bike'),dbo.master2.saledate +60,IIf([Claim_type] Not in
('bike'),dbo.mistat2.deliverdate + 60)) AS Projected Date
From (dbo.flat_master1 inner join dbo.master2 on dbo.flat_master1.muniq_id =
dbo.master2.uniq_id)

I cannot get the iif statement to work.

Any help would be appreciated.
 
J

John W. Vinson

The SQL Pass-Through query in Access

Yes, but it is being *EXECUTED* by SQL/Server or some other program. That's
what a pass through means - it is "passed through" Access to some *OTHER*
server program which in turn executes it. Access does not parse it or
understand it or evaluate it at all - it passes it through, verbatim, to the
server.

If the server sees something (such as IIF) which it doesn't recognize, it will
generate an error and return that error to your program.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top