Re: a select statement question

Martin Meadows <mmeadows_at_indy.net> wrote:

>Hello again. Based on what Robert & Josef wrote last Friday I'm
> going to create a sql statement that looks like this:
>
> select ptd_job_fn
> from payroll_transaction_detail
> where
> ptd_so_# = :pjms_so_n,
> and ptd_so_item_# = :pjms_so_item_n,
> and ptd-so_chg_# = :pjms_so_chg_n
> minus
> select bmh_job_fn
> from bill_material_hours
> where
> bmh_so_# = :pjms_so_n,
> and bmh_so_item_# = :pjms_so_item_n,
> and bmh_so_chg_# = :pjms_so_chg_n;
>
> I'm hoping that the result will be all all ptd_job_fn #'s not found
> in the bill_material_hours table. Everything about bmh_job_fn is the
>same
> as ptd_job_fn. Just different names.
>
> Will this work?
>
> Thanks,
> Martin Meadows

I think for the set operator to work, the field names must be the same in each select statement ( tho I'm not at all sure of this ) , so use an alias for each of your statements

select ptd_job testjob from table1 where..... minus
select bmh_job testjob from table2 where....

This should work ok.... Received on Tue Jun 16 1998 - 09:20:23 CDT