Sybase To SQL 200

• Mac-312815

  SSC Enthusiast

  Points: 130

  More actions

  March 28, 2006 at 8:44 am

  #171026

  How would I convert this to SQL??? 

  SELECT   rgn_region_code AS phone_region,

           wrk_wo_number,

           wrk_process_date,

           (CASE

              WHEN (Substring(wrk_wo_reasons,1,2) = 'ST'

                     OR Substring(wrk_wo_reasons,3,2) = 'ST'

                     OR Substring(wrk_wo_reasons,5,2) = 'ST'

                     OR Substring(wrk_wo_reasons,7,2) = 'ST'

                     OR wrk_campaign_code IN ('GTE')) THEN 'Knology'

              WHEN srm_digital_phone_service > 0 THEN 'Phone'

              ELSE 'Dish'

            END) AS 'TYPE',

           (CASE

              WHEN Isnull(slr_department,'OTH') = 'DSR' THEN 'Direct Sales'

              ELSE 'Other'

            END) AS 'SOURCE',

           Sum(wrs_net_quantity) AS 'NET'

  INTO     #phone_temp

  FROM     workorder,

           workorder_service,

           sales_rep,

           service_matrix,

           region

  WHERE    wrk_sys_prin_id *= slr_sys_prin_id

           AND rgn_sys_prin_id = wrk_sys_prin_id

           AND wrk_sales_rep_number *= slr_sales_rep_number

           AND wrk_wo_number = wrs_wo_number

           AND wrk_wo_type IN ('RC',

                               'NC',

                               'RS',

                               'CS')

           AND wrk_sys_prin_id IN ('82231300',

                                   '82231400',

                                   '82231500')

           AND wrs_discount_code IN ('FA',

                                     'FD',

                                     'FG',

                                     'FJ',

                                     'FM')

           AND wrs_sys_prin_id = srm_sys_prin_id

           AND wrs_agent_id = srm_agent_id

           AND wrs_service_code = srm_service_code

           AND srm_digital_phone_service > 0

           AND wrk_process_date >= '2006-01-22'

           AND wrk_process_date <= '2006-02-21'

           AND wrk_status_code = 'C'

  GROUP BY rgn_region_code,

           wrk_wo_number,

           wrk_process_date,

           TYPE,

           source

  HAVING   Sum(wrs_net_quantity) > 0

                                   /* END OF PHONE TEMP QUERY */

  SELECT   'DIV',

           source,

           TYPE,

           Sum(net) AS gain,

           Convert(int,0) AS 'LOSS'

  FROM     #phone_temp

  WHERE    TYPE = 'Phone'

  GROUP BY source,

           TYPE

  UNION ALL

  SELECT   phone_region AS region_code,

           source,

           TYPE,

           Sum(net) AS gain,

           Convert(int,0) AS 'LOSS'

  FROM     #phone_temp

  WHERE    TYPE = 'Phone'

  GROUP BY region_code,

           source,

           TYPE

  ORDER BY 1,

           2,

           3

  Thanks,

  Mac

• March 31, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 31, 2006 at 10:22 am

  #630185

  syntax-wise, it raises no errors in SQL Query Analyzer; the sybase statement needs no conversions to get it to be correct in SQL Server.

  without the data, i do not know if it produces different result sets than Sybase.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!