darold
commented
7 years ago You can get latest developement code using:
wget https://github.com/darold/ora2pg/archive/master.zipthen unzip and install like other release.
Closed amoltarte closed 7 years ago
darold
commented
7 years ago You can get latest developement code using:
wget https://github.com/darold/ora2pg/archive/master.zipthen unzip and install like other release.
amoltarte
commented
7 years ago Hi Darold, I'm already using the same build which you are pointing to... Please help, I'm in need.
On Wed, May 3, 2017 at 5:27 PM, Darold notifications@github.com wrote:
You can get latest developement code using:
wget https://github.com/darold/ora2pg/archive/master.zip
then unzip and install like other release.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/darold/ora2pg/issues/393#issuecomment-298890618, or mute the thread https://github.com/notifications/unsubscribe-auth/AHp2cld4Ezy7jyZay9gg7u__Vk8d8iPoks5r2GurgaJpZM4NPQ_4 .
-- With Warm Regards,
Amol Tarte. Project Lead Rajdeep InfoTechno Pvt. Ltd.
Visit Us At: http://it.rajdeepgroup.com
amoltarte
commented
7 years ago Hi! The attached file is better (at least it is creating function definition more correctly than the one which is on github) Downloaded from https://codeload.github.com/darold/ora2pg/zip/v18.1 but it is also repeating column names in views header.
Can you please suggest something for joins, as it is not writing joins in 90% cases.
On Wed, May 3, 2017 at 5:39 PM, Amol Tarte amoltarte@gmail.com wrote:
Hi Darold, I'm already using the same build which you are pointing to... Please help, I'm in need.
On Wed, May 3, 2017 at 5:27 PM, Darold notifications@github.com wrote:
You can get latest developement code using:
wget https://github.com/darold/ora2pg/archive/master.zip
then unzip and install like other release.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/darold/ora2pg/issues/393#issuecomment-298890618, or mute the thread https://github.com/notifications/unsubscribe-auth/AHp2cld4Ezy7jyZay9gg7u__Vk8d8iPoks5r2GurgaJpZM4NPQ_4 .
-- With Warm Regards,
Amol Tarte. Project Lead Rajdeep InfoTechno Pvt. Ltd.
Visit Us At: http://it.rajdeepgroup.com
-- With Warm Regards,
Amol Tarte. Project Lead Rajdeep InfoTechno Pvt. Ltd.
Visit Us At: http://it.rajdeepgroup.com
darold
commented
7 years ago I'm not able to reproduce the issue, download latest development code and give it a try.
Can you please suggest something for joins, as it is not writing joins in 90% cases.
What are you trying to explain with this sentence? If you are talking about (+) Oracle outer join, same answer as above: use latest development code.
darold
commented
7 years ago I think I have found what's wrong with your view export. I will work on it asap.
amoltarte
commented
7 years ago Thanks Darold. Please look at the joins also. If you wish, I can send you both views, Oracle and Ora2PG output. And rest assured, I m using the latest one.
https://codeload.github.com/darold/ora2pg/zip/v18.1
With Warm Regards,
Amol Tarte. Project Lead Rajdeep InfoTechno Pvt. Ltd.
Visit Us At: http://it.rajdeepgroup.com
On May 5, 2017 10:58 PM, "Darold" notifications@github.com wrote:
I think I have found what's wrong with your view export. I will work on it asap.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/darold/ora2pg/issues/393#issuecomment-299526072, or mute the thread https://github.com/notifications/unsubscribe-auth/AHp2cn2DdAOfqMsaLmABK-08aZYOyuETks5r21wxgaJpZM4NPQ_4 .
darold
commented
7 years ago Can you post your ora2pg.conf file here, at least the value of the following configuration directives SCHEMA and EXPORT_SCHEMA. Please add you ora2Pg command too.
darold
commented
7 years ago About joins can you provide an Oracle source example and the export made by Ora2Pg, this will help me to understand what you are talking about.
darold
commented
7 years ago I can not find an easy way to solve this issue, I think if you export your views schema by schema (by setting the SCHEMA directive) or enable EXPORT_SCHEMA this will solve your current export issue. Le me know.
amoltarte
commented
7 years ago Hi! Below is an example of Inline view from Oracle. Ora2PG is converting it only half and rest of the part is omitted. Please have a look and suggest.
/*** Start Oracle Def. **/ CREATE OR REPLACE FORCE VIEW TMS.V_CCHTAGRECONCILATION ( TOLLPLAZA_NAME, SHIFT_DATE, SHIFT_CODE, BOOTH_CODE, ETC_TAG_ID, ETCLANE, ETCVRN, ETCTRANSID, ENTRY_TIME, AUDITDATETIME, TRANSDATETIME, UPLOAD_COUNT, NOTUPLOAD_COUNT, DOWNLOAD_TIME, ETCTRIPID, CCHBANK_ID, CCH_VENDOR, ACCEPTED_AMOUNT, ETCDISCAMOUNT, TAG_MOP, RESPONSE_CODE, REASON_CODE, ACCEPTED_COUNT, RCSCLASS_NAME, INCHARGECLASS_NAME, INCHARGE_FAREAMOUNT, CCHPENDING_STATUS, ETCUPLOAD_DATETIME, TAGCLASS_NAME, ETCRECHARGE_CODE, ISMAINTENANCE_MODE, UPLOAD_STATUS, DOWNLOAD_STATUS, INCHARGE_COUNT, SUBCLASS_CODE, EFFECTIVE_DATE, TAG_STATUS, EPC_DATA, TID_DATA, USER_DATA, UPLOAD_FILENAME ) AS SELECT TOLLPLAZA_NAME, SHIFT_DATE, SHIFT_CODE, BOOTH_CODE, ETC_TAG_ID, ETCLANE, ETCVRN, ETCTRANSID, ENTRY_TIME, AuditDateTime, TRANSDATETIME, UPLOAD_COUNT, NOTUPLOAD_COUNT, DOWNLOAD_TIME, ETCTRIPID, CCHBANK_ID, CCH_VENDOR, ACCEPTED_AMOUNT, ETCDISCAMOUNT, TAG_MOP, RESPONSE_CODE, REASON_CODE, ACCEPTED_COUNT, RCSCLASS_NAME, INCHARGECLASS_NAME, INCHARGE_FAREAMOUNT, CCHPENDING_STATUS, ETCUPLOAD_DATETIME, TAGCLASS_NAME, ETCRECHARGE_CODE, ISMAINTENANCE_MODE, UPLOAD_STATUS, DOWNLOAD_STATUS, INCHARGE_COUNT, SUBCLASS_CODE, TO_CHAR (EFFECTIVE_DATE, 'DD/MM/YYYY HH24:MI:SS') AS EFFECTIVE_DATE, Tag_Status, EPC_Data, TID_Data, USER_DATA, Upload_FileName FROM (SELECT NVL (ETCTV.Transaction_ID, ETCTR.Transaction_ID) AS Transaction_ID, F_GetFileDownloadDateTime ( NVL (ETCTV.ETCFileDownload_ID, ETCTR.ETCFileDownload_ID)) AS Download_Time, NVL (ETCTV.CCHCustomerTrip_ID, ETCTR.CCHCustomerTrip_ID) AS ETCTripID, DECODE (NVL (ETCTV.CCHBank_ID, ETCTR.CCHBank_ID), 1, 'ICICI', 2, 'AXIS') AS CCH_Vendor, NVL (NVL (ETCTV.Accepted_Amount, ETCTR.Accepted_Amount), 0) AS Accepted_Amount, NVL (ETCTV.Discounted_Amount, ETCTR.Discounted_Amount) AS ETCDiscAmount, DECODE ( NVL (ETCTV.Discounted_Amount, ETCTR.Discounted_Amount), 0, 'SJ', 'DP') AS Tag_MOP, NVL (ETCTV.Response_Code, ETCTR.Response_Code) AS Response_Code, NVL (NVL (ETCTV.Reason_Code, ETCTR.Reason_Code), 'N/A') AS Reason_Code, DECODE (NVL (ETCTV.Response_Code, ETCTR.Response_Code), 'A', 1, 0) AS Accepted_Count, DECODE (NVL (ETCTV.Response_Code, ETCTR.Response_Code), NULL, 0, 1) AS Download_Status FROM Tbl_ETCTollReconciliation ETCTR FULL OUTER JOIN Tbl_ETCTollViolation ETCTV ON ETCTR.Transaction_ID = ETCTV.Transaction_ID FULL OUTER JOIN Tbl_ETCFileDownload ETCFD ON ETCFD.ETCFileDownload_ID = NVL (ETCTV.ETCFileDownload_ID, ETCTR.ETCFileDownload_ID) AND ( ETCFD.ETCFileDownload_ID = ETCTR.ETCFileDownload_ID OR ETCFD.ETCFileDownload_ID = ETCTV.ETCFileDownload_ID)) A, ( SELECT 'PLAZA PLAZA' AS TollPlaza_Name, VE.Shift_Date, VE.Shift_Code, VE.Booth_Code, VE.ETC_Tag_ID, VE.EPC_Data, VE.TID_Data, VE.USER_DATA, F_GetBoothName (VE.Booth_Code) AS ETCLane, VE.Vehicle_No AS ETCVRN, VE.VehicleEntry_Code AS ETCTransID, VE.Last_Update_Date AS Entry_Time, VE.Transaction_Datetime AS TransDateTime, DECODE (VE.ETCUpload_Flag, 1, 1, 0) AS Upload_Count, DECODE (VE.ETCUpload_Flag, 0, 1, 0) AS NotUpload_Count, C.Last_Update_Date AS AuditDateTime, NVL ( F_RCSClassName ( NVL (VE.AVCClass_Code, F_GetVEClass_Code (VE.VehicleModel_Code))), 'UnIdentified') AS RCSClass_Name, VE.CCHBank_ID AS CCHBank_ID, F_GetVehicleClassName (VE.Corrected_SCCode) AS InchargeClass_Name, F_GetFareAmount ( 1, NVL (VE.Corrected_SCCode, VE.SubClass_Code), VE.Last_Update_Date) AS Incharge_FareAmount, DECODE (NVL (VE.ETCUpload_Flag, 0), 0, 'No', 'Yes') AS CCHPending_Status, VE.ETCUpload_DateTime, SUBSTR (F_GetTagClass_Name (VE.Tag_Vehicle_Class), 1, 50) AS TagClass_Name, NVL (VE.ETCRecharge_Code, 0) AS ETCRecharge_Code, NVL (VE.IsMaintenance_Mode, 0) AS IsMaintenance_Mode, NVL (VE.ETCUpload_Flag, 0) AS Upload_Status, DECODE (VE.Corrected_SCCode, VE.SubClass_Code, 1, 0) AS Incharge_Count, NVL (VE.Corrected_SCCode, VE.SubClass_Code) AS SubClass_Code, F_GetCCHFileName (VE.VehicleEntry_Code, 0) AS Upload_FileName, E.Tag_Status, E.EFFECTIVE_DATE AS EFFECTIVE_DATE FROM Tbl_VehicleEntry VE, Tbl_Corrections C, Tbl_ETCBlacklist E WHERE VE.VehicleEntry_Code = C.VehicleEntry_Code(+) AND VE.ETC_Tag_ID IS NOT NULL AND NVL (VE.IsMaintenance_Mode, 0) = 0 AND NVL (VE.ETCRecharge_Code, 0) <= 0 AND E.ETC_Tag_ID(+) = VE.ETC_Tag_ID AND VE.PaymentMode_Code IN (SELECT PaymentMode_Code FROM Tbl_PaymentMode WHERE Issue_Type = 9) ORDER BY VE.Last_Update_Date) B WHERE A.Transaction_ID(+) = B.ETCTransID;
/*** End Oracle Def ****/
/** Start ORA2PG Def ***/
CREATE OR REPLACE VIEW tms.v_cchtagreconcilation AS SELECT TOLLPLAZA_NAME, SHIFT_DATE, SHIFT_CODE, BOOTH_CODE, ETC_TAG_ID, ETCLANE, ETCVRN, ETCTRANSID, ENTRY_TIME, AuditDateTime, TRANSDATETIME, UPLOAD_COUNT, NOTUPLOAD_COUNT, DOWNLOAD_TIME, ETCTRIPID, CCHBANK_ID, CCH_VENDOR, ACCEPTED_AMOUNT, ETCDISCAMOUNT, TAG_MOP, RESPONSE_CODE, REASON_CODE, ACCEPTED_COUNT, RCSCLASS_NAME, INCHARGECLASS_NAME, INCHARGE_FAREAMOUNT, CCHPENDING_STATUS, ETCUPLOAD_DATETIME, TAGCLASS_NAME, ETCRECHARGE_CODE, ISMAINTENANCE_MODE, UPLOAD_STATUS, DOWNLOAD_STATUS, INCHARGE_COUNT, SUBCLASS_CODE, TO_CHAR(EFFECTIVE_DATE,'DD/MM/YYYY HH24:MI:SS') AS EFFECTIVE_DATE, Tag_Status, EPC_Data, TID_Data, USER_DATA, Upload_FileName FROM ( SELECT coalesce(ETCTV.Transaction_ID,ETCTR.Transaction_ID) AS Transaction_ID , F_GetFileDownloadDateTime(coalesce(ETCTV.ETCFileDownload_ID,ETCTR.ETCFileDownload_ID)) AS Download_Time , coalesce(ETCTV.CCHCustomerTrip_ID,ETCTR.CCHCustomerTrip_ID) AS ETCTripID , CASE WHEN coalesce(ETCTV.CCHBank_ID,ETCTR.CCHBank_ID)=1 THEN 'ICICI' WHEN coalesce(ETCTV.CCHBank_ID,ETCTR.CCHBank_ID)=2 THEN 'AXIS' END CCH_Vendor , coalesce(coalesce(ETCTV.Accepted_Amount,ETCTR.Accepted_Amount),0) AS Accepted_Amount , coalesce(ETCTV.Discounted_Amount,ETCTR.Discounted_Amount) AS ETCDiscAmount , CASE WHEN coalesce(ETCTV.Discounted_Amount,ETCTR.Discounted_Amount)=0 THEN 'SJ' ELSE 'DP' END Tag_MOP , coalesce(ETCTV.Response_Code,ETCTR.Response_Code) AS Response_Code , coalesce(coalesce(ETCTV.Reason_Code,ETCTR.Reason_Code),'N/A') AS Reason_Code , CASE WHEN coalesce(ETCTV.Response_Code,ETCTR.Response_Code)='A' THEN 1 ELSE 0 END Accepted_Count , CASE WHEN coalesce(ETCTV.Response_Code,ETCTR.Response_Code) IS NULL THEN 0 ELSE 1 END Download_Status FROM Tbl_ETCTollReconciliation ETCTR FULL OUTER JOIN Tbl_ETCTollViolation ETCTV ON ETCTR.Transaction_ID = ETCTV.Transaction_ID FULL OUTER JOIN Tbl_ETCFileDownload ETCFD ON ETCFD.ETCFileDownload_ID = coalesce(ETCTV.ETCFileDownload_ID,ETCTR.ETCFileDownload_ID) AND ( ETCFD.ETCFileDownload_ID = ETCTR.ETCFileDownload_ID OR ETCFD.ETCFileDownload_ID = ETCTV.ETCFileDownload_ID) ) a RIGHT OUTER JOIN b ON (A.Transaction_ID = B.ETCTransID);
/** End ORA2PG Def. *****/
darold
commented
7 years ago I don't see any problem here, Ora2Pg has rewritten the Oracle outer join (+) into SQL ANSI syntax. But you don't answer to any of my previous questions.
amoltarte
commented
7 years ago Hi Darold! Thanks for your support. Please find below my ora2pg.conf
****/ Start Ora2PG.conf ***/
#################### Ora2Pg Configuration file #####################
directive.
ORACLE_HOME E:\oracle\product\11.2.0\client_1\
ORACLE_DSN dbi:Oracle:host=172.16.10.6;sid=SWDB ORACLE_USER system ORACLE_PWD SYSSWDB
USER_GRANTS 0
DEBUG 0
after
ORA_INITIAL_COMMAND
EXPORT_SCHEMA 1
CREATE_SCHEMA 1
code.
invalidate
'SESSION_USER'));
following
COMPILE_SCHEMA 0
# CTXSYS,DBSNMP,EXFSYS,LBACSYS,MDSYS,MGMT_VIEW,OLAPSYS,ORDDATA,OWBSYS, # ORDPLUGINS,ORDSYS,OUTLN,SI_INFORMTN_SCHEMA,SYS,SYSMAN,SYSTEM,WK_TEST, # WKSYS,WKPROXY,WMSYS,XDB,APEX_PUBLIC_USER,DIP,FLOWS_020100,FLOWS_030000,
# SPATIAL_WFS_ADMIN_USR,XS$NULL,PERFSTAT,SQLTXPLAIN,DMSYS,TSMSYS,WKSYS,
This
values
avoid
to
TYPE VIEW
and
DISABLE_COMMENT 0
regex
of
the
statement.
object
EXTERNAL_TO_FDW 1
TRUNCATE_TABLE 0
TABLE_INFO[NAME='test']
and
tables.
by
database.
USE_TABLESPACE 0
REORDERING_COLUMNS 0
TABLE_INFO[NAME='test']
can
JOIN EMP_UPDT u ON (e.id=u.id AND u.cdate>'2014-08-01 00:00:00')]
using
indexes
enough,
# CONTEXT_AS_TRGM 0
example:
#
triggers
before
#
#
use
# FTS_INDEX_ONLY 1
not
#
enable
#
#
#
#
#
#
#
# USE_UNACCENT 0
#
# USE_LOWER_UNACCENT 0
true,
KEEP_PKEY_NAMES 0
the
exporting
PKEY_IN_CREATE 0
FKEY_ADD_UPDATE never
FKEY_DEFERRABLE 0
DEFER_FKEY 0
directive.
DROP_FKEY 0
DISABLE_SEQUENCE 0
DISABLE_TRIGGERS 0
modifications)
directives
PRESERVE_CASE 0
name
INDEXES_RENAMING 0
bpchar_pattern_ops
character by
This
will
columns
only
than
USE_INDEX_OPCLASS 0
PREFIX_PARTITION 0
PostgreSQL
name.
partition
DISABLE_PARTITION 0
creating
WITH_OID 0
ORA_RESERVED_WORDS audit,comment
reserved
USE_RESERVED_WORDS 0
Bzip2
OUTPUT output.sql
BZIP2
export.
the
to
FILE_PER_CONSTRAINT 0
file
corresponding
extension to
into
the
FILE_PER_INDEX 0
FILE_PER_TABLE 0
FILE_PER_FUNCTION 0
through
#
#
example you
respectively use #
#
set this
information.
STOP_ON_ERROR 1
1. COPY_FREEZE 0
directive
syntax is
are the
bigint
RAW:bytea,CLOB:text,NCLOB:text,BLOB:bytea,BFILE:bytea,RAW:bytea,ROWID:oid,FLOAT:double precision,DEC:decimal,DECIMAL:decimal,DOUBLE PRECISION:double precision,INT:numeric,INTEGER:numeric,REAL:real,SMALLINT:smallint,BINARY_FLOAT:double precision,BINARY_DOUBLE:double precision,TIMESTAMP:timestamp,XMLTYPE:xml,BINARY_INTEGER:integer,PLS_INTEGER:integer,TIMESTAMP WITH TIME ZONE:timestamp with time zone,TIMESTAMP WITH LOCAL TIME ZONE:timestamp with time zone
PG_NUMERIC_TYPE 1
PG_INTEGER_TYPE 1
DEFAULT_NUMERIC bigint
HH24:MI:SS')
HH24:MI:SS.FF'. ENABLE_MICROSECOND 1
list
type
boolean.
or
Oracle
default:
NULL.
constraint.
arbitral
want
GEN_USER_PWD 0
connect
by
setting
FORCE_OWNER 0
set
DATA_LIMIT 10000
1000.
of
NOESCAPE 0
TRANSACTION serializable
Ora2Pg
causing
statements.
STANDARD_CONFORMING_STRINGS 1
default
the LOB
Truncation'
# http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm#Data_Interface_for_Persistent_LOBs #
that
blob
require
memory.
directive
use
Note
always
line
works, see # http://search.cpan.org/~pythian/DBD-Oracle-1.74/lib/DBD/Oracle.pm#Data_Interface_for_LOB_Locators
compatibility. NO_LOB_LOCATOR 1
XML_PRETTY 0
PostgreSQL
output
the
abort
LOG_ON_ERROR 0
trailing
TRIM_TYPE BOTH
need
you
comment
following
century
value.
timestamp. INTERNAL_DATE_MAX 49
JOBS 1
cores
that
DEFINED_PK. ORACLE_COPIES 1
machine.
extraction.
JOBS.
enable
PARALLEL_TABLES 1
set
time.
query.
ROUND()
DROP_INDEXES 0
written
This
file.
is
modified
this
SYNCHRONOUS_COMMIT 0
PL/SQL code
even if
functions,
EXPORT_INVALID 0
PLSQL_PGSQL 1
will
AND
NULL_EQUAL_EMPTY 0
empty
your
EMPTY_LOB_NULL 0
PACKAGE_AS_SCHEMA 1
REWRITE_OUTER_JOIN 1
functions
DATE_FUNCTION_REWRITE 1
SHOW_REPORT,
ESTIMATE_COST 0
COST_UNIT_VALUE 5
DUMP_AS_HTML 0
TOP_MAX 10
HUMAN_DAYS_LIMIT 5
available)
PG_SUPPORTS_WHEN 1
PG_SUPPORTS_INSTEADOF 1
PG_SUPPORTS_MVIEW 1
PostgreSQL
PG_SUPPORTS_CHECKOPTION 1
PG_SUPPORTS_IFEXISTS 1
created BITMAP_AS_GIN 1
PG_BACKGROUND 0
and
spatial
have
"geometry"
lines
setting
AUTODETECT_SPATIAL_TYPE 1
value:
27572; CONVERT_SRID 1
DEFAULT_SRID 4326
geometry
INTERNAL
SDO_GEOMETRY
GEOMETRY_EXTRACT_TYPE INTERNAL
used
This
Default
FDW_SERVER orcl
MYSQL_PIPES_AS_CONCAT 0
internal
MYSQL_INTERNAL_EXTRACT_FORMAT 0
/*** End Ora2PG.conf **/
I'm using both commands (The output is same for both)
ora2pg -p -c ora2pg.conf
ora2pg -c ora2pg.conf
Check the above view output "v_cchtagreconciliation" (in last conversation) It has written the half view and omitted the half only and omitted the half Check tbl_VehicleEntry table. It has omitted it entirely in the ora2pg output.
On Mon, May 8, 2017 at 2:33 PM, Darold notifications@github.com wrote:
I don't see any problem here, Ora2Pg has rewritten the Oracle outer join (+) into SQL ANSI syntax. But you don't answer to any of my previous questions.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/darold/ora2pg/issues/393#issuecomment-299812909, or mute the thread https://github.com/notifications/unsubscribe-auth/AHp2cpsB2qILi9gjHpKhpxc_09YE6T8Sks5r3tp8gaJpZM4NPQ_4 .
-- With Warm Regards,
Amol Tarte. Project Lead Rajdeep InfoTechno Pvt. Ltd.
Visit Us At: http://it.rajdeepgroup.com
amoltarte
commented
7 years ago Also see it is keeping (+) as it is in below view
/*** Oracle View def **/
CREATE OR REPLACE FORCE VIEW TMS.V_BOOTHWISESHORTEXCESS ( SHIFT_DATE, ORASHIFT_DATE, SHIFT_CODE, BOOTH_CODE, EMPLOYEE_CODE, MINISHIFT_CODE, SHIFTASSIGNMENT_CODE, CALCULATEDAMOUNT, COLLECTIONAMOUNT, DUE_AMOUNT, EXCESS_AMOUNT, SHORTEXCESS, TOTALCOLLECTION, CASHIER_NAME, TC_NAME, LOGIN_ID, SHIFT_NAME ) AS SELECT TO_CHAR (SA.Shift_Date, 'DD/MM/YYYY') AS Shift_Date, SA.Shift_Date AS ORAShift_Date, SA.Shift_Code, SA.Booth_Code, SA.Employee_Code, SA.MiniShift_Code, SA.ShiftAssignment_Code, NVL ( DECODE (SA.MiniShift_Code, NULL, SUM (SE.Calculated_Amount), EOS.Amount_By_System), 0) AS CalculatedAmount, NVL ( DECODE (SA.MiniShift_Code, NULL, NVL (SA.Amount_By_Cashier, 0), NVL (EOS.Amount_By_TC, 0)), 0) AS CollectionAmount, DECODE (SA.MiniShift_Code, NULL, NVL (SA.Due_Amount, 0), NVL (EOS.Due_Amount, 0)) AS Due_Amount, DECODE (SA.MiniShift_Code, NULL, NVL (SA.Excess_Amount, 0), NVL (EOS.Excess_Amount, 0)) AS Excess_Amount, DECODE (SA.MiniShift_Code, NULL, NVL (SA.Excess_Amount, 0), NVL (EOS.Excess_Amount, 0))
/*** Ora2PG output **/ CREATE OR REPLACE VIEW tms.r_boothwiseshortexcess AS SELECT TO_CHAR(SA.Shift_Date,'DD/MM/YYYY') AS Shift_Date , SA.Shift_Date AS ORAShift_Date , SA.Shift_Code , SA.Booth_Code , F_GetShiftName(SA.Shift_Code) AS Shift_Name , F_GetBoothName(SA.Booth_Code) AS Booth_Name , SA.Employee_Code , SA.MiniShift_Code , SA.ShiftAssignment_Code , coalesce(CASE WHEN SA.MiniShift_Code IS NULL THEN SUM(SE.Calculated_Amount) ELSE EOS.Amount_By_System END ,0) AS CalculatedAmount , coalesce(CASE WHEN SA.MiniShift_Code IS NULL THEN coalesce(SA.Amount_By_Cashier,0) ELSE coalesce(EOS.Amount_By_TC,0) END ,0) AS CollectionAmount , CASE WHEN SA.MiniShift_Code IS NULL THEN coalesce(SA.Due_Amount,0) ELSE coalesce(EOS.Due_Amount,0) END Due_Amount , CASE WHEN SA.MiniShift_Code IS NULL THEN coalesce(SA.Excess_Amount,0) ELSE coalesce(EOS.Excess_Amount,0) END Excess_Amount , CASE WHEN SA.MiniShift_Code IS NULL THEN coalesce(SA.Excess_Amount,0) ELSE coalesce(EOS.Excess_Amount,0) END - CASE WHEN SA.MiniShift_Code IS NULL THEN coalesce(SA.Due_Amount,0) ELSE coalesce(EOS.Due_Amount,0) END ShortExcess , NVL(CASE WHEN SA.MiniShift_Code IS NULL THEN SUM(SE.Calculated_Amount) ELSE EOS.Amount_By_System END + (CASE WHEN SA.MiniShift_Code IS NULL THEN coalesce(SA.Excess_Amount,0) ELSE coalesce(EOS.Excess_Amount,0) END - CASE WHEN SA.MiniShift_Code IS NULL THEN coalesce(SA.Due_Amount,0) ELSE coalesce(EOS.Due_Amount,0) END ),0) AS TotalCollection , '(' || E.Login_ID || ') ' || PK_Index.F_Title(E.Title)|| ' ' || E.First_Name || ' ' || E.Last_Name AS Cashier_Name , '(' || E.Login_ID || ') ' || E.First_Name || ' ' || E.Last_Name AS TC_Name , E.Login_ID -- , SH.Shift_Name FROM Tbl_ShiftAssignment SA , Tbl_EOSDetails EOS , Tbl_Employee E , Tbl_ShiftEnd SE -- , Tbl_Shift SH WHERE SA.ShiftAssignment_Code = EOS.ShiftAssignment_Code(+) AND SA.Employee_Code = E.Employee_Code AND SE.ShiftAssignment_Code(+) = SA.ShiftAssignment_Code -- AND SA.Shift_Code = SH.Shift_Code AND Login_Status <> 8 GROUP BY SA.Shift_Date , SA.MiniShift_Code , SA.ShiftAssignment_Code , SA.Shift_Code , SA.Booth_Code , SA.Employee_Code , SA.Excess_Amount , SA.Due_Amount , EOS.Excess_Amount , EOS.Amount_By_System , EOS.Due_Amount , SA.MiniShift_Code , '(' || E.Login_ID || ') ' || PK_Index.F_Title(E.Title)|| ' ' || E.First_Name || ' ' || E.Last_Name , '(' || E.Login_ID || ') ' || E.First_Name || ' ' || E.Last_Name , E.Login_ID -- , SH.Shift_Name , coalesce(CASE WHEN SA.MiniShift_Code IS NULL THEN coalesce(SA.Amount_By_Cashier,0) ELSE coalesce(EOS.Amount_BY_TC,0) END ,0) ORDER BY SA.Employee_Code , SA.Shift_Date , SA.Shift_Code , SA.Booth_Code;
darold
commented
7 years ago About the column named doubled I think the issue can be avoid if you are doing a per schema export. I mean that if you set SCHEMA to MEP in your ora2pg.conf configuration file the columns will not be doubled. Can you confirm that?
About outer join (+) not translated this is a current limitation of the code, when a query or subquery have both right and left outer join, there is no possible automatic translation. For example:
WHERE MT.ID_CISDRUREV = A1.ID_CISDRUREV(+)
AND MT.ID_ASPRAVCE(+) = A2.ID_ASPRAVCEAn other translation limitation is when the predicate contains OR expression:
WHERE ... AND (
A9.ID_SEGMENT= A10.ID_SEGMENT(+)
AND ( (A10.TYP = 'S') OR (A10.TYP IS NULL ) )
)Hi Darold, The column name repeating problem solved after replacing the program to https://codeload.github.com/darold/ora2pg/zip/v18.1
But please let me know if something can be done for the above case where is has left the half view written and ommitted the half part (not converted and not even written as it is, it has just left that part)
Sorry to write all the things under one ticket of "Repeating column names" but I thought I should not open a new thread until suggested from you so writing all the queries / problems under this thread.
I understood that this is the limitation that if a view has two joins in it, it can't convert it right now. but it has omitted the entire part of my view above.
On Mon, May 8, 2017 at 9:39 PM, Darold notifications@github.com wrote:
About the column named doubled I think the issue can be avoid if you are doing a per schema export. I mean that if you set SCHEMA to MEP in your ora2pg.conf configuration file the columns will not be doubled. Can you confirm that?
About outer join (+) not translated this is a current limitation of the code, when a query or subquery have both right and left outer join, there is no possible automatic translation. For example:
WHERE MT.ID_CISDRUREV = A1.ID_CISDRUREV(+) AND MT.ID_ASPRAVCE(+) = A2.ID_ASPRAVCE
An other translation limitation is when the predicate contains OR expression:
WHERE ... AND ( A9.ID_SEGMENT= A10.ID_SEGMENT(+) AND ( (A10.TYP = 'S') OR (A10.TYP IS NULL ) ) )
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/darold/ora2pg/issues/393#issuecomment-299912600, or mute the thread https://github.com/notifications/unsubscribe-auth/AHp2cvBPjFqD163qYPkDaKRzAaNhjAltks5r3z4dgaJpZM4NPQ_4 .
-- With Warm Regards,
Amol Tarte. Project Lead Rajdeep InfoTechno Pvt. Ltd.
Visit Us At: http://it.rajdeepgroup.com
darold
commented
7 years ago Can you point me the code that is omitted? Outside the unconverted outer join part I do not see waht is missing.
amoltarte
commented
7 years ago Hi Darold, Please see above Oracle definition and Ora2PG output for tms.v_cchtagreconcilation You can see it has completely completely omitted table tbl_VehicleEntry in the output.
Left the part from Oracle definition ( SELECT 'PLAZA PLAZA' AS TollPlaza_Name, VE.Shift_Date, VE.Shift_Code, VE.Booth_Code, VE.ETC_Tag_ID, VE.EPC_Data, VE.TID_Data, VE.USER_DATA, ...
darold
commented
7 years ago With current code I'm not able to reproduce the issue. Here is the result of the translation:
CREATE OR REPLACE VIEW tms.v_cchtagreconcilation (tollplaza_name, shift_date, shift_code, booth_code, etc_tag_id, etclane, etcvrn, etctransid, entry_time, auditdatetime, transdatetime, upload_count, notupload_count, download_time, etctripid, cchbank_id, cch_vendor, accepted_amount, etcdiscamount, tag_mop, response_code, reason_code, accepted_count, rcsclass_name, inchargeclass_name, incharge_fareamount, cchpending_status, etcupload_datetime, tagclass_name, etcrecharge_code, ismaintenance_mode, upload_status, download_status, incharge_count, subclass_code, effective_date, tag_status, epc_data, tid_data, user_data, upload_filename) AS SELECT TOLLPLAZA_NAME,
SHIFT_DATE,
SHIFT_CODE,
BOOTH_CODE,
ETC_TAG_ID,
ETCLANE,
ETCVRN,
ETCTRANSID,
ENTRY_TIME,
AuditDateTime,
TRANSDATETIME,
UPLOAD_COUNT,
NOTUPLOAD_COUNT,
DOWNLOAD_TIME,
ETCTRIPID,
CCHBANK_ID,
CCH_VENDOR,
ACCEPTED_AMOUNT,
ETCDISCAMOUNT,
TAG_MOP,
RESPONSE_CODE,
REASON_CODE,
ACCEPTED_COUNT,
RCSCLASS_NAME,
INCHARGECLASS_NAME,
INCHARGE_FAREAMOUNT,
CCHPENDING_STATUS,
ETCUPLOAD_DATETIME,
TAGCLASS_NAME,
ETCRECHARGE_CODE,
ISMAINTENANCE_MODE,
UPLOAD_STATUS,
DOWNLOAD_STATUS,
INCHARGE_COUNT,
SUBCLASS_CODE,
TO_CHAR(EFFECTIVE_DATE, 'DD/MM/YYYY HH24:MI:SS')
AS EFFECTIVE_DATE,
Tag_Status,
EPC_Data,
TID_Data,
USER_DATA,
Upload_FileName
FROM (SELECT coalesce(ETCTV.Transaction_ID, ETCTR.Transaction_ID)
AS Transaction_ID,
F_GetFileDownloadDateTime(
coalesce(ETCTV.ETCFileDownload_ID,
ETCTR.ETCFileDownload_ID))
AS Download_Time,
coalesce(ETCTV.CCHCustomerTrip_ID, ETCTR.CCHCustomerTrip_ID)
AS ETCTripID,
CASE WHEN coalesce(ETCTV.CCHBank_ID, ETCTR.CCHBank_ID)=1 THEN 'ICICI' WHEN coalesce(ETCTV.CCHBank_ID, ETCTR.CCHBank_ID)=2 THEN 'AXIS' END
AS CCH_Vendor,
coalesce(coalesce(ETCTV.Accepted_Amount, ETCTR.Accepted_Amount),
0)
AS Accepted_Amount,
coalesce(ETCTV.Discounted_Amount, ETCTR.Discounted_Amount)
AS ETCDiscAmount,
CASE WHEN coalesce(ETCTV.Discounted_Amount, ETCTR.Discounted_Amount)=0 THEN 'SJ' ELSE 'DP' END
AS Tag_MOP,
coalesce(ETCTV.Response_Code, ETCTR.Response_Code)
AS Response_Code,
coalesce(coalesce(ETCTV.Reason_Code, ETCTR.Reason_Code), 'N/A')
AS Reason_Code,
CASE WHEN coalesce(ETCTV.Response_Code, ETCTR.Response_Code)='A' THEN 1 ELSE 0 END
AS Accepted_Count,
CASE WHEN coalesce(ETCTV.Response_Code, ETCTR.Response_Code) IS NULL THEN 0 ELSE 1 END
AS Download_Status
FROM Tbl_ETCTollReconciliation ETCTR
FULL OUTER JOIN Tbl_ETCTollViolation ETCTV
ON ETCTR.Transaction_ID = ETCTV.Transaction_ID
FULL OUTER JOIN Tbl_ETCFileDownload ETCFD
ON ETCFD.ETCFileDownload_ID =
coalesce(ETCTV.ETCFileDownload_ID,
ETCTR.ETCFileDownload_ID)
AND ( ETCFD.ETCFileDownload_ID =
ETCTR.ETCFileDownload_ID
OR ETCFD.ETCFileDownload_ID =
ETCTV.ETCFileDownload_ID) alias17) a
RIGHT OUTER JOIN ( SELECT 'PLAZA PLAZA' AS TollPlaza_Name,
VE.Shift_Date,
VE.Shift_Code,
VE.Booth_Code,
VE.ETC_Tag_ID,
VE.EPC_Data,
VE.TID_Data,
VE.USER_DATA,
F_GetBoothName(VE.Booth_Code) AS ETCLane,
VE.Vehicle_No AS ETCVRN,
VE.VehicleEntry_Code AS ETCTransID,
VE.Last_Update_Date AS Entry_Time,
VE.Transaction_Datetime AS TransDateTime,
CASE WHEN VE.ETCUpload_Flag=1 THEN 1 ELSE 0 END AS Upload_Count,
CASE WHEN VE.ETCUpload_Flag=0 THEN 1 ELSE 0 END AS NotUpload_Count,
C.Last_Update_Date AS AuditDateTime,
coalesce(
F_RCSClassName(
coalesce(VE.AVCClass_Code,
F_GetVEClass_Code(VE.VehicleModel_Code))),
'UnIdentified')
AS RCSClass_Name,
VE.CCHBank_ID AS CCHBank_ID,
F_GetVehicleClassName(VE.Corrected_SCCode)
AS InchargeClass_Name,
F_GetFareAmount(
1,
coalesce(VE.Corrected_SCCode, VE.SubClass_Code),
VE.Last_Update_Date)
AS Incharge_FareAmount,
CASE WHEN coalesce(VE.ETCUpload_Flag, 0)=0 THEN 'No' ELSE 'Yes' END
AS CCHPending_Status,
VE.ETCUpload_DateTime,
SUBSTR(F_GetTagClass_Name(VE.Tag_Vehicle_Class), 1, 50)
AS TagClass_Name,
coalesce(VE.ETCRecharge_Code, 0) AS ETCRecharge_Code,
coalesce(VE.IsMaintenance_Mode, 0) AS IsMaintenance_Mode,
coalesce(VE.ETCUpload_Flag, 0) AS Upload_Status,
CASE WHEN VE.Corrected_SCCode=VE.SubClass_Code THEN 1 ELSE 0 END
AS Incharge_Count,
coalesce(VE.Corrected_SCCode, VE.SubClass_Code)
AS SubClass_Code,
F_GetCCHFileName(VE.VehicleEntry_Code, 0)
AS Upload_FileName,
E.Tag_Status,
E.EFFECTIVE_DATE AS EFFECTIVE_DATE
FROM Tbl_VehicleEntry VE, Tbl_Corrections C, Tbl_ETCBlacklist E
WHERE VE.VehicleEntry_Code = C.VehicleEntry_Code(+)
AND VE.ETC_Tag_ID IS NOT NULL
AND coalesce(VE.IsMaintenance_Mode, 0) = 0
AND coalesce(VE.ETCRecharge_Code, 0) <= 0
AND E.ETC_Tag_ID(+) = VE.ETC_Tag_ID
AND VE.PaymentMode_Code IN (SELECT PaymentMode_Code
FROM Tbl_PaymentMode
WHERE Issue_Type = 9)
ORDER BY VE.Last_Update_Date) b ON (A.Transaction_ID = B.ETCTransID);
Hi! I've just downloaded again Ora2PG 18.1 from github. Still it is repeating the column names in CREATE VIEW
/* Oracle definition ***/ DROP VIEW MEP.V_SCHEDULEM;
/ Formatted on 5/3/2017 3:26:23 PM (QP5 v5.215.12089.38647) / CREATE OR REPLACE FORCE VIEW MEP.V_SCHEDULEM ( FORMATTED_CURRENT_MONTH, FORMATTED_PRECEDING_MONTH, FORMATTED_CORRESPONDING_MONTH, CURRENTMONTH, CURRENTYEAR, CORRESPONDING_PREVIOUS_MONTH, PRECEDING_MONTH, SUBCLASS_CODE, SUBCLASS_NAME, PAYMENTMODE_CODE, PAYMENTMODE_NAME, CURRENT_MONTH, MOP_CODE_TO_DISPLAY, SUBPAYMENTMODE_CODE, SUBPAYMENTMODE_NAME, SUBCLASS_RATE, CURRENT_MONTH_TRAFFICCOUNT, CURRENT_MONTH_COLLECTION, PRECEDING_MONTH_TRAFFICCOUNT, PRECEDING_MONTH_COLLECTION, CORRESPONDING_TRAFFICCOUNT, CORRESPONDING_COLLECTION, CURRENTWEIGHT_AMOUNT, CORRESPONDWEIGHT_AMOUNT, PRECEDINGWEIGHT_AMOUNT ) AS SELECT TO_CHAR (TO_DATE (Current_Month, 'MMYYYY'), 'MON YYYY') AS Formatted_Current_Month, TO_CHAR (TO_DATE (Preceding_Month, 'MMYYYY'), 'MON YYYY') AS Formatted_Preceding_Month, TO_CHAR (TO_DATE (Corresponding_Previous_Month, 'MMYYYY'), 'MON YYYY') AS Formatted_Corresponding_Month, TO_CHAR (TO_DATE (Current_Month, 'MMYYYY'), 'MM') AS CurrentMonth, TO_CHAR (TO_DATE (Current_Month, 'MMYYYY'), 'YYYY') AS CurrentYear, Corresponding_Previous_Month, Preceding_Month, VCD.Subclass_Code, SubClass_Name, PM.PaymentMode_Code, PaymentMode_Name, Current_Month, MOP_Code_To_Display, NVL (R.SubPaymentMode_Code, 0) AS SubPaymentMode_Code, PMD.SubPaymentMode_Name, DECODE (PM.Issue_Type, 0, F_GetFareAmount (PM.PaymentMode_Code, VCD.SubClass_Code, TO_DATE (Current_Month, 'MMYYYY'), R.SubPaymentMode_Code), 0) AS SubClass_Rate, SUM (NVL (Current_Month_TrafficCount, 0)) AS Current_Month_TrafficCount, SUM (NVL (Current_Month_Collection, 0)) AS Current_Month_Collection, SUM (NVL (Preceding_Month_TrafficCount, 0)) AS Preceding_Month_TrafficCount, SUM (NVL (Preceding_Month_Collection, 0)) AS Preceding_Month_Collection, SUM (NVL (Corresponding_TrafficCount, 0)) AS Corresponding_TrafficCount, SUM (NVL (Corresponding_Collection, 0)) AS Corresponding_Collection, SUM (NVL (CurrentWeight_Amount, 0)) AS CurrentWeight_Amount, SUM (NVL (CorrespondWeight_Amount, 0)) AS CorrespondWeight_Amount, SUM (NVL (PrecedingWeight_Amount, 0)) AS PrecedingWeight_Amount FROM Rpt_ScheduleM R, Tbl_VehicleClass_Detail VCD, Tbl_PaymentMode PM, Tbl_PaymentMode_Detail PMD WHERE R.Subclass_Code = VCD.Subclass_Code AND R.PaymentMode_Code = PM.PaymentMode_Code AND R.SubPaymentMode_code = PMD.SubPaymentMode_code(+) GROUP BY PMD.SubPaymentMode_Name, TO_CHAR (TO_DATE (Current_Month, 'MMYYYY'), 'MON YYYY'), TO_CHAR (TO_DATE (Preceding_Month, 'MMYYYY'), 'MON YYYY'), TO_CHAR (TO_DATE (Corresponding_Previous_Month, 'MMYYYY'), 'MON YYYY'), TO_CHAR (TO_DATE (Current_Month, 'MMYYYY'), 'MM'), TO_CHAR (TO_DATE (Current_Month, 'MMYYYY'), 'YYYY'), Corresponding_Previous_Month, Preceding_Month, VCD.SubClass_Code, SubClass_Name, PM.PaymentMode_Code, PMD.SubPaymentmode_Name, PaymentMode_Name, Current_Month, MOP_Code_To_Display, NVL (R.SubPaymentMode_Code, 0), DECODE (PM.Issue_Type, 0, F_GetFareAmount (PM.PaymentMode_Code, VCD.SubClass_Code, TO_DATE (Current_Month, 'MMYYYY'), R.SubPaymentMode_Code), 0);
DROP SYNONYM TMS.V_SCHEDULEM;
CREATE OR REPLACE SYNONYM TMS.V_SCHEDULEM FOR MEP.V_SCHEDULEM;
GRANT SELECT ON MEP.V_SCHEDULEM TO TMS; /**** End Oracle Definition *****/
Now see ORA2PG output..
CREATE OR REPLACE VIEW v_schedulem (formatted_current_month, formatted_current_month, formatted_current_month, formatted_current_month, formatted_current_month, formatted_current_month, formatted_current_month, formatted_current_month, formatted_current_month, formatted_current_month, formatted_current_month, formatted_current_month, formatted_preceding_month, formatted_preceding_month, formatted_preceding_month, formatted_preceding_month, formatted_preceding_month, formatted_preceding_month, formatted_preceding_month, formatted_preceding_month, formatted_preceding_month, formatted_preceding_month, formatted_preceding_month, formatted_preceding_month, formatted_corresponding_month, formatted_corresponding_month, formatted_corresponding_month, formatted_corresponding_month, formatted_corresponding_month, formatted_corresponding_month, formatted_corresponding_month, formatted_corresponding_month, formatted_corresponding_month, formatted_corresponding_month, formatted_corresponding_month, formatted_corresponding_month, currentmonth, currentmonth, currentmonth, currentmonth, currentmonth, currentmonth, currentmonth, currentmonth, currentmonth, currentmonth, currentmonth, currentmonth, currentyear, currentyear, currentyear, currentyear, currentyear, currentyear, currentyear, currentyear, currentyear, currentyear, currentyear, currentyear, corresponding_previous_month, corresponding_previous_month, corresponding_previous_month, subclass_code, corresponding_previous_month, corresponding_previous_month, corresponding_previous_month, corresponding_previous_month, corresponding_previous_month, corresponding_previous_month, corresponding_previous_month, corresponding_previous_month, preceding_month, preceding_month, preceding_month, subclass_name, preceding_month, preceding_month, preceding_month, preceding_month, preceding_month, preceding_month, preceding_month, preceding_month, subclass_code, subclass_code, subclass_code, paymentmode_code, subclass_code, subclass_code, subclass_code, subclass_code, subclass_code, subclass_code, subclass_code, subclass_code, subclass_name, subclass_name, subclass_name, paymentmode_name, subclass_name, subclass_name, subclass_name, subclass_name, subclass_name, subclass_name, subclass_name, subclass_name, paymentmode_code, paymentmode_code, paymentmode_code, current_month, paymentmode_code, paymentmode_code, paymentmode_code, paymentmode_code, paymentmode_code, paymentmode_code, paymentmode_code, paymentmode_code, paymentmode_name, paymentmode_name, paymentmode_name, current_month_trafficcount, paymentmode_name, paymentmode_name, paymentmode_name, paymentmode_name, paymentmode_name, paymentmode_name, paymentmode_name, paymentmode_name, current_month, current_month, current_month, current_month_collection, current_month, current_month, current_month, current_month, current_month, current_month, current_month, current_month, mop_code_to_display, mop_code_to_display, mop_code_to_display, preceding_month, mop_code_to_display, mop_code_to_display, mop_code_to_display, mop_code_to_display, mop_code_to_display, mop_code_to_display, mop_code_to_display, mop_code_to_display, subpaymentmode_code, subpaymentmode_code, subpaymentmode_code, preceding_month_trafficcount, subpaymentmode_code, subpaymentmode_code, subpaymentmode_code, subpaymentmode_code, subpaymentmode_code, subpaymentmode_code, subpaymentmode_code, subpaymentmode_code, subpaymentmode_name, subpaymentmode_name, subpaymentmode_name, preceding_month_collection, subpaymentmode_name, subpaymentmode_name, subpaymentmode_name, subpaymentmode_name, subpaymentmode_name, subpaymentmode_name, subpaymentmode_name, subpaymentmode_name, subclass_rate, subclass_rate, subclass_rate, corresponding_previous_month, subclass_rate, subclass_rate, subclass_rate, subclass_rate, subclass_rate, subclass_rate, subclass_rate, subclass_rate, current_month_trafficcount, current_month_trafficcount, current_month_trafficcount, corresponding_trafficcount, current_month_trafficcount, current_month_trafficcount, current_month_trafficcount, current_month_trafficcount, current_month_trafficcount, current_month_trafficcount, current_month_trafficcount, current_month_trafficcount, current_month_collection, current_month_collection, current_month_collection, corresponding_collection, current_month_collection, current_month_collection, current_month_collection, current_month_collection, current_month_collection, current_month_collection, current_month_collection, current_month_collection, preceding_month_trafficcount, preceding_month_trafficcount, preceding_month_trafficcount, mop_code_to_display, preceding_month_trafficcount, preceding_month_trafficcount, preceding_month_trafficcount, preceding_month_trafficcount, preceding_month_trafficcount, preceding_month_trafficcount, preceding_month_trafficcount, preceding_month_trafficcount, preceding_month_collection, preceding_month_collection, preceding_month_collection, subclass_rate, preceding_month_collection, preceding_month_collection, preceding_month_collection, preceding_month_collection, preceding_month_collection, preceding_month_collection, preceding_month_collection, preceding_month_collection, corresponding_trafficcount, corresponding_trafficcount, corresponding_trafficcount, corresponding_trafficcount, corresponding_trafficcount, corresponding_trafficcount, corresponding_trafficcount, corresponding_trafficcount, corresponding_trafficcount, corresponding_trafficcount, corresponding_trafficcount, corresponding_collection, corresponding_collection, corresponding_collection, corresponding_collection, corresponding_collection, corresponding_collection, corresponding_collection, corresponding_collection, corresponding_collection, corresponding_collection, corresponding_collection, currentweight_amount, currentweight_amount, currentweight_amount, currentweight_amount, currentweight_amount, currentweight_amount, currentweight_amount, currentweight_amount, currentweight_amount, currentweight_amount, currentweight_amount, correspondweight_amount, correspondweight_amount, correspondweight_amount, correspondweight_amount, correspondweight_amount, correspondweight_amount, correspondweight_amount, correspondweight_amount, correspondweight_amount, correspondweight_amount, correspondweight_amount, precedingweight_amount, precedingweight_amount, precedingweight_amount, precedingweight_amount, precedingweight_amount, precedingweight_amount, precedingweight_amount, precedingweight_amount, precedingweight_amount, precedingweight_amount, precedingweight_amount) AS SELECT TO_CHAR(TO_DATE(Current_Month,'MMYYYY'),'MON YYYY') AS Formatted_Current_Month , TO_CHAR(TO_DATE(Preceding_Month,'MMYYYY'),'MON YYYY') AS Formatted_Preceding_Month , TO_CHAR(TO_DATE(Corresponding_Previous_Month,'MMYYYY'),'MON YYYY') AS Formatted_Corresponding_Month , TO_CHAR(TO_DATE(Current_Month,'MMYYYY'),'MM') AS CurrentMonth , TO_CHAR(TO_DATE(Current_Month,'MMYYYY'),'YYYY') AS CurrentYear , Corresponding_Previous_Month , Preceding_Month , VCD.Subclass_Code , SubClass_Name , PM.PaymentMode_Code , PaymentMode_Name , Current_Month , MOP_Code_To_Display , coalesce(R.SubPaymentMode_Code,0) AS SubPaymentMode_Code , PMD.SubPaymentMode_Name , CASE WHEN PM.Issue_Type=0 THEN F_GetFareAmount(PM.PaymentMode_Code,VCD.SubClass_Code,TO_DATE(Current_Month,'MMYYYY'),R.SubPaymentMode_Code) ELSE 0 END AS SubClass_Rate , SUM(coalesce(Current_Month_TrafficCount,0)) AS Current_Month_TrafficCount , SUM(coalesce(Current_Month_Collection,0)) AS Current_Month_Collection , SUM(coalesce(Preceding_Month_TrafficCount,0)) AS Preceding_Month_TrafficCount , SUM(coalesce(Preceding_Month_Collection,0)) AS Preceding_Month_Collection , SUM(coalesce(Corresponding_TrafficCount,0)) AS Corresponding_TrafficCount , SUM(coalesce(Corresponding_Collection,0)) AS Corresponding_Collection , SUM(coalesce(CurrentWeight_Amount,0)) AS CurrentWeight_Amount , SUM(coalesce(CorrespondWeight_Amount,0)) AS CorrespondWeight_Amount , SUM(coalesce(PrecedingWeight_Amount,0)) AS PrecedingWeight_Amount FROM tbl_paymentmode pm, tbl_vehicleclass_detail vcd, rpt_schedulem r LEFT OUTER JOIN tbl_paymentmode_detail pmd ON (R.SubPaymentMode_code = PMD.SubPaymentMode_code) WHERE R.Subclass_Code = VCD.Subclass_Code AND R.PaymentMode_Code = PM.PaymentMode_Code GROUP BY PMD.SubPaymentMode_Name , TO_CHAR(TO_DATE(Current_Month,'MMYYYY'),'MON YYYY') , TO_CHAR(TO_DATE(Preceding_Month,'MMYYYY'),'MON YYYY') , TO_CHAR(TO_DATE(Corresponding_Previous_Month,'MMYYYY'),'MON YYYY') , TO_CHAR(TO_DATE(Current_Month,'MMYYYY'),'MM') , TO_CHAR(TO_DATE(Current_Month,'MMYYYY'),'YYYY') , Corresponding_Previous_Month , Preceding_Month , VCD.SubClass_Code , SubClass_Name , PM.PaymentMode_Code , PMD.SubPaymentmode_Name , PaymentMode_Name , Current_Month , MOP_Code_To_Display , coalesce(R.SubPaymentMode_Code,0) , CASE WHEN PM.Issue_Type=0 THEN F_GetFareAmount(PM.PaymentMode_Code,VCD.SubClass_Code,TO_DATE(Current_Month,'MMYYYY'),R.SubPaymentMode_Code) ELSE 0 END;
/* End ORA2PG Output *****/
Also it is NOT writing joins, at many places it is keeping (+) as it is. Please help. I'm on Windows 7 and have installed StrawberryPerl, ActivePerl etc.