"Bob Smith" <> schrieb im Newsbeitrag
news:db5b0h$aol$...
> Peter.Kramer wrote:
>
>>
>> "Paul Lalli" <> schrieb im Newsbeitrag
>> news: oups.com...
>>> Peter.Kramer wrote:
>>>> I'm wondering why I cant get all resulst in my hasref in this
>>>> statement:
>>>
>>> "Hashref". Not "hasref".
>>>
>>>> --
>>>> select r.resourcesize, a.samaccountname
>>>>
>>>> from aduser a
>>>> (
>>>> select * from resourcesize
>>>> where ts = (select max(ts) from resourcesize where server = 'xyz')
>>>> ) r
>>>>
>>>> where a.samaccountname = r.samaccountname(+)
>>>> ----
>>>>
>>>> What I get is an hasref and all data from table "a" and all fields of
>>>> table
>>>> "r" but no data of table "r".
>>>> I assume, that the sub select is the problem.
>>>>
>>>> result:
>>>> $hash->{SAMACCOUNTNAME} = "tiger"
>>>> $hash->{RESOURCESIZE} = ""
>>>>
>>>> When I execute the script in SQL Plus all Data is availible!
>>>>
>>>> $hash->{SAMACCOUNTNAME} = "tiger"
>>>> $hash->{RESOURCESIZE} = "1234"
>>>>
>>>> I'm using Active State Perl 5.8.4 with DBD-Oracle and Oracle 9i
>>>
>>>>From the lack of code posted, I gather you believe there is something
>>> wrong with Perl and/or DBD-Oracle, rather than something wrong with
>>> your code? That's mighty arrogant of you. You'll forgive me if I tend
>>> not to believe you. Please post a short-but-complete script that
>>> demonstrates the problem you're having. Have you seen the posting
>>> guidelines for this newsgroup? Please read them, and follow at least 2
>>> particulars:
>>> Speak Perl, not English. (Show us, in Perl, what your variable
>>> contains, don't try to tell us what you think it contains).
>>> Post the aforementioned short-but-complete script.
>>>
>>> Paul Lalli
>>>
>>
>> Here the complete Code.
>> But as I said, when I copy the sql statement to sql+ i see all expected
>> data. Its not the first time that I use DBI, but the first time I use a
>> sub select.
>> I assume a problem with perl because of the sql+ experience
>>
>> schnipp:
>> ---
>> use DBI;
>> my $sizelimit =0;
>> my $sizelimitmail =0;
>>
>>
>> my $dbh=DBI->connect("DBI:Oracle:$database","user","pass") or die "no
>> connect\n";
>>
>> my $sql ="
>> select
>> resourcesize R,a.samaccountname USR,displayname,
>> department,m.sizemb Mailboxsize, floor((resourcesize)/(1024)) rsize,
>> a.EXTENSIONATTRIBUTE9 Kostenstelle
>>
>> from btrsammdba.aduser a, btrsammdba.mailboxsize m,
>> (
>> select * from btrsammdba.resourcesize
>> where ts = (select max(ts) from btrsammdba.resourcesize where server
>> =
>> 'BMAIZ-FILEN02')
>> ) r
>>
>> where a.samaccountname = r.samaccountname(+)
>> and a.samaccountname = m.samaccountname(+)
>>
>> and a.EXTENSIONATTRIBUTE9 is not null
>> and a.EXTENSIONATTRIBUTE3 is null
>> and m.sizemb > '$sizelimitmail'
>>
>> order by a.EXTENSIONATTRIBUTE9,resourcesize desc
>>
>>
>> ";
>>
>>
>> print "\n$query\n";
>>
>>
>> print "creating query.... ";
>> my $sth = $dbh->prepare($sql, $attr) or return undef;
>> $sth->execute;
>> print "...created\n";
>>
>> print $DBI::errstr."\n";
>> while( my $href= $sth->fetchrow_hashref)
>> {
>>
>> foreach my $key (keys %$href)
>> {
>> print "$key -> $href->{$key}\n"; # here is no value in rsize
>> }
>> }
> hmm, I don't know, are you sure that the field you call resourcesize is
> populated?
>
> what about the floor function, what fieldtype is resourcesize, ?
> --
> http://www.kolumbus.fi/bob.smith
Yes I'm sure, when I use a sql statemnet that has no sub select all adta is
presented well.
the floor function works proper as well, the data type is NUMBER(38,0)
Similar Threads
