comma delimited list

am 07.10.2003 02:06:01 von brianm

I'm moving from MS SQL to mySQL while using perl and dbi. I'm trying to
preform the following statement...

MS SQL
----------------------
DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ',', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList AS EmpIDList
----------------------
RESULT

EmpIDList
--------------
22,54,95,78,62

mySQL
----------------------
SELECT (@EmployeeList = COALESCE(@EmployeeList + ',', '') + Emp_UniqueID) AS
EmpIDList
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
----------------------
RESULT

EmpIDList
--------------
(NULL)
(NULL)
(NULL)
(NULL)
(NULL)

I'm trying to get a comma delimited list back from the query, although all I
get back are 5 columns that are all NULL. Can anyone tell me whats going on,
what i'm doing wrong, or possibly suggest another way to create a comma
delimited list in one select statement?

Thanks,
Brian

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: comma delimited list

am 07.10.2003 04:47:35 von John Von Essen

It might help if I knew the table's scheme (describe ...), and exactly
what you want to get out.

However, here is another way to do it external to MySQL using Perl
logic instead. For example, lets say your table had three columns:
EmpName, Emp_UniqueID, and SalCal_UniqueID.

And I did: SELECT Emp_UniqueID from SalesCallsEmployees WHERE
SalCal_UniqueID = 1;

In Perl DBI I could do:

use DBI;
use strict;

my $concat = ();
my $dbh = ();
my $sth = ();
$dbh =
DBI-
>connect("DBI:mysql:database=db;host=localhost","user","pass ",{'RaiseErr
or' => 1});
$sth = $dbh->prepare("SELECT Emp_UniqueID from SalesCallsEmployees
WHERE SalCal_UniqueID = 1");
$sth->execute();
while(my $ref = $sth->fetchrow_hashref()) {
$concat = $concat . $ref->{Emp_UniqueID} . ',';
}
$sth->finish();
$dbh->disconnect();

chop($concat); # To chop the trailing comma
print "$concat\n";

The above will give a comma delimited list of Emp_UniqueID's. I just
use Perl to form the list, rather then SQL commands. I personally don't
have time to learn all the in's and out's of the non-basic SQL commands.

-John

On Monday, October 6, 2003, at 08:06 PM, brianm@invite.net wrote:

> I'm moving from MS SQL to mySQL while using perl and dbi. I'm trying
> to
> preform the following statement...
>
> MS SQL
> ----------------------
> DECLARE @EmployeeList varchar(100)
>
> SELECT @EmployeeList = COALESCE(@EmployeeList + ',', '') +
> CAST(Emp_UniqueID AS varchar(5))
> FROM SalesCallsEmployees
> WHERE SalCal_UniqueID = 1
>
> SELECT @EmployeeList AS EmpIDList
> ----------------------
> RESULT
>
> EmpIDList
> --------------
> 22,54,95,78,62
>
> mySQL
> ----------------------
> SELECT (@EmployeeList = COALESCE(@EmployeeList + ',', '') +
> Emp_UniqueID) AS
> EmpIDList
> FROM SalesCallsEmployees
> WHERE SalCal_UniqueID = 1
> ----------------------
> RESULT
>
> EmpIDList
> --------------
> (NULL)
> (NULL)
> (NULL)
> (NULL)
> (NULL)
>
> I'm trying to get a comma delimited list back from the query, although
> all I
> get back are 5 columns that are all NULL. Can anyone tell me whats
> going on,
> what i'm doing wrong, or possibly suggest another way to create a comma
> delimited list in one select statement?
>
> Thanks,
> Brian
>
>
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=john@essenz.com
>
>
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: comma delimited list

am 07.10.2003 04:47:35 von John Von Essen