Re: [sqlite] CASE WHEN .. END AS 'blah' problem

[John LeSueur]

Steve Babineau wrote:
Hello all, 

 I'm having a problem here that seemed to work for SQLite < v3.
 When I do have a query like:
SELECT 
UPPER(col1)||
''
'||
col2||
'
'||
CASE 
 WHEN col3 IS NULL THEN ''19XX'' 
 ELSE col3
END AS 'col3'||
'
'
FROM mytable;

I get an error:
near "||": syntax error.
If I take out the 'AS 'col3'|| part, it works fine (but this won't be
helpful when setting this up as a view).  It seemed to work fine when
I tried with a previous version of SQLite and I'm not sure why I'm
having such an issue now..?
 I'm using the SQLite.NET dll from phpguru.org.  I did a search in
the archives of this mailing list and came up emtpy, so I think I
might be doing something wrong.  Can some other smarter people help me
out here, please?
Thanks for all your help!
 

I'll be honest, I'm having a hard time following that query, but if 
you're aliasing a query, the AS clause needs to be after all the 
concatenation. So assuming everything else is right,

SELECT 
UPPER(col1)||
''
'||
col2||
'
'||
CASE 
 WHEN col3 IS NULL THEN ''19XX'' 
 ELSE col3
END ||
'
'
AS 'col3'
FROM mytable;

should be correct, unless you're trying to to concatenate that newline 
to the actual name of the column.

John LeSueur