$sqlselect = "SELECT XL.id,
XL.country,
XLcountry.couname,
XL.deal_name,
XL.Sector,
XLSector.secname,
XL.mainProduct,
XLProd.prodname,
XL.exptRev,
XL.status,
XLstatus.staname,
XL.implManager,
XL.salesperson,
XLComm.commName,
TO_CHAR(winDate,'MM/DD/YYYY') as winDate ,
TO_CHAR(origLiveTargetDate,'MM/DD/YYYY') as
origLiveTargetDate ,
TO_CHAR(entryDate,'MM/DD/YYYY') as entryDate";
$sqlfrom = "FROM ximplLatam XL,
ximplLatamSector XLSector,
ximplLatamCountry XLCountry,
ximplLatamProd XLProd,
ximplLatamComm XLComm,
ximplLatamStatus XLStatus";
$sqlwhere = "WHERE
XL.sector = XLSector.seccode(+)
AND XL.country = XLcountry.coucode(+)
AND XL.mainProduct = XLProd.prodcode(+)
AND XL.comments = XLComm.commCode(+)
AND XL.status = XLstatus.staCode(+)";
$qlorderby = "order by XL.id";
$sql = "$sqlselect
$sqlfrom
$sqlwhere
$qlorderby
HAVING rownum <= 25";
$sth = $dbh->prepare($sql);
# BIND THE RETURNED COLUMNS TO SPECIFIC RETURN VARIABLES
$sth->bind_columns(undef, \$id,
\$imcountry,
\$country,
\$deal_name,
\$imsector,
\$sector,
\$immainProduct,
\$mainProduct,
\$exptRev,
\$imstatus,
\$status,
\$implManager,
\$salesperson,
\$comments,
\$winDate,
\$origLiveTargetDate,
\$entryDate);
$sth->execute or die("Could not execute query.\n$DBI::errstr");
# QUERY THE DB FOR STATUS CODES TO BE RESOLVED AND ASSIGN TO AN ARRAY
$counter = 0;
while ($sth->fetchrow_arrayref) {
# SET THE ROW COLOR. ODD IS LIGHT SILVER, EVEN IS WHITE
if (($counter % 2) == 0) {
$bgColor = "E0E0E0";
} else {
$bgColor = "FFFFFF";
}
}
I don't work for some time whith this but must work. Code is for a machine
to parse but for a human to understand also ;)
by
-----Original Message-----
From: Coello, David [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 06, 2003 3:24 PM
To: Daniela Silva - Absoluta.net; Gary Stainburn; [EMAIL PROTECTED]
Subject: RE: how to do paging of records
thank you Daniela and also gary this is what i got. it is a very complex
query how can i make this work, Please help!!! :o)
$sqlselect = "SELECT ximplLatam.id, ximplLatam.country,
ximplLatamcountry.couname, ximplLatam.deal_name, ximplLatam.Sector,
ximplLatamSector.secname, ximplLatam.mainProduct, ximplLatamProd.prodname,
ximplLatam.exptRev, ximplLatam.status, ximplLatamstatus.staname,
ximplLatam.implManager, ximplLatam.salesperson, ximplLatamComm.commName,
TO_CHAR(winDate,'MM/DD/YYYY') as winDate ,
TO_CHAR(origLiveTargetDate,'MM/DD/YYYY') as origLiveTargetDate ,
TO_CHAR(entryDate,'MM/DD/YYYY') as entryDate";
$sqlfrom = "FROM ximplLatam, ximplLatamSector, ximplLatamCountry,
ximplLatamProd, ximplLatamComm, ximplLatamstatus ";
$sqlwhere = "where ximplLatam.sector = ximplLatamSector.seccode(+) and
ximplLatam.country = ximplLatamcountry.coucode(+) and ximplLatam.mainProduct
= ximplLatamProd.prodcode(+) and ximplLatam.comments =
ximplLatamComm.commCode(+) and ximplLatam.status =
ximplLatamstatus.staCode(+) and rownum <= 25";
$qlorderby = "order by id";
$sql = "$sqlselect $sqlfrom $sqlwhere $qlorderby";
$sth = $dbh->prepare($sql);
# BIND THE RETURNED COLUMNS TO SPECIFIC RETURN VARIABLES
$sth->bind_columns(undef, \$id, \$imcountry,\$country,
\$deal_name,\$imsector, \$sector, \$immainProduct, \$mainProduct, \$exptRev,
\$imstatus, \$status,\$implManager, \$salesperson, \$comments, \$winDate,
\$origLiveTargetDate, \$entryDate);
$sth->execute or die("Could not execute query.\n$DBI::errstr");
# QUERY THE DB FOR STATUS CODES TO BE RESOLVED AND ASSIGN TO AN ARRAY
$counter = 0;
while ($sth->fetchrow_arrayref) {
# SET THE ROW COLOR. ODD IS LIGHT SILVER, EVEN IS WHITE
if (($counter % 2) == 0) {
$bgColor = "E0E0E0";
} else {
$bgColor = "FFFFFF";
}
-----Original Message-----
From: Daniela Silva - Absoluta.net [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 06, 2003 9:11 AM
To: Coello, David; Gary Stainburn; [EMAIL PROTECTED]
Subject: Re: how to do paging of records
Hi, try using :
select *
from yourtable
where rownum < 50;
Only first 50 registers should be retrieved.
You could do for each page:
where rownum > page*nro_reg_per_page and
rownum < page * nro_reg_per_page + nro_reg_per_page
Or something like that, this is the idea.
----- Original Message -----
From: "Coello, David" <[EMAIL PROTECTED]>
To: "Gary Stainburn" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, August 06, 2003 9:58 AM
Subject: RE: how to do paging of records
oracle doesnt accept a limit clause, so i have to work around that, any
ideas! i been stuck on this
issue over a few days...
-----Original Message-----
From: Gary Stainburn [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 06, 2003 8:57 AM
To: Coello, David; [EMAIL PROTECTED]
Subject: Re: how to do paging of records
On Wednesday 06 Aug 2003 1:40 pm, Coello, David wrote:
> can anyone help me on how to do paging of records i want a few records per
> page, im using oracle 8i.!!! and cgi.
> david
I personally would look at LIMIT and OFFSET clauses for your select
statement
to enable you to choose the chunk of records retrieved
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Esta mensagem foi verificada pelo E-mail Protegido Terra.
Scan engine: VirusScan / Atualizado em 01/08/2003 / Vers�o: 1.3.13
Proteja o seu e-mail Terra: http://www.emailprotegido.terra.com.br/
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
