March 22, 2006 at 11:59 am
I have a Sybase script SPR_DWI_MKT_BRTMOVR that I need to convert to SQL Server 2000, this is the script in Sybase. Any help would greatly be appreciated...Thank you
Mac
IF
OBJECT_ID('SPR_DWI_MKT_BRTMOVR') IS NOT NULL DROP PROCEDURE SPR_DWI_MKT_BRTMOVR
GO
CREATE
PROCEDURE SPR_DWI_MKT_BRTMOVR
@AD_BEGIN_DATE
DATETIME,
@AD_END_DATE
DATETIME
AS
/
/************************************ PART 1 NEW WO INFO *******************************************//
SELECT
CNT_REGION_CODE REG,
WRK_WO_NUMBER NEW_WO_NUM,
WRK_PROCESS_DATE WO_CLOSED,
STRING(SUBSTR(SBT_SUBSCRIBER_NUMBER,
5,2),'00') FM_PRIN,
SUBSTR(SBT_SUBSCRIBER_NUMBER,
5,12) OLD_SUB_NUM,
STRING(SUBSTR(WRK_SUBSCRIBER_NUMBER,
5,2),'00') TO_PRIN,
SUBSTR(WRK_SUBSCRIBER_NUMBER,
5,12) NEW_SUB_NUM,
MAX(CASE WHEN SRM_BASIC_SERVICE+SRM_TIER_SERVICE>0 THEN 1 ELSE 0 END) NBAS,
MAX(CASE WHEN SRM_DIGITAL_SERVICE+SRM_DIGITAL_BOX+SRM_HDTV_SERVICE>0 THEN 1 ELSE 0 END) NDIG,
MAX(CASE WHEN SRM_RDRNNR_SERVICE+SRM_ALT_ISP_SERVICE>0 THEN 1 ELSE 0 END) NHSD,
MAX(CASE WHEN SRM_DIGITAL_PHONE_SERVICE>0 THEN 1 ELSE 0 END) NPHN,
MAX(CASE WHEN WRS_SERVICE_CODE IN ('E0','E8','G6','J2','J3','J4','J5','L0','L1','K1','K2','K7','D8') THEN 1 ELSE 0 END) NCOMBO,
MAX(CASE WHEN WRS_SERVICE_CODE IN ('K3','K4','K5','K6','K8','K9','J8','J9','K0','K6') THEN 1 ELSE 0 END) NCOMBOP
INTO
#TEMP_BRT1
FROM
WORKORDER, SUBSCRIBER_TRANSFER, WORKORDER_SERVICE, SERVICE_MATRIX, COUNTY
WHERE
WRK_SYS_PRIN_ID LIKE '82231%'
AND
WRK_WO_TYPE IN ('NC','RC','RS')
AND
WRK_WO_REASONS LIKE 'RS%'
AND
WRK_STATUS_CODE = 'C'
AND
WRK_PROCESS_DATE >= @AD_BEGIN_DATE
AND
WRK_PROCESS_DATE < DATEADD(DD, 1, @AD_END_DATE)
AND
WRK_SUBSCRIBER_NUMBER = SBT_TRANSFER_ID
AND
WRK_SYS_PRIN_ID = CNT_SYS_PRIN_ID
AND
WRK_WO_NUMBER = WRS_WO_NUMBER
AND
WRS_SYS_PRIN_ID = SRM_SYS_PRIN_ID
AND
WRS_AGENT_ID = SRM_AGENT_ID
AND
WRS_SERVICE_CODE = SRM_SERVICE_CODE
GROUP
BY REG, NEW_WO_NUM, WO_CLOSED, FM_PRIN, OLD_SUB_NUM, TO_PRIN, NEW_SUB_NUM
ORDER
BY 1,2
/
/************************************ PART 2 LAST VD *******************************************//
SELECT
SUBSTR(WRK_SUBSCRIBER_NUMBER,5,12) DISCO_SUB_NUM,
MAX(WRK_WO_NUMBER) DISCO_WO_NUM
INTO
#TEMP_BRT2
FROM
WORKORDER
WHERE
WRK_SYS_PRIN_ID LIKE '82231%'
AND
WRK_WO_TYPE IN ('VD')
AND
WRK_WO_REASONS LIKE 'RS%'
AND
WRK_STATUS_CODE = 'C'
AND
WRK_PROCESS_DATE > DATEADD(DD, -30, @AD_BEGIN_DATE)
GROUP
BY DISCO_SUB_NUM
ORDER
BY 1
/
/************************************ PART 3 OLD WO INFO *******************************************//
SELECT
DISCO_WO_NUM,
DISCO_SUB_NUM,
MAX(CASE WHEN SRM_BASIC_SERVICE+SRM_TIER_SERVICE>0 THEN 1 ELSE 0 END) OBAS,
MAX(CASE WHEN SRM_DIGITAL_SERVICE+SRM_DIGITAL_BOX+SRM_HDTV_SERVICE>0 THEN 1 ELSE 0 END) ODIG,
MAX(CASE WHEN SRM_RDRNNR_SERVICE+SRM_ALT_ISP_SERVICE>0 THEN 1 ELSE 0 END) OHSD,
MAX(CASE WHEN SRM_DIGITAL_PHONE_SERVICE>0 THEN 1 ELSE 0 END) OPHN,
MAX(CASE WHEN WRS_SERVICE_CODE IN ('E0','E8','G6','J2','J3','J4','J5','L0','L1','K1','K2','K7','D8') THEN 1 ELSE 0 END) OCOMBO,
MAX(CASE WHEN WRS_SERVICE_CODE IN ('K3','K4','K5','K6','K8','K9','J8','J9','K0','K6') THEN 1 ELSE 0 END) OCOMBOP
INTO
#TEMP_BRT3
FROM
#TEMP_BRT2, WORKORDER, WORKORDER_SERVICE, SERVICE_MATRIX
WHERE
DISCO_WO_NUM = WRK_WO_NUMBER
AND
WRK_WO_NUMBER = WRS_WO_NUMBER
AND
WRS_SYS_PRIN_ID = SRM_SYS_PRIN_ID
AND
WRS_AGENT_ID = SRM_AGENT_ID
AND
WRS_SERVICE_CODE = SRM_SERVICE_CODE
GROUP
BY DISCO_WO_NUM, DISCO_SUB_NUM
ORDER
BY 1,2
/
/************************************ PART 4 COMBINING *******************************************//
SELECT
REG,
FM_PRIN,
OLD_SUB_NUM,
TO_PRIN,
NEW_SUB_NUM,
WO_CLOSED,
CASE WHEN OBAS>0 AND ODIG=0 AND OHSD=0 AND OPHN=0 THEN 'ANL'
WHEN OBAS>=0 AND ODIG>0 AND OHSD=0 AND OPHN=0 THEN 'DIG'
WHEN OBAS>=0 AND ODIG>0 AND OHSD>0 AND OPHN=0 THEN 'DIG-HSD'
WHEN OBAS>=0 AND ODIG>0 AND OHSD>0 AND OPHN>0 THEN 'DIG-HSD-PHN'
WHEN OBAS=0 AND ODIG=0 AND OHSD>0 AND OPHN=0 THEN 'HSD'
WHEN OBAS>=0 AND ODIG=0 AND OHSD>0 AND OPHN=0 THEN 'ANL-HSD'
WHEN OBAS=0 AND ODIG=0 AND OHSD=0 AND OPHN>0 THEN 'PHN'
WHEN OBAS>=0 AND ODIG>0 AND OHSD=0 AND OPHN>0 THEN 'DIG-PHN'
WHEN OBAS>0 AND ODIG=0 AND OHSD>0 AND OPHN>0 THEN 'ANL-HSD-PHN'
WHEN OBAS>0 AND ODIG=0 AND OHSD=0 AND OPHN>0 THEN 'ANL-PHN'
WHEN OBAS=0 AND ODIG=0 AND OHSD>0 AND OPHN>0 THEN 'HSD-PHN'
ELSE '???' END OLD_SERVICE,
CASE WHEN NBAS>0 AND NDIG=0 AND NHSD=0 AND NPHN=0 THEN 'ANL'
WHEN NBAS>=0 AND NDIG>0 AND NHSD=0 AND NPHN=0 THEN 'DIG'
WHEN NBAS>=0 AND NDIG>0 AND NHSD>0 AND NPHN=0 THEN 'DIG-HSD'
WHEN NBAS>=0 AND NDIG>0 AND NHSD>0 AND NPHN>0 THEN 'DIG-HSD-PHN'
WHEN NBAS=0 AND NDIG=0 AND NHSD>0 AND NPHN=0 THEN 'HSD'
WHEN NBAS>=0 AND NDIG=0 AND NHSD>0 AND NPHN=0 THEN 'ANL-HSD'
WHEN NBAS=0 AND NDIG=0 AND NHSD=0 AND NPHN>0 THEN 'PHN'
WHEN NBAS>=0 AND NDIG>0 AND NHSD=0 AND NPHN>0 THEN 'DIG-PHN'
WHEN NBAS>0 AND NDIG=0 AND NHSD>0 AND NPHN>0 THEN 'ANL-HSD-PHN'
WHEN NBAS>0 AND NDIG=0 AND NHSD=0 AND NPHN>0 THEN 'ANL-PHN'
WHEN NBAS=0 AND NDIG=0 AND NHSD>0 AND NPHN>0 THEN 'HSD-PHN'
ELSE '???' END NEW_SERVICE,
CASE WHEN OCOMBO=0 AND OCOMBOP = 0 AND NCOMBO>0 THEN 1
WHEN OCOMBO = NCOMBO THEN 0
WHEN OCOMBOP>0 AND NCOMBOP=0 THEN 0
ELSE 0 END 'UPG_DIG_CMBO',
CASE WHEN OCOMBOP=0 AND NCOMBOP>0 THEN 1
WHEN OCOMBOP = NCOMBOP THEN 0
WHEN OCOMBOP>0 AND NCOMBOP=0 THEN 0
ELSE 0 END 'UPG_DIG_CMBO_PLUS',
CASE WHEN SAC_EMAIL_ADDRESS IS NULL THEN ' '
WHEN SAC_SOLICIT_FLAG = 'N' THEN 'N'
ELSE SAC_EMAIL_ADDRESS END 'EMAIL_ADDRESS'
FROM
#TEMP_BRT1, #TEMP_BRT3, SUBSCRIBER_ACCESS
WHERE
NEW_SUB_NUM *= SUBSTR(SAC_SUBSCRIBER_NUMBER,5,12)
AND
SAC_PRIMARY_FLAG = 'Y'
AND
OLD_SUB_NUM = DISCO_SUB_NUM
ORDER
BY 1,2,4,5
RETURN
0
GO
GRANT
EXECUTE ON SPR_DWI_MKT_BRTMOVR TO public
GO
March 22, 2006 at 2:20 pm
I do believe the only things you have to change to work are the following two lines
STRING(SUBSTR(SBT_SUBSCRIBER_NUMBER,5,2),'00') FM_PRIN,
and
STRING(SUBSTR(WRK_SUBSCRIBER_NUMBER,5,2),'00') TO_PRIN,
all the rest should work as is. The only issue I don't recognize STRING as a function name in Sybase and couldn't find anything on it. Can you point me in the correct direction or is this a user defined function?
March 23, 2006 at 6:07 am
Hai,
Here is equivalent converted code.
IF OBJECT_ID
('SPR_DWI_MKT_BRTMOVR') IS NULL
DROP PROCEDURE SPR_DWI_MKT_BRTMOVR
GO
CREATE PROCEDURE dbo.SPR_DWI_MKT_BRTMOVR
@AD_BEGIN_DATE DATETIME ,
@AD_END_DATE DATETIME
AS
SET NOCOUNT ON
/************************************ PART 1 NEW WO INFO *******************************************/
SELECT
CNT_REGION_CODE REG,
WRK_WO_NUMBER NEW_WO_NUM,
WRK_PROCESS_DATE WO_CLOSED,
DBO.STRING(SUBSTRING(SBT_SUBSCRIBER_NUMBER, 5, 2), '00') FM_PRIN,
SUBSTRING(SBT_SUBSCRIBER_NUMBER, 5, 12) OLD_SUB_NUM,
DBO.STRING(SUBSTRING(WRK_SUBSCRIBER_NUMBER, 5, 2), '00') TO_PRIN,
SUBSTRING(WRK_SUBSCRIBER_NUMBER, 5, 12) NEW_SUB_NUM,
MAX(CASE
WHEN SRM_BASIC_SERVICE + SRM_TIER_SERVICE > 0 THEN 1
ELSE 0
END) NBAS,
MAX(CASE
WHEN SRM_DIGITAL_SERVICE + SRM_DIGITAL_BOX + SRM_HDTV_SERVICE > 0 THEN 1
ELSE 0
END) NDIG,
MAX(CASE
WHEN SRM_RDRNNR_SERVICE + SRM_ALT_ISP_SERVICE > 0 THEN 1
ELSE 0
END) NHSD,
MAX(CASE
WHEN SRM_DIGITAL_PHONE_SERVICE > 0 THEN 1
ELSE 0
END) NPHN,
MAX(CASE
WHEN WRS_SERVICE_CODE IN ( 'E0' , 'E8' , 'G6' , 'J2' , 'J3' , 'J4' , 'J5' , 'L0' , 'L1' , 'K1' , 'K2' , 'K7' , 'D8' ) THEN 1
ELSE 0
END) NCOMBO,
MAX(CASE
WHEN WRS_SERVICE_CODE IN ( 'K3' , 'K4' , 'K5' , 'K6' , 'K8' , 'K9' , 'J8' , 'J9' , 'K0' , 'K6' ) THEN 1
ELSE 0
END) NCOMBOP INTO #TEMP_BRT1
FROM WORKORDER,
SUBSCRIBER_TRANSFER,
WORKORDER_SERVICE,
SERVICE_MATRIX,
COUNTY
WHERE WRK_SYS_PRIN_ID LIKE '82231%'
ANDWRK_WO_TYPE IN ( 'NC' , 'RC' , 'RS' )
ANDWRK_WO_REASONS LIKE 'RS%'
ANDWRK_STATUS_CODE = 'C'
ANDWRK_PROCESS_DATE >= @AD_BEGIN_DATE
ANDWRK_PROCESS_DATE DATEADD(DD, - 30, @AD_BEGIN_DATE)
GROUP BY SUBSTRING(WRK_SUBSCRIBER_NUMBER, 5, 12)
ORDER BY 1
/************************************ PART 3 OLD WO INFO *******************************************/
SELECT
DISCO_WO_NUM,
DISCO_SUB_NUM,
MAX(CASE
WHEN SRM_BASIC_SERVICE + SRM_TIER_SERVICE > 0 THEN 1
ELSE 0
END) OBAS,
MAX(CASE
WHEN SRM_DIGITAL_SERVICE + SRM_DIGITAL_BOX + SRM_HDTV_SERVICE > 0 THEN 1
ELSE 0
END) ODIG,
MAX(CASE
WHEN SRM_RDRNNR_SERVICE + SRM_ALT_ISP_SERVICE > 0 THEN 1
ELSE 0
END) OHSD,
MAX(CASE
WHEN SRM_DIGITAL_PHONE_SERVICE > 0 THEN 1
ELSE 0
END) OPHN,
MAX(CASE
WHEN WRS_SERVICE_CODE IN ( 'E0' , 'E8' , 'G6' , 'J2' , 'J3' , 'J4' , 'J5' , 'L0' , 'L1' , 'K1' , 'K2' , 'K7' , 'D8' ) THEN 1
ELSE 0
END) OCOMBO,
MAX(CASE
WHEN WRS_SERVICE_CODE IN ( 'K3' , 'K4' , 'K5' , 'K6' , 'K8' , 'K9' , 'J8' , 'J9' , 'K0' , 'K6' ) THEN 1
ELSE 0
END) OCOMBOP INTO #TEMP_BRT3
FROM #TEMP_BRT2,
WORKORDER,
WORKORDER_SERVICE,
SERVICE_MATRIX
WHERE DISCO_WO_NUM = WRK_WO_NUMBER
ANDWRK_WO_NUMBER = WRS_WO_NUMBER
ANDWRS_SYS_PRIN_ID = SRM_SYS_PRIN_ID
ANDWRS_AGENT_ID = SRM_AGENT_ID
ANDWRS_SERVICE_CODE = SRM_SERVICE_CODE
GROUP BY DISCO_WO_NUM,
DISCO_SUB_NUM
ORDER BY 1,
2
/************************************ PART 4 COMBINING *******************************************/
SELECT
REG,
FM_PRIN,
OLD_SUB_NUM,
TO_PRIN,
NEW_SUB_NUM,
WO_CLOSED,
CASE
WHEN OBAS > 0
ANDODIG = 0
ANDOHSD = 0
ANDOPHN = 0 THEN 'ANL'
WHEN OBAS >= 0
ANDODIG > 0
ANDOHSD = 0
ANDOPHN = 0 THEN 'DIG'
WHEN OBAS >= 0
ANDODIG > 0
ANDOHSD > 0
ANDOPHN = 0 THEN 'DIG-HSD'
WHEN OBAS >= 0
ANDODIG > 0
ANDOHSD > 0
ANDOPHN > 0 THEN 'DIG-HSD-PHN'
WHEN OBAS = 0
ANDODIG = 0
ANDOHSD > 0
ANDOPHN = 0 THEN 'HSD'
WHEN OBAS >= 0
ANDODIG = 0
ANDOHSD > 0
ANDOPHN = 0 THEN 'ANL-HSD'
WHEN OBAS = 0
ANDODIG = 0
ANDOHSD = 0
ANDOPHN > 0 THEN 'PHN'
WHEN OBAS >= 0
ANDODIG > 0
ANDOHSD = 0
ANDOPHN > 0 THEN 'DIG-PHN'
WHEN OBAS > 0
ANDODIG = 0
ANDOHSD > 0
ANDOPHN > 0 THEN 'ANL-HSD-PHN'
WHEN OBAS > 0
ANDODIG = 0
ANDOHSD = 0
ANDOPHN > 0 THEN 'ANL-PHN'
WHEN OBAS = 0
ANDODIG = 0
ANDOHSD > 0
ANDOPHN > 0 THEN 'HSD-PHN'
ELSE '???'
END OLD_SERVICE,
CASE
WHEN NBAS > 0
ANDNDIG = 0
ANDNHSD = 0
ANDNPHN = 0 THEN 'ANL'
WHEN NBAS >= 0
ANDNDIG > 0
ANDNHSD = 0
ANDNPHN = 0 THEN 'DIG'
WHEN NBAS >= 0
ANDNDIG > 0
ANDNHSD > 0
ANDNPHN = 0 THEN 'DIG-HSD'
WHEN NBAS >= 0
ANDNDIG > 0
ANDNHSD > 0
ANDNPHN > 0 THEN 'DIG-HSD-PHN'
WHEN NBAS = 0
ANDNDIG = 0
ANDNHSD > 0
ANDNPHN = 0 THEN 'HSD'
WHEN NBAS >= 0
ANDNDIG = 0
ANDNHSD > 0
ANDNPHN = 0 THEN 'ANL-HSD'
WHEN NBAS = 0
ANDNDIG = 0
ANDNHSD = 0
ANDNPHN > 0 THEN 'PHN'
WHEN NBAS >= 0
ANDNDIG > 0
ANDNHSD = 0
ANDNPHN > 0 THEN 'DIG-PHN'
WHEN NBAS > 0
ANDNDIG = 0
ANDNHSD > 0
ANDNPHN > 0 THEN 'ANL-HSD-PHN'
WHEN NBAS > 0
ANDNDIG = 0
ANDNHSD = 0
ANDNPHN > 0 THEN 'ANL-PHN'
WHEN NBAS = 0
ANDNDIG = 0
ANDNHSD > 0
ANDNPHN > 0 THEN 'HSD-PHN'
ELSE '???'
END NEW_SERVICE,
CASE
WHEN OCOMBO = 0
ANDOCOMBOP = 0
ANDNCOMBO > 0 THEN 1
WHEN OCOMBO = NCOMBO THEN 0
WHEN OCOMBOP > 0
ANDNCOMBOP = 0 THEN 0
ELSE 0
END 'UPG_DIG_CMBO',
CASE
WHEN OCOMBOP = 0
ANDNCOMBOP > 0 THEN 1
WHEN OCOMBOP = NCOMBOP THEN 0
WHEN OCOMBOP > 0
ANDNCOMBOP = 0 THEN 0
ELSE 0
END 'UPG_DIG_CMBO_PLUS',
CASE
WHEN SAC_EMAIL_ADDRESS IS NULL THEN ' '
WHEN SAC_SOLICIT_FLAG = 'N' THEN 'N'
ELSE SAC_EMAIL_ADDRESS
END 'EMAIL_ADDRESS'
FROM #TEMP_BRT1,
#TEMP_BRT3,
SUBSCRIBER_ACCESS
WHERE SAC_PRIMARY_FLAG = 'Y'
ANDOLD_SUB_NUM = DISCO_SUB_NUM
ORDER BY 1,
2,
4,
5
RETURN 0
GO
GRANT EXECUTE
ON SPR_DWI_MKT_BRTMOVR
TO public
GO
(This script is converted using SwisSQL Tool)
Regards,
Vignesh.A
Automated Database Migration Solution
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply