April 13, 2006 at 10:02 am
I attempted to convert this script to SQL but having trouble because it's not returning any data from the SQL side. Can I get a good eye to see if I miss something in my conversion. I'm getting returns for the column but no data is being populated to match that in Sybase.
SELECT HSE_HOUSE_NUMBER HOUSE_NUM,
JOB_CUSTOMER_NAME FULL_NAME,
HSE_LINE_1_ADDRESS ADDR1,
HSE_LINE_2_ADDRESS ADDR2,
HSE_CITY_NAME CITY,
HSE_STATE_CODE STATE,
HSE_ZIP_CODE ZIP,
JOB_HOME_PHONE_NUMBER PHONE,
SAC_EMAIL_ADDRESS E_MAIL,
SUM(JBS_NET_QUANTITY)
FROM JOB,
JOB_SERVICE,
SERVICE_MATRIX,
HOUSE,
SUBSCRIBER_ACCESS
WHERE JOB_JOB_NUMBER = JBS_JOB_NUMBER
AND JBS_SYS_PRIN_ID = SRM_SYS_PRIN_ID
AND JBS_AGENT_ID = SRM_AGENT_ID
AND JBS_SERVICE_CODE = SRM_SERVICE_CODE
AND JOB_HOUSE_NUMBER = HSE_HOUSE_NUMBER
AND JBS_SUBSCRIBER_NUMBER = SAC_SUBSCRIBER_NUMBER
AND JOB_STATUS = 'C'
AND JOB_PROCESS_DATE >= '2006-01-22'
AND JOB_PROCESS_DATE <= '2006-02-21'
AND JBS_SERVICE_CODE IN ('8V')
AND JBS_BEFORE_QUANTITY > 0
AND JBS_BEFORE_QUANTITY < 1
AND JBS_SUBSCRIBER_NUMBER IN (SELECT SBR_SUBSCRIBER_NUMBER
FROM SUBSCRIBER, SUBSCRIBER_SERVICE, SUBSCRIBER_PACKAGE
WHERE SBR_SUBSCRIBER_NUMBER = SBS_SUBSCRIBER_NUMBER
AND SBS_SUBSCRIBER_NUMBER = SBP_SUBSCRIBER_NUMBER
AND SBR_SUBSCRIBER_NUMBER = JBS_SUBSCRIBER_NUMBER
AND SBP_RDRNNR_SERVICE + SBP_ALT_ISP_SERVICE < 1
AND SBP_EXMAIL_FLAG = 0
AND SBP_EXEMAIL_FLAG = 0
AND SBP_EXCALL_FLAG = 0
AND SBP_FREE_FLAG = 0
AND SBP_COMMERCIAL_FLAG = 0
AND SBP_BULK_FLAG = 0
AND SBR_EXTERNAL_STATUS = ' '
AND SBR_BILL_CYCLE_CODE <> '00'
AND SBR_DELINQUENCY_STATUS = ' '
AND SUBSTRING(SBR_HOME_PHONE_NUMBER,4,7) NOT IN ('0000000','9999999','1111111')
AND SUBSTRING(SBR_HOME_PHONE_NUMBER,4,1) <> ' '
AND SBR_VIP_FLAG <> 'Z'
AND SBP_DVR_SERVICE = 0
AND SBP_DVR_BOX = 0)
GROUP BY HSE_HOUSE_NUMBER,
JOB_CUSTOMER_NAME,
HSE_LINE_1_ADDRESS,
HSE_LINE_2_ADDRESS,
HSE_CITY_NAME,
HSE_STATE_CODE,
HSE_ZIP_CODE,
JOB_HOME_PHONE_NUMBER,
SAC_EMAIL_ADDRESS
April 13, 2006 at 10:28 am
What was the Sybase code?
Have you verified that the sub query returns any rows?
SELECT SBR_SUBSCRIBER_NUMBER
FROM SUBSCRIBER, SUBSCRIBER_SERVICE, SUBSCRIBER_PACKAGE
WHERE SBR_SUBSCRIBER_NUMBER = SBS_SUBSCRIBER_NUMBER
AND SBS_SUBSCRIBER_NUMBER = SBP_SUBSCRIBER_NUMBER
AND SBR_SUBSCRIBER_NUMBER = JBS_SUBSCRIBER_NUMBER
AND SBP_RDRNNR_SERVICE + SBP_ALT_ISP_SERVICE < 1
AND SBP_EXMAIL_FLAG = 0
AND SBP_EXEMAIL_FLAG = 0
AND SBP_EXCALL_FLAG = 0
AND SBP_FREE_FLAG = 0
AND SBP_COMMERCIAL_FLAG = 0
AND SBP_BULK_FLAG = 0
AND SBR_EXTERNAL_STATUS = ' '
AND SBR_BILL_CYCLE_CODE '00'
AND SBR_DELINQUENCY_STATUS = ' '
AND SUBSTRING(SBR_HOME_PHONE_NUMBER,4,7) NOT IN ('0000000','9999999','1111111')
AND SUBSTRING(SBR_HOME_PHONE_NUMBER,4,1) ' '
AND SBR_VIP_FLAG 'Z'
AND SBP_DVR_SERVICE = 0
AND SBP_DVR_BOX = 0
Tim S
April 13, 2006 at 10:52 am
This looks a little strange:
AND JBS_BEFORE_QUANTITY > 0
AND JBS_BEFORE_QUANTITY < 1
What's the data type for this column? If it's integer, that might explain the 'no rows returned' problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply