X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 73B128B9E42 for ; Tue, 1 Feb 2005 06:44:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 16511-06 for ; Tue, 1 Feb 2005 06:44:17 +0000 (GMT) Received: from chopin.effigent.net (effigent.net [61.11.97.207]) by svr1.postgresql.org (Postfix) with ESMTP id 53E9B8B9E2D for ; Tue, 1 Feb 2005 06:44:15 +0000 (GMT) Received: from imam ([192.168.1.19]) by chopin.effigent.net (8.12.11/8.12.11) with ESMTP id j116jRSR015110 for ; Tue, 1 Feb 2005 12:15:29 +0530 Message-ID: <003901c5082d$bac76f30$1301a8c0@eipl.com> From: "imam" To: Subject: problem while converting sybase quries to postgres Date: Tue, 1 Feb 2005 12:44:50 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0032_01C5085B.D207CD60" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=3.088 tagged_above=0 required=5 tests=HTML_20_30, HTML_MESSAGE, RCVD_IN_NJABL_DUL, UPPERCASE_50_75 X-Spam-Level: *** X-Archive-Number: 200502/54 X-Sequence-Number: 20165 This is a multi-part message in MIME format. ------=_NextPart_000_0032_01C5085B.D207CD60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable The following is a sybase query and i am trying to convet it into = postgres but in postgres the=20 join will be specified in from clause and we cannot specify any logical = operator=20 i would be grateful if you could tell me the solution for it SELECT t1.SR_TRAN_HEAD_PK AS ReceiptHeaderPK,=20 t1.ADJ_TRAN_ID AS AdjustmentTransactionID,=20 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,=20 t1.SHIPMENT_RECEIPT_DATE AS ReceiptDate,=20 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,=20 PE_POP_SR_POSTED_TRAN_DET t3 Where t1.SR_TRAN_HEAD_PK =3D t3.SR_TRAN_HEAD_PK and (t1.PO_HEADER_PK *=3D t2.PO_HEADER_PK or t3.PO_HEADER_PK *=3D = t2.PO_HEADER_PK) ------=_NextPart_000_0032_01C5085B.D207CD60 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
The following is a sybase query and i = am trying to=20 convet it into postgres but in postgres the
join will be specified in from clause = and we cannot=20 specify any logical operator
 
i would be grateful if you could tell = me the=20 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 =3D t3.SR_TRAN_HEAD_PK

and (t1.PO_HEADER_PK *=3D t2.PO_HEADER_PK or t3.PO_HEADER_PK *=3D=20 t2.PO_HEADER_PK)

 

 

------=_NextPart_000_0032_01C5085B.D207CD60--