Hi I am newbie. I need to translate the following Oracle script to SQL Server 2005 script.
I will appreciate your help and feedback.
Thanks a lot in advance.
-----------------------------------------------------------------------------------------------------------
test_reporting/here4fun@test_PROD
SET NEWPAGE NONE;
SET SPACE 0;
SET LINESIZE 181;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET UNDERLINE OFF;
SET FLUSH OFF;
SET MARKUP HTML OFF;
COLUMN AGT_CD NOPRINT;
COLUMN PROC_DT NOPRINT;
SPOOL OFF;
SET COLSEP ',';
spool d:\test.txt;
select chr(34)||'Producer'||chr(34)||','||
chr(34)||'Commission Type'||chr(34)||','||
chr(34)||'Adjustment Type'||chr(34)||','||
chr(34)||'Process Date'||chr(34)||','||
chr(34)||'Division'||chr(34)||','||
chr(34)||'Line of Business'||chr(34)||','||
chr(34)||'Policy Number'||chr(34)||','||
chr(34)||'Producer Share'||chr(34)||','||
chr(34)||'NAFYC'||chr(34)||','||
chr(34)||'Plan Code'||chr(34)||','||
chr(34)||'Policy Branch'||chr(34)||','||
chr(34)||'Product'||chr(34) ||','||
chr(34)||'Gen Date'||chr(34) "A",
'00000' "AGT_CD",
'0000/00/00' "PROC_DT"
from dual
union all
SELECT trim(chr(34)||AAC_AGT_CD||chr(34))||','||
trim(chr(34)||AAC_COMM_TYPE||chr(34))||','||
trim(chr(34)||AAC_ADJ_TYPE||chr(34))||','||
trim(chr(34)||to_char(AAC_PROC_DT,'RRRR/MM/DD')||chr(34))||','||
trim(chr(34)||AAC_FASAT_DIVISION||chr(34))||','||
trim(chr(34)||AAC_FASAT_LOB||chr(34))||','||
trim(chr(34)||AAC_POL||chr(34))||','||
trim(to_char(AAC_SHARE,'999999990.99'))||','||
trim(to_char(AAC_WEIGHTED_AFYC,'999999990.99'))||' ,'||
trim(chr(34)||AAC_PLAN_CD||chr(34))||','||
trim(chr(34)||AAC_BRCH_CD||chr(34))||','||
trim(chr(34)||AAC_FASAT_PRODUCT||chr(34))||','||
trim(chr(34)||to_char(AAC_GEN_DT,'RRRR/MM/DD')||chr(34))"A",
AAC_AGT_CD "AGT_CD",
to_char(AAC_PROC_DT,'RRRR/MM/DD') "PROC_DT"
FROM
Database.VTABLE
WHERE
(
(AAC_COMM_TYPE = 'FYC'
OR AAC_ADJ_TYPE IN ('FAC01', 'FAC02', 'FAC04', 'FAC09', 'FAC10', 'FAC35', 'FAC38', 'FAC59', 'FAC60'))
AND AAC_FASAT_DIVISION IN ('I', 'G')
AND AAC_WEIGHTED_AFYC != 0
AND ( ( AAC_PROC_DT ) > (to_date(Concat((to_number(to_char(sysdate,'YYYY') ) - 1),'-12-31'),'YYYY/mm/dd')) )
)
ORDER BY AGT_CD, PROC_DT;
spool off;
exit;