pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem while converting sybase quries to postgres
3+ messages / 2 participants
[nested] [flat]
* problem while converting sybase quries to postgres
@ 2005-02-01 07:14 imam <imam@effigent.net>
2005-02-03 09:33 ` Re: problem while converting sybase quries to postgres Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: imam @ 2005-02-01 07:14 UTC (permalink / raw)
To: pgsql-sql
The following is a sybase query and i am trying to convet it into postgres but in postgres the
join will be specified in from clause and we cannot specify any logical operator
i would be grateful if you could tell me the solution for it
SELECT t1.SR_TRAN_HEAD_PK AS ReceiptHeaderPK,
t1.ADJ_TRAN_ID AS AdjustmentTransactionID,
t1.TOTAL_QTY_INVOICED AS TotalQtyInvoiced,
t1.TOTAL_QTY_MATCHED AS TotalQtyMatched,
t1.VENDOR_ID AS VendorID,
t1.COMMENTS AS Comments,
t1.SR_NUMBER AS ShipmentReceiptNumber,
t1.SHIPMENT_RECEIPT_DATE AS ReceiptDate,
t1.POSTING_DATE AS PostingDate,
t2.PO_HEADER_PK AS POHeaderPK,
t1.VENDOR_DOCUMENT_NUMBER AS VendorDocumentNumber,
t1.TRAN_STATUS_ID AS TransactionStatusID,
t1.SR_TOTAL AS ReceiptTotal,
t1.SR_BATCH_NUMBER AS ReceiptBatchNumber,
t1.VENDOR_PK AS VendorPK,
t1.TOTAL_QTY_RECEIVED AS TotalQtyReceived,
t1.TOTAL_QTY_REJECTED AS TotalQtyRejected,
t2.PO_NUMBER AS PONumber,
t2.BUYER_PK AS BuyerPK,
t2.BILLING_TERMS_PK AS BillingTermsPK,
t2.PO_GROUP_PK AS POGroupPK,
t2.PURCHASE_ORDER_TYPE_PK AS POTypePK,
t2.PO_DELIVERY_METHOD_PK AS PODeliveryMethodPK,
t2.CUSTOMER_PK AS CustomerPK,
t2.PO_HEADER_STATUS_PK AS POHeaderStatusPK,
t2.BUYER_ID AS BuyerID,
t2.PO_GROUP AS POGroup,
t2.PO_TYPE AS POType,
t2.PO_DATE AS PODate,
t2.PO_HEADER_STATUS AS POStatus,
t2.IS_PO_ON_HOLD AS POonHold,
t2.TRADE_DISCOUNT AS TradeDiscount,
t2.SHIPPING_CHARGES AS ShippingCharges,
t2.OTHER_CHARGES AS OtherCharges,
t2.PO_TAX AS POTax,
t2.PO_SUB_TOTAL AS POSubTotal,
t2.PO_DISCOUNT AS PODiscount,
t2.PO_TOTAL AS POTotal,
t2.PO_TOTAL_QTY AS TotalQty,
t2.RELEASED_DATE AS ReleasedDate,
t3.AMT_APPORTIONED AS AmtApportioned,
t3.MODIFIED_RECEIVED_QTY AS ModifiedReceivedQty,
t3.QTY_INVOICED AS QtyInvoiced,
t3.QTY_MATCHED AS QtyMatched,
t3.STATUS_ID AS StatusID,
t3.INV_COST AS InvoiceCost,
t3.AMT_INVOICE_APPORTIONED AS AmtInvoiceApportioned,
t3.SR_TRAN_DETAIL_PK AS ReceiptDetailPK,
t3.WAREHOUSE_PK AS WarehousePK,
t3.UNIT_COST AS UnitCost,
t3.QTY_RECEIVED AS QtyReceived,
t3.EXT_COST AS ExtendedCost,
t3.QTY_REJECTED AS QtyRejected,
t3.REASON_FOR_REJECTION AS ReasonforRejection,
t3.BILL_OF_LADING AS BillofLading,
t3.ITEM_ID AS ItemID,
t3.WAREHOUSE_ID AS WarehouseID,
t3.ITEM_PK AS ItemPK,
t3.UOM_PK AS UomPK,
t3.UOM_ID AS UomID,
t3.QTY_FOR_BASE_UOM AS BaseUOM,
t3.UNIT_COST_FOR_BASE_UOM AS UnitCostforBaseUOM,
t3.ITEM_NAME AS ItemName,
t3.ITEM_ALTERNATE_NAME AS ItemAlternateName,
t3.PO_DETAIL_PK AS PODetailPK
FROM PE_POP_SR_POSTED_TRAN_HEAD t1,
PE_POP_PO_HEADER t2,
PE_POP_SR_POSTED_TRAN_DET t3
Where t1.SR_TRAN_HEAD_PK = t3.SR_TRAN_HEAD_PK
and (t1.PO_HEADER_PK *= t2.PO_HEADER_PK or t3.PO_HEADER_PK *= t2.PO_HEADER_PK)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: problem while converting sybase quries to postgres
2005-02-01 07:14 problem while converting sybase quries to postgres imam <imam@effigent.net>
@ 2005-02-03 09:33 ` Richard Huxton <dev@archonet.com>
2005-02-03 10:52 ` Re: problem while converting sybase quries to postgres Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Huxton @ 2005-02-03 09:33 UTC (permalink / raw)
To: imam <imam@effigent.net>; +Cc: pgsql-sql
imam wrote:
> The following is a sybase query and i am trying to convet it into
> postgres but in postgres the join will be specified in from clause
> and we cannot specify any logical operator
>
> i would be grateful if you could tell me the solution for it
You don't actually say what your query is trying to do.
> SELECT t1.SR_TRAN_HEAD_PK AS ReceiptHeaderPK,
> t1.ADJ_TRAN_ID AS AdjustmentTransactionID,
...other columns...
> FROM PE_POP_SR_POSTED_TRAN_HEAD t1,
> PE_POP_PO_HEADER t2,
> PE_POP_SR_POSTED_TRAN_DET t3
> Where t1.SR_TRAN_HEAD_PK = t3.SR_TRAN_HEAD_PK
> and (t1.PO_HEADER_PK *= t2.PO_HEADER_PK or t3.PO_HEADER_PK *=
> t2.PO_HEADER_PK)
If this is a left outer join, you could do something like:
SELECT
...
FROM
pe_pop_sr_posted_tran_head t1
LEFT JOIN
pe_pop_po_header t2
ON
t1.po_header_pk = t2.po_header_pk
LEFT JOIN
pe_pop_sr_posted_tran_det t3
ON
t2.po_header_pk = t3.po_header_pk
AND t1.sr_tran_head_pk = t3.sr_tran_head_pk
But you'll want to test it because I'm not clear what your query is doing.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: problem while converting sybase quries to postgres
2005-02-01 07:14 problem while converting sybase quries to postgres imam <imam@effigent.net>
2005-02-03 09:33 ` Re: problem while converting sybase quries to postgres Richard Huxton <dev@archonet.com>
@ 2005-02-03 10:52 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2005-02-03 10:52 UTC (permalink / raw)
To: imam <imam@effigent.net>; +Cc: pgsql-sql
Please cc: the list when you reply to me - that way others can help too.
imam wrote:
>>If this is a left outer join, you could do something like:
>>SELECT
>> ...
>>FROM
>> pe_pop_sr_posted_tran_head t1
>>LEFT JOIN
>> pe_pop_po_header t2
>>ON
>> t1.po_header_pk = t2.po_header_pk
>>LEFT JOIN
>> pe_pop_sr_posted_tran_det t3
>>ON
>> t2.po_header_pk = t3.po_header_pk
>> AND t1.sr_tran_head_pk = t3.sr_tran_head_pk
>>
>>But you'll want to test it because I'm not clear what your query is doing.
>>--
>> Richard Huxton
>> Archonet Ltd
>
>
> Thanking for you reply the problem is that i have a "or" condition and left
> outer join between two table.A po_header_table is comman in both the join as
> given below
> t1.PO_HEADER_PK *= t2.PO_HEADER_PK or t3.PO_HEADER_PK *=
>>>t2.PO_HEADER_PK)
Ah! Hadn't spotted that. Would this be what you're after?
SELECT ... FROM
(
SELECT
t1.po_header_pk AS t1_phpk
t3.po_header_pk AS t3_phpk
...
FROM
t1, t3
WHERE
t1.sr_tran_head_pk = t3.sr_tran_head_pk
)
AS first
LEFT JOIN
t2
ON (
first.t1_phpk = t2.po_header_pk
OR first.t3_phpk = t2.po_header_pk
)
Not sure what happens if t1_phpk and t3_phpk both match different rows
in t2 or whether that's what you want.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-02-03 10:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-01 07:14 problem while converting sybase quries to postgres imam <imam@effigent.net>
2005-02-03 09:33 ` Richard Huxton <dev@archonet.com>
2005-02-03 10:52 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox