Re^5: Multiple queries on DBI corresponding to multiple csv files?

That piece of code is returning only the last id. How to get all ids using it?

I have changed it for the below code but now it is returning only the first element. Can you help me, please?

if ($id[0] eq "")
{   
    my $sql = "SELECT DISTINCT id FROM mytable WHERE active = ?";
    $id[0] =  @{$dbh->selectall_arrayref($sql,undef,'no')};
    #@id = map{ $_->[0] } @$ref;
    print join (", ", @id), "\n";
}
[download]

Comment on Re^5: Multiple queries on DBI corresponding to multiple csv files? Download Code

Replies are listed 'Best First'.
Re^6: Multiple queries on DBI corresponding to multiple csv files? by hippo (Archbishop) on Feb 21, 2019 at 11:18 UTC
I have changed it for the below code but now it is returning only the first element. `$id[0] = @{$dbh->selectall_arrayref($sql,undef,'no')};` [download] It's returning all the elements but you are then discarding all but the first. If you want them all, keep them all: `@id = @{$dbh->selectall_arrayref($sql,undef,'no')};` [download]	[reply] [d/l] [select]
Re^7: Multiple queries on DBI corresponding to multiple csv files? by jtech (Sexton) on Feb 21, 2019 at 12:38 UTC
It seems I am not able to integrate array, scalar, reference and variables using an undefined "" parameter. The code explains better than me: `# When it is not typed, the arguments will start as "" blank # (which means all ids) @id = ""; # Check typed argument ("-id1", duplicated or none) foreach my $arg (@ARGV) { if ($arg =~ /^--?id(\d+)$/) { die "Error: Multiple ids specified: -id@id and $arg.\n" if ( +$id[0]); @id = $1 ; next; } } # Exec problem if ($id[0] eq "") { my $sql = "SELECT DISTINCT id FROM mytable WHERE active = ?"; @id = @{$dbh->selectall_arrayref($sql,undef,'no')}; print join (", ", @id), "\n"; }` [download] Output error: `Running SELECT id FROM mytable WHERE id=? AND salary >= 1 for ARRA +Y(0x1011e70) ID: ARRAY(0x1011e70), 1 lines dumped to csvfile1_ARRAY(0x1011e70).csv` [download] Output error when replaced $id[0] to @id at if conditional: `if (@id eq "")` `Argument "" isn't numeric in subroutine entry at ./test.pl line 108.` [download]	[reply] [d/l] [select]
Re^8: Multiple queries on DBI corresponding to multiple csv files? by poj (Abbot) on Feb 21, 2019 at 13:00 UTC
`->selectall_arrayref()` returns a reference to an Array of Arrays `[ [field1,field2,etc],[field1,field2,etc],[field1,field2,etc]]` so you need the map to extract the first field from each record. Your query only returns one field but the structure is the same. `my $ref = $dbh->selectall_arrayref($sql,undef,'no'); @id = map{ $_->[0] } @$ref;` [download] poj	[reply] [d/l] [select]
Re^7: Multiple queries on DBI corresponding to multiple csv files? by jtech (Sexton) on Feb 21, 2019 at 12:53 UTC
Please ignore it, I don't have a clue what I am doing The code was always right, just realized the where clause has bitten me here. lol	[reply]