> > SELECT trans_no,
> > customer,
> > date_placed,
> > date_complete,
> > date_printed,
> > ord_type,
> > ord_status,
(select SUM(sell_price) -- this syntax working for me. see
below
from soh_product
where sales_orders.trans_no = soh_product.soh_num
) as transact_sum,
> > customer_reference,
> > salesman,
> > parent_order,
> > child_order,
> > order_number
> > FROM sales_orders
> > WHERE (trans_no Like '8%' AND order_number Like '8%')
> > OR (trans_no Like '9%' AND order_number Like '8%')
> > OR (trans_no Like '8%' AND order_number Like '9%')
> > OR (trans_no Like '9%' AND order_number Like '9%')
> > AND (warehouse='M')
> > AND (date_placed > (current_date + ('12 months ago'::interval)))
> > ORDER BY trans_no DESC
>
>
> I am pretty new to SQL. But while reading a book written by an author
> recommended on this
> list,I
> can suggest a possible solution that I've seen. It might work for your
> problem. Of course, I
> haven't tested anything like this and don't know if PostgreSQL supports it.
>
> Just be sure that trans_no is unique in the returned query.
select
f1.fiscalyear,
(select f2.startdate
from fiscalyeartable2 as f2
where f1.fiscalyear = f2.fiscalyear
) as start2date,
f1.enddate
from
fiscalyeartable1 as f1;
fiscalyear | start2date | enddate
------------+------------+------------
1995 | 1994-10-01 | 1995-09-30
1996 | 1995-10-01 | 1996-08-30
1997 | 1996-10-01 | 1997-09-30
1998 | 1997-10-01 | 1998-09-30
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on fiscalyeartable1 f1 (cost=0.00..6.83 rows=1 width=6)
(actual time=0.044..0.067 rows=4 loops=1)
SubPlan
-> Index Scan using fiscalyeartable2_pkey on fiscalyeartable2 f2
(cost=0.00..5.82 rows=1 width=4)
(actual time=0.008..0.009 rows=1 loops=4)
Index Cond: ($0 = fiscalyear)
Total runtime: 0.138 ms
(5 rows)
it works, and check out the nifty query plan.
Regards,
Richard Broersma Jr.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
