Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I'm relatively new to sql and I've been trying to get something which I think my programming background is not helping. Please help me with some examples.
I need to build a return statement with the concatenation of strings based on the possibilities of having or not 2 IDs

So far I've got this:
SQL
SELECT
....
  ,'ON VESSEL "' + VESSEL.NAME + '" FROM '
   + ORIGIN.NAME + '/' + ORIGIN.COUNTRY_NAME + 'TO'
   + CASE ISNULL(SHIP.TRANSSHIP_PORT_ID_1,0)
     WHEN 0 THEN
         ''
     ELSE
         PORT1.NAME + '/' + PORT1.COUNTRY_NAME
         + CASE ISNULL(SHIP.TRANSSHIP_PORT_ID_2,0) +
           WHEN 0 THEN
               ''
           ELSE
               ' AND TO ' 
               + PORT2.NAME + '/' + PORT2.COUNTRY_NAME
           END
         + 'IN TRANSIT TO' 
     END  
   + DESTINATION.NAME + '/' + DESTINATION.COUNTRY_NAME
FROM
....


The thing is, so far there is nothing stopping the user to delete the first record. Which would cause an error with the logic.
Is there a way to do something like:

SQL
CASE condition
WHEN test AND A = B THEN

So far I get an error due to the "AND"...


Thanks in advance
Posted
Updated 21-Feb-14 3:45am
v2
Comments
Andrius Leonavicius 21-Feb-14 15:15pm    
Hi,

Regarding to the CASE expression. Your usage of it is incorrect. I would recommend you to read this:
http://www.mindfiresolutions.com/Using-CASE-expression-in-SQL-queries-965.php

I could help you further, but I need more information about requirements of your task and data structure (tables).

1 solution

You are probably getting a syntax error "near to AND" ... the problem is with test You need a Boolean expression in there ... something like "test='Y'"
SQL server does not have a Boolean Data Type

Have a look at this link http://blog.sqlauthority.com/2007/04/14/sql-server-case-statementexpression-examples-and-explanation/[^]
or this reference material[^]
 
Share this answer
 
Comments
Paulo Augusto Kunzel 24-Feb-14 8:05am    
Thanks CHill60,

Those links really helped to better understand how it works. It is a shame that I have to do either simple or searched case instead of mixing the logic of both. But still, this was a lesson learned.
CHill60 24-Feb-14 8:16am    
You could mix the logic of both ... it depends on test really - if you could have something there that returns (say) a 0 for False and -1 for True you could have something like SELECT X = CASE WHEN Test = 0 AND A = B THEN OneValue ELSE OtherValue END, .... If you get what I mean :-)
Paulo Augusto Kunzel 24-Feb-14 9:09am    
I'm sorry, I'm bit confused.
Wouldn't that be the searched CASE?

When I meant mixing it would be something in the lines of:
SELECT X = CASE test WHEN 0 AND A = B THEN OneValue ELSE OtherValue END..
CHill60 24-Feb-14 9:58am    
Well yes, it is the searched case ... but the key is that SQL doesn't have a Boolean type so the CASE test bit won't work as is. Sorry I didn't mean to confuse the situation
Paulo Augusto Kunzel 24-Feb-14 11:38am    
It's alright. The fact that I can talk about both cases already is a great improvement that you helped me with.
thx again

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900