Subquery or Join?
I'm trying to query a table (lets call it "InvoicesTable") and I would
like to include the invoices last update remark by the employee whose
managing the invoice. In sql, I would probably do a subquery, using
the TOP function, but informix doesn't seem to allow you to use the
FIRST function within a subquery.
In SQL, I would write it like:
select i.invoice_number, (select top 1 u.remarks from user_comments u
where u.invoice_number = i.invoice_number and u.employeeID =
i.invoiceOwnerID order by u.input_date desc)
from InvoicesTable i
In informix, trying to use the same logic, I get the error "Cannot use
'first' in this context."
I also tried doing a join, but I couldn't figure out how to get the
last updated remark, it's just returning all remarks for that specific
user.
Sample Code
Select i.invoice_number, u.remarks
from InvoicesTable i
left join user_comments u on u.invoice_number = i.invoice_number andu.employeeID = i.invoiceOwnerID
Our informix database is a read only DR server. So, we are not able to
create stored procedures/functions or temp tables... It's a pain in
the neck dealing with the vendor and informix.
Is this query possible or an I way off here?
Thanks,
James
J. Hart wrote:
> I'm trying to query a table (lets call it "InvoicesTable") and I would
> like to include the invoices last update remark by the employee whose
> managing the invoice. In sql, I would probably do a subquery, using
> the TOP function, but informix doesn't seem to allow you to use the
> FIRST function within a subquery.
>
> In SQL, I would write it like:
> select i.invoice_number, (select top 1 u.remarks from user_comments u
> where u.invoice_number = i.invoice_number and u.employeeID =
> i.invoiceOwnerID order by u.input_date desc)
> from InvoicesTable i>
> In informix, trying to use the same logic, I get the error "Cannot use
> 'first' in this context."
>
> I also tried doing a join, but I couldn't figure out how to get the
> last updated remark, it's just returning all remarks for that specific
> user.
> Sample Code
> Select i.invoice_number, u.remarks
> from InvoicesTable i
> left join user_comments u on u.invoice_number = i.invoice_number and> u.employeeID = i.invoiceOwnerID
>
> Our informix database is a read only DR server. So, we are not able to
> create stored procedures/functions or temp tables... It's a pain in
> the neck dealing with the vendor and informix.
>
> Is this query possible or an I way off here?
>
> Thanks,
>
> James
You can include a where condition in the user_comments, like:
u.input_date = (select max(u1.input_date) from user_comments u1 where
u1.invoice_number = i.invoice_number).
Efficiency is another matter... If performance becomes a problem, you
can consider and index (invoice_number, input_date desc) on the
user_comments table.
Regards.
This will only work as expected if there are only one user_comments record
for the latest update date:
Select i.invoice_number, u.remarks
from InvoicesTable i
left join user_comments u
on u.invoice_number = i.invoice_number
and u.employeeID = i.invoiceOwnerID
and u.input_date = (
select max(uc.input_date)
from user_comments uc
where uc.invoice_number = i.invoice_number
);
I suppose you could add the FIRST clause to the outer SELECT though to
resolve that problem unless there's a sequence number you can MAX on as
well.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
See you at the 2010 IIUG Informix Conference
April 25-28, 2010
Overland Park (Kansas City), KS
www.iiug.org/conf
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, Mar 17, 2010 at 6:34 PM, J. Hart <unleashedmaniac@gmail.com> wrote:
> I'm trying to query a table (lets call it "InvoicesTable") and I would
> like to include the invoices last update remark by the employee whose
> managing the invoice. In sql, I would probably do a subquery, using
> the TOP function, but informix doesn't seem to allow you to use the
> FIRST function within a subquery.
>
> In SQL, I would write it like:
> select i.invoice_number, (select top 1 u.remarks from user_comments u
> where u.invoice_number = i.invoice_number and u.employeeID =
> i.invoiceOwnerID order by u.input_date desc)
> from InvoicesTable i>
> In informix, trying to use the same logic, I get the error "Cannot use
> 'first' in this context."
>
> I also tried doing a join, but I couldn't figure out how to get the
> last updated remark, it's just returning all remarks for that specific
> user.
> Sample Code
> Select i.invoice_number, u.remarks
> from InvoicesTable i
> left join user_comments u on u.invoice_number = i.invoice_number and> u.employeeID = i.invoiceOwnerID
>
> Our informix database is a read only DR server. So, we are not able to
> create stored procedures/functions or temp tables... It's a pain in
> the neck dealing with the vendor and informix.
>
> Is this query possible or an I way off here?
>
> Thanks,
>
> James
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On Mar 17, 3:48 pm, Fernando Nunes <domusonl...@gmail.com> wrote:
> J. Hart wrote:
> > I'm trying to query a table (lets call it "InvoicesTable") and I would
> > like to include the invoices last update remark by the employee whose
> > managing the invoice. In sql, I would probably do a subquery, using
> > the TOP function, but informix doesn't seem to allow you to use the
> > FIRST function within a subquery.
>
> > In SQL, I would write it like:
> > select i.invoice_number, (select top 1 u.remarks from user_comments u
> > where u.invoice_number = i.invoice_number and u.employeeID =
> > i.invoiceOwnerID order by u.input_date desc)
> > from InvoicesTable i>
> > In informix, trying to use the same logic, I get the error "Cannot use
> > 'first' in this context."
>
> > I also tried doing a join, but I couldn't figure out how to get the
> > last updated remark, it's just returning all remarks for that specific
> > user.
> > Sample Code
> > Select i.invoice_number, u.remarks
> > from InvoicesTable i
> > left join user_comments u on u.invoice_number = i.invoice_number and> > u.employeeID = i.invoiceOwnerID
>
> > Our informix database is a read only DR server. So, we are not able to
> > create stored procedures/functions or temp tables... It's a pain in
> > the neck dealing with the vendor and informix.
>
> > Is this query possible or an I way off here?
>
> > Thanks,
>
> > James
>
> You can include a where condition in the user_comments, like:
>
> u.input_date = (select max(u1.input_date) from user_comments u1 where
> u1.invoice_number = i.invoice_number).
>
> Efficiency is another matter... If performance becomes a problem, you
> can consider and index (invoice_number, input_date desc) on the
> user_comments table.
>
> Regards.- Hide quoted text -
>
> - Show quoted text -
Well... I actually need to get the max u.input_date, u.input_time....
I actually took your suggestion and also used ROWID. (If there is
anyone that used ROWID requestly, tell me if I'm using this
correctly)...
Select i.invoice_number, u.remarks
from InvoicesTable i
left join user_comments u on u.invoice_number = i.invoice_number andu.ROWID = (Select max(u1.ROWID) from user_comments u1 where
u1.invoice_number = i.invoice_number and u1.employeeID =
i.invoiceOwnerID)
Thanks,
James
On Mar 18, 4:34 am, "J. Hart" <unleashedman...@gmail.com> wrote:
> On Mar 17, 3:48 pm, Fernando Nunes <domusonl...@gmail.com> wrote:
>
>
>
>
>
> > J. Hart wrote:
> > > I'm trying to query a table (lets call it "InvoicesTable") and I would
> > > like to include the invoices last update remark by the employee whose
> > > managing the invoice. In sql, I would probably do a subquery, using
> > > the TOP function, but informix doesn't seem to allow you to use the
> > > FIRST function within a subquery.
>
> > > In SQL, I would write it like:
> > > select i.invoice_number, (select top 1 u.remarks from user_comments u
> > > where u.invoice_number = i.invoice_number and u.employeeID =
> > > i.invoiceOwnerID order by u.input_date desc)
> > > from InvoicesTable i>
> > > In informix, trying to use the same logic, I get the error "Cannot use
> > > 'first' in this context."
>
> > > I also tried doing a join, but I couldn't figure out how to get the
> > > last updated remark, it's just returning all remarks for that specific
> > > user.
> > > Sample Code
> > > Select i.invoice_number, u.remarks
> > > from InvoicesTable i
> > > left join user_comments u on u.invoice_number = i.invoice_number and> > > u.employeeID = i.invoiceOwnerID
>
> > > Our informix database is a read only DR server. So, we are not able to
> > > create stored procedures/functions or temp tables... It's a pain in
> > > the neck dealing with the vendor and informix.
>
> > > Is this query possible or an I way off here?
>
> > > Thanks,
>
> > > James
>
> > You can include a where condition in the user_comments, like:
>
> > u.input_date = (select max(u1.input_date) from user_comments u1 where
> > u1.invoice_number = i.invoice_number).
>
> > Efficiency is another matter... If performance becomes a problem, you
> > can consider and index (invoice_number, input_date desc) on the
> > user_comments table.
>
> > Regards.- Hide quoted text -
>
> > - Show quoted text -
>
> Well... I actually need to get the max u.input_date, u.input_time....
>
> I actually took your suggestion and also used ROWID. (If there is
> anyone that used ROWID requestly, tell me if I'm using this
> correctly)...
>
> Select i.invoice_number, u.remarks
> from InvoicesTable i
> left join user_comments u on u.invoice_number = i.invoice_number and> u.ROWID = (Select max(u1.ROWID) from user_comments u1 where
> u1.invoice_number = i.invoice_number and u1.employeeID =
> i.invoiceOwnerID)
>
> Thanks,
>
> James- Hide quoted text -
>
> - Show quoted text -
Hi James,
You can use sql liek below -
select i.inv_no,d.inv_dtl_comment
from invce_hdr i, invce_dtl d
where i.inv_emp_id = d.inv_dtl_emp_id
and i.inv_no = d.inv_dtl_no
and d.inv_dtl_cr_dt = (select max(dd.inv_dtl_cr_dt) from invce_dtl dd
where dd.inv_dtl_emp_id = i.inv_emp_id
and dd.inv_dtl_no = i.inv_no)
Regards,
Nandkishor