Help with Conditionals

  • Thread starter Thread starter JohnCreighton_
  • Start date Start date
J

JohnCreighton_

SELECT index.id, Category =
CASE
WHEN (SELECT Compond_link_types.id FROM
Compond_link_types WHERE Compond_link_types.id>0)
THEN 'Compound Link'
WHEN (SELECT Link_types.id FROM id FROM
Link_types.id WHERE Link_types.id>0)
THEN 'Normal Link'
ELSE 'Something Else'
END
FROM ([index] LEFT JOIN Compond_link_types ON index.id =
Compond_link_types.id)
LEFT JOIN Link_types ON index.id = Link_types.id;

http://www.craigsmullins.com/ssu_0899.htm

I'm trying to do a conditional query by following the above link. I
can't figure out what I did wrong. Did I make a syntax error or does
Microsoft use a different syntax then the sql in the link I found?
 
SELECT index.id, Category =
CASE
WHEN (SELECT Compond_link_types.id FROM
Compond_link_types WHERE Compond_link_types.id>0)
THEN 'Compound Link'
WHEN (SELECT Link_types.id FROM id FROM
Link_types.id WHERE Link_types.id>0)
THEN 'Normal Link'
ELSE 'Something Else'
END
FROM ([index] LEFT JOIN Compond_link_types ON index.id =
Compond_link_types.id)
LEFT JOIN Link_types ON index.id = Link_types.id;

http://www.craigsmullins.com/ssu_0899.htm

I'm trying to do a conditional query by following the above link. I
can't figure out what I did wrong. Did I make a syntax error or does
Microsoft use a different syntax then the sql in the link I found?
Click to expand...

This is valid SQL for SQL/Server - but CASE is not supported by JET,
the database engine in Access.

John W. Vinson[MVP]
 
John said:
SELECT index.id, Category =
CASE
WHEN (SELECT Compond_link_types.id FROM
Compond_link_types WHERE Compond_link_types.id>0)
THEN 'Compound Link'
WHEN (SELECT Link_types.id FROM id FROM
Link_types.id WHERE Link_types.id>0)
THEN 'Normal Link'
ELSE 'Something Else'
END
FROM ([index] LEFT JOIN Compond_link_types ON index.id =
Compond_link_types.id)
LEFT JOIN Link_types ON index.id = Link_types.id;

http://www.craigsmullins.com/ssu_0899.htm

I'm trying to do a conditional query by following the above link. I
can't figure out what I did wrong. Did I make a syntax error or does
Microsoft use a different syntax then the sql in the link I found?
Click to expand...

This is valid SQL for SQL/Server - but CASE is not supported by JET,
the database engine in Access.

John W. Vinson[MVP]
Click to expand...

Oh, okay. Thanks for the information. I found an alternative way of
doing it. However the code is much longer.

SELECT
index.id,
mytable
FROM
[index]
LEFT JOIN
[
SELECT
*,
"Compond_link_types" As mytable
FROM Compond_link_types
]. AS Compond_link_types
ON
index.id = Compond_link_types.id
WHERE
Compond_link_types.id>0

UNION

SELECT
index.id,
mytable
FROM
[index]
LEFT JOIN
[
SELECT
*,
"Link_types" As mytable
FROM
Link_types
]. AS Link_types
ON
index.id = Link_types.id
WHERE
Link_types.id>0

UNION

SELECT
index.id,
mytable
FROM
[index]
LEFT JOIN
[
SELECT
*,
"Deffinitions" As mytable
FROM
Deffinitions
]. AS Deffinitions
ON
index.id = Deffinitions.id
WHERE
Deffinitions.id>0

UNION

SELECT
index.id,
mytable
FROM
[index]
LEFT JOIN
[
SELECT
*,
"Links" As mytable
FROM
Links
]. AS Links
ON
index.id = Links.id
WHERE
Links.id>0

UNION

SELECT
index.id,
mytable
FROM
[index]
LEFT JOIN
[
SELECT
*,
"Words" As mytable
FROM
Words
]. AS Words
ON
index.id = Words.id
WHERE
Words.id>0
;
 
Oh, okay. Thanks for the information. I found an alternative way of
doing it. However the code is much longer.
Click to expand...

OUCH!!!!

My hat's off to you, sir. That's impressive.

I worry about the database design that makes it necessary, but
sometimes that's just the way the cookie crumbles!

John W. Vinson[MVP]
 
Back
Top