Adding two queries together 1

[fordtran]

I need to retrieve two sets of fields (one field for one condition and two fields for another condition) depending on the value of the third field - something like :
'select townname from towns where activated=false and townname, websitename from towns where activated=true'
This query is wrong - can somebody help me to structure it correctly.

fordtran

 

[DotNetGnat]

select townname,
iif(activated='true', websitename,'') as websitename
from mytable

-DNG

 

[fordtran]

DotNetGnat

Thanks - I think it will work - can you just please check the syntax - it gives me an error.


fordtran

 

[DotNetGnat]

what is the error you are getting...

select townname,
iif(activated='true', websitename,'') as website
from towns

are you sure the values for activated are true and false in the database...just check and make appropriate changes in the query

-DNG

 

[fordtran]

It gave me this error :
System.InvalidOperationException: The provider could not determine the String value. For example, the row was just created, the default for the String column was not available, and the consumer had not yet set a new String value.


fordtran

 

[DotNetGnat]

try this:

select townname,
iif(activated='true', websitename,NULL) as website
from towns

or try this:

Select townname, NULL from towns
where activated='false'
UNION ALL
Select townname, websitename from towns
where activated='true'

-DNG

 

[fordtran]

Thx DotNetGnat

The second one nearly works, but it gives the error :
System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.
which I think is because of the null

fordtran

 

[DotNetGnat]

then how about this:

Select townname, '' as websitename from towns
where activated='false'
UNION ALL
Select townname, websitename from towns
where activated='true'

-DNG

 

[stix4t2]

How about posting the code that is causing the error so it can be checked for syntax. Also any variable declarations.

Stix 42
Long Live Rock and Roll
Pop is for drinking

 

[PHV]

And what about this ?
SELECT townname, websitename FROM towns WHERE activated = TRUE
UNION ALL SELECT townname, '' FROM towns WHERE activated = FALSE

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

 

[fordtran]

PHV
You get the star - thank you very much

fordtran