IF/ELSE statements in SQL 1

[tejs1dhu]

Hi

I am working on a VB tool that uses an Access db. It was designed a while ago but at the moment needs some fixing. Basically I have the query below:

PARAMETERS NewUserID Long;
SELECT DISTINCT TOP 10 getField18.EntityID, getField18.EntityName, getField18.DateValue
FROM getField18 INNER JOIN getField19 ON getField18.EntityID = getField19.EntityID
ORDER BY getField18.DateValue DESC;

This is called from the VB code using the ADODB.COMMAND object which takes the statement to execute and 2 parameters. The above query is called.

What I need to do is check the second parameter and make a decision based on that i.e. i need something like the following:

PARAMETERS NewUserID Long, NewEntityType Text ( 1 );
SELECT DISTINCT TOP 10 getField18.EntityID, getField18.EntityName, getField18.DateValue
FROM getField18 INNER JOIN getField19 ON getField18.EntityID = getField19.EntityID
IF NewEntityType = 'V'
Begin
ORDER BY getField18.DateValue DESC
End
ELSE
Begin
ORDER BY getField18.DataValue ASC
End;

I have no idea if this would work, any help would be greatly appreciated.

 

[lespaul]

if you are using a VB tool then check beforehand and build the query dynamically:

SQL = "PARAMETERS NewUserID Long;
SELECT DISTINCT TOP 10 getField18.EntityID, getField18.EntityName, getField18.DateValue
FROM getField18 INNER JOIN getField19 ON getField18.EntityID = getField19.EntityID"


if NewEntityType = 'V' then
  SQL = SQL & "ORDER BY getField18.DateValue DESC"
else
  SQL = SQL & "ORDER BY getField18.DateValue ASC"
end

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

You may try this:
ORDER BY DateValue * IIf([NewEntityType]='V', -1, 1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tejs1dhu]

@lespaul

Thanks for your suggestion, unfortunately the VB tool is quite complex, at the moment I dont create the SQL within the tool but instead call the queries from Access. If I can't come up with a solution then I will use this as a last resort.

@PHV

I see what you are trying to do but unfortunately that didnt work I got an error saying ORDER BY clause (ORDER BY DateValue*IIf([NewEntityTye]='V',-1,1) conflicts with DISTINCT? Im I doing something wrong? NewEntityType is a parameter passed by the user.

 

[PHV]

And this ?
PARAMETERS NewUserID Long, NewEntityType Text ( 1 );
SELECT TOP 10 getField18.EntityID, getField18.EntityName, getField18.DateValue
FROM getField18 INNER JOIN getField19 ON getField18.EntityID = getField19.EntityID
ORDER BY getField18.DateValue * IIf([NewEntityType]='V', -1, 1);
Or this ?
PARAMETERS NewUserID Long, NewEntityType Text ( 1 );
SELECT DISTINCT TOP 10 getField18.EntityID, getField18.EntityName, getField18.DateValue, getField18.DateValue * IIf([NewEntityType]='V', -1, 1) As Rank
FROM getField18 INNER JOIN getField19 ON getField18.EntityID = getField19.EntityID
ORDER BY 4;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

NewEntityType is a parameter passed by the user

A parameter passed in to a function in the VB program or a Query parameter? I'm assuming the former, because your query only has the NewUserID parameter (which you're not using in this query either???)


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual