473,222 Members | 1,740 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,222 software developers and data experts.

Coalesce / Comma Delimitted List

I'm trying to return multiple column, one of which is a column + a comma delimitted list of values. Below is a simplified, non working, query:

(assume @MyBit, @MyVarChar, and @MyValue are all declared)

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @MyBit = 1 THEN ' My Test: ' + (select @MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @MyValue) ELSE '' END AS Column2,
FROM TableMain

I get an ADO error: Incorrect syntax near '='


Oct 19 '06 #1
3 3956
143 100+
(select @MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @MyValue)

use "[ ]" brackets instead of "( )" around the above select statment.

(and remove the last comma before the FROM stmnt)
Oct 19 '06 #2
I now have:

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @MyBit = 1 THEN ' My Test: ' + [select @MyVarChar = COALESCE(Column3 + ', ', '') from TableSub where Value = @MyValue] ELSE '' END AS Column2
FROM TableMain

and I get:
ADO error: Invalid column name 'select @MyVarChar = COALESCE(Column3 + ',', '') from TableSub where Value = @MyValue'

Oct 19 '06 #3
143 100+
my apologies - here is the correct query

SELECT distinct
IsNull(Column1, '') AS Column1,
IsNull(Column2,'') + CASE WHEN @MyBit = 1 THEN ' My Test: ' + (select COALESCE(Column3 + ', ', '') from TableSub where Value = @MyValue) ELSE '' END AS Column2
FROM TableMain

put the "( )" back on and remove the @MyVarChar = (that is where the err is)
you just need select coalesce...
Oct 20 '06 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Alberto Vera | last post by:
Hello: I have the next structure: How Can I make it using Python? How Can I update the value of 6?
by: Paul Davis | last post by:
I'd like to overload 'comma' to define a concatenation operator for integer-like classes. I've got some first ideas, but I'd appreciate a sanity check. The concatenation operator needs to so...
by: Jeff Roughgarden | last post by:
I am using a clever product called SQL Expert Pro to optimize problematic SQL statements. It works by generating syntactically identical variations of the original SQL, finding all unique execution...
by: Craig Keightley | last post by:
I have a mysql database with a list of companies who supply specific products tblSuppliers (simplified) sID | sName | goodsRefs 1 | comp name | 1,2,3,4,5 2 | company 2 | 2,4
by: Steffen Vulpius | last post by:
Hi everybody, VARCHAR has a higher precedence than CHAR. If you have a query SELECT COALESCE(c1,c2) FROM T1 where c1 is CHAR(5) and c2 is VARCHAR(10), I would expect the return type to be...
by: Simon Windsor | last post by:
Hi Is there a standard postgres method of replacing empty strings. In Oracle, nvl handles nulls and empty strings, as does ifnull() in MySQL, but with postgres coalesce only handles null...
by: therealtigger | last post by:
i know I'm going mad. I did this just a couple of weeks ago, now I can't get anything in the export window to let me send query results as a comma delimited txt file. I'm new to access but i thought...
by: r035198x | last post by:
This is a short tip describing the sql coalesce function. Description COALESCE accepts a comma separated list of parameters and returns the first value that is not null in that list or null...
by: hansoffate | last post by:
Hi, The first part of my script works fine. Basically, the script reads a file with IDs that I want to search from a Flatdatabase and pull information. I setup the a TILDA Delimitted File...
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.