Link to home
Start Free TrialLog in
Avatar of 25112
25112

asked on

organize big query to meaning format

can i make SSMS make a big unformatted query into a nicely formatted query to understand it better?
Avatar of 25112
25112

ASKER

here is an example what i have in mind..
example.txt
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If I first remove the quote characters, as recommended by Jim Horn, the result is:
SELECT KXos1.office_code                              AS Office_Code,
       KXos1.office_name                              AS Office_Name,
       KXos1.kxos_3_served                            AS KXos_3_Served,
       KXos1.employee_id                              AS Employee_ID,
       KXos1.c5                                       AS c5,
       KXos1.passed                                   AS Passed,
       KXos1.c7                                       AS c7,
       Evaluation_Previous_Year_2.attained_level_code AS c8,
       CASE
         WHEN NOT KXos1.c7 IS NULL THEN
           CASE
             WHEN Cast(KXos1.c5 AS REAL) - Cast(KXos1.c7 AS REAL) >= .6 THEN 1
             ELSE 0
           END
         WHEN NOT Evaluation_Previous_Year_2.attained_level_code IS NULL THEN
           CASE
             WHEN Cast(KXos1.c5 AS REAL) - Cast(
                  Evaluation_Previous_Year_2.attained_level_code AS
                         REAL) >= .6 THEN 1
             ELSE 0
           END
         ELSE 0
       END                                            AS Growth,
       CASE
         WHEN KXos1.passed = 0
              AND CASE
                    WHEN NOT KXos1.c7 IS NULL THEN
                      CASE
                        WHEN ( Cast(KXos1.c5 AS REAL) - Cast(KXos1.c7 AS REAL)
                               >= .6 )
                      THEN 1
                        ELSE 0
                      END
                    WHEN NOT Evaluation_Previous_Year_2.attained_level_code IS
                             NULL
                  THEN
                      CASE
                        WHEN ( Cast(KXos1.c5 AS REAL) - Cast(
                               Evaluation_Previous_Year_2.attained_level_code
                               AS
                                        REAL) >= .6 ) THEN 1
                        ELSE 0
                      END
                    ELSE 0
                  END = 0 THEN 1
         ELSE 0
       END                                            AS No_Up
FROM   (SELECT KXos3_Assess_CurrentYear.office_code           AS Office_Code,
               KXos3_Assess_CurrentYear.office_name           AS Office_Name,
               KXos3_Assess_CurrentYear.kxos_3_served         AS KXos_3_Served,
               KXos3_Assess_CurrentYear.employee_id           AS Employee_ID,
               KXos3_Assess_CurrentYear.c5                    AS c5,
               KXos3_Assess_CurrentYear.passed                AS Passed,
               Evaluation_Previous_Year_1.attained_level_code AS c7
        FROM   (SELECT DISTINCT KXos_3_Final.office_code                    AS
                                Office_Code,
                                KXos_3_Final.office_name                    AS
                                Office_Name,
                                KXos_3_Final.employee_id                    AS
                                Employee_ID,
                                KXos_3_Final.kxos_3_served                  AS
                                KXos_3_Served,
                                Evaluation_Current_Year.attained_level_code AS
                                c5,
                                CASE
                                  WHEN
               Evaluation_Current_Year.attained_level_code =
               'NA' THEN
                                  0
                                  WHEN
               Evaluation_Current_Year.attained_level_code >=
               4.5 THEN
                                  1
                                  ELSE 0
                                END                                         AS
                                Passed
                FROM   (SELECT DISTINCT KXos_3.office_code            AS
                                        Office_Code,
                                        KXos_3.office_name            AS
                                        Office_Name,
                                        KXos_3.premise_id             AS
                                        Premise_ID,
                                        KXos_3.full_premise_name      AS
                                        Full_Premise_Name,
                                        KXos_3.employee_id            AS
                                        Employee_ID,
                                        KXos_3.current_position_level AS
                                        Current_Position_Level
                                        ,
                                        KXos_3.ethnicity
                                        AS Ethnicity,
                                        KXos_3.ethnicity_code         AS
                                        Ethnicity_Code
                                        ,
       KXos_3.employee_gender        AS Employee_Gender,
       KXos_3.employee_gender_code   AS Employee_Gender_Code,
       KXos_3.main_skill_code        AS Main_Skill_Code,
       KXos_3.mainskillcode          AS MainSkillCode,
       KXos_3.kxos_3_served          AS KXos_3_Served,
       KXos_3.current_pel            AS Current_PEL
       FROM   (SELECT office.office_code                   AS Office_Code,
       office.office_name                   AS Office_Name,
       premise.premise_id                   AS Premise_ID,
       premise.premise_name                 AS
       Full_Premise_Name,
       Employee_Snapshot.employee_id        AS Employee_ID,
       Employee_Snapshot.curr_position_lvl  AS
       Current_Position_Level,
       Employee_Snapshot.ethnic_desc        AS Ethnicity,
       Employee_Snapshot.ethnic_code        AS Ethnicity_Code,
       Employee_Snapshot.employee_gender    AS Employee_Gender,
       Employee_Snapshot.employee_gender_cd AS
       Employee_Gender_Code,
       Employee_Snapshot.main_prim_skill    AS Main_Skill_Code,
       Employee_Snapshot.mainskillcode      AS MainSkillCode,
       1                                    AS KXos_3_Served,
       CASE
        WHEN Employee_Snapshot.pel_part_code IN ( '01', '02' )
       THEN
        'Y'
        ELSE 'N'
       END                                  AS Current_PEL,
       Employee_Snapshot.pel_duration       AS LPP__Duration,
       Employee_Snapshot.pel_eligibility    AS LPP__Eligibility
       ,
       Employee_Snapshot.pel_eligibil_code  AS LPP__Eligibility_Code,
       Employee_Snapshot.pel_part           AS LPP__Participation,
       Employee_Snapshot.pel_part_code      AS LPP__Participation_Code
       FROM   rzaj.office Office,
       rzaj.premise Premise,
       rzaj.emp_snapshot Employee_Snapshot
       WHERE  Cast(Employee_Snapshot.eval_date AS DATE) = {d '2014-02-02'}
       AND Cast(Employee_Snapshot.snapshot_date AS DATE) =
       {d '2014-02-02'}
       AND ( office.level_aggr IN ( 'D1', 'S1' )
       OR premise.org_type_code = 'CC' )
       AND Employee_Snapshot.pel_part_code IN ( '01', '02' )
       AND Employee_Snapshot.pel_eligibility = 'KXos 3Served'
       AND office.office_key = Employee_Snapshot.office_key
       AND premise.premise_key = Employee_Snapshot.premise_key
       AND ( office.office_code = 4322 )) KXos_3) KXos_3_Final
       LEFT OUTER JOIN (SELECT DISTINCT office.office_code
       AS Office_Code,
       office.office_name
       AS Office_Name,
       premise.premise_id
       AS Premise_ID,
       premise.loc_short_name
       AS
       Common_Premise_Name,
       Employee_Snapshot.employee_id
       AS Employee_ID,
       Evaluation_Attributes.review_desc
       AS Evaluation_ID,
       Evaluation_Attributes.version
       AS
       Evaluation_EvalDate,
       Evaluation_Attributes.review_desc_version
       AS
       Evaluation_ID_and_EvalDate,
       Evaluation_Attributes.review_type
       AS
       Evaluation_Type,
       Evaluation_Attributes.review_desc_long
       AS
       Evaluation_Name,
       Evaluation_Attributes.review_group
       AS
       Evaluation_Group,
       Evaluation_Attributes.review_category
       AS
       Evaluation_Category,
       Evaluation_Attributes.review_vendor
       AS
       Evaluation_Vendor,
       Evaluation_Ctgy_Attributes.point_desc
       AS
       Ctgy_Identifier,
       Evaluation_Ctgy_Attributes.point_subject_area
       AS
       Ctgy_Subject_Area_Code,
       Evaluation_Ctgy_Attributes.point_item_type
       AS c16,
       CASE
       WHEN Evaluation_Ctgy_Levels.attain_code =
       ' NA ' THEN '0'
       ELSE Evaluation_Ctgy_Levels.attain_code
       END
       AS
       Attained_Level_Code,
       Employee_Snapshot.pel_part_code
       AS
       LPP__Participation_Code,
       Employee_Snapshot.pel_part
       AS
       LPP__Participation,
       CASE
       WHEN Evaluation_Ctgy_Levels.attain_code =
       ' NA ' THEN '0'
       WHEN Cast(Evaluation_Ctgy_Levels.attain_code
           AS REAL) > 4.4
       THEN '1'
       ELSE '0'
       END
       AS Attainment
       FROM   rzaj.office Office,
       rzaj.premise Premise,
       rzaj.emp_snapshot Employee_Snapshot,
       rzaj.evaluation_info Evaluation_Attributes,
       rzaj.evaluation_item Evaluation_Ctgy_Attributes,
       rzaj.evaluation_fact Evaluation_Ctgy_Levels
       WHERE  Evaluation_Attributes.review_desc = 'PAELL'
       AND Evaluation_Ctgy_Levels.eval_date = {d '2014-02-02'}
       AND Evaluation_Ctgy_Attributes.point_subject_area =
       'Tender'
       AND Employee_Snapshot.pel_part_code IN ( '01', '02' )
       AND office.office_key =
       Evaluation_Ctgy_Levels.office_key
       AND Evaluation_Attributes.review_key =
       Evaluation_Ctgy_Levels.review_key
       AND premise.premise_key =
       Evaluation_Ctgy_Levels.premise_key
       AND Employee_Snapshot.employee_key =
       Evaluation_Ctgy_Levels.employee_key
       AND Evaluation_Ctgy_Attributes.point_key =
       Evaluation_Ctgy_Levels.point_key
       AND ( office.office_code = 4322 ))
       Evaluation_Current_Year
       ON KXos_3_Final.employee_id = Evaluation_Current_Year.employee_id)
       KXos3_Assess_CurrentYear
       LEFT OUTER JOIN (SELECT DISTINCT office.office_code
               AS Office_Code,
               office.office_name
                                        AS Office_Name,
               premise.premise_id
                                        AS Premise_ID,
               premise.loc_short_name
                                        AS
       Common_Premise_Name,
               Employee_Snapshot.employee_id
                                        AS Employee_ID,
               Evaluation_Attributes.review_desc
                                        AS Evaluation_ID,
               Evaluation_Attributes.version
                                        AS
       Evaluation_EvalDate,
               Evaluation_Attributes.review_desc_version
                                        AS
       Evaluation_ID_and_EvalDate,
               Evaluation_Attributes.review_type
                                        AS
       Evaluation_Type,
               Evaluation_Attributes.review_desc_long
                                        AS
       Evaluation_Name,
               Evaluation_Attributes.review_group
                                        AS
       Evaluation_Group,
               Evaluation_Attributes.review_category
                                        AS
       Evaluation_Category,
               Evaluation_Attributes.review_vendor
                                        AS
       Evaluation_Vendor,
               Evaluation_Ctgy_Attributes.point_desc
                                        AS
       Ctgy_Identifier,
               Evaluation_Ctgy_Attributes.point_subject_area
                                        AS
       Ctgy_Subject_Area_Code,
               Evaluation_Ctgy_Attributes.point_item_type
                                        AS c16,
               CASE
                 WHEN Evaluation_Ctgy_Levels.attain_code =
                      'NA' THEN '0'
                 ELSE Evaluation_Ctgy_Levels.attain_code
               END
                                        AS
       Attained_Level_Code,
               Employee_Snapshot.pel_part_code
                                        AS
       LPP__Participation_Code,
               Employee_Snapshot.pel_part
                                        AS
       LPP__Participation
       FROM   rzaj.office Office,
       rzaj.premise Premise,
       rzaj.emp_snapshot Employee_Snapshot,
       rzaj.evaluation_info Evaluation_Attributes,
       rzaj.evaluation_item Evaluation_Ctgy_Attributes,
       rzaj.evaluation_fact Evaluation_Ctgy_Levels
       WHERE  Evaluation_Attributes.review_desc = 'PAELL'
       AND Dateadd(year, 1, Cast(
          Evaluation_Ctgy_Levels.eval_date AS DATE)) =
          {d '2014-02-02'}
       AND Evaluation_Ctgy_Attributes.point_subject_area =
          'Tender'
       AND Employee_Snapshot.pel_part_code IN ( '01', '02' )
       AND office.office_key =
          Evaluation_Ctgy_Levels.office_key
       AND Evaluation_Attributes.review_key =
          Evaluation_Ctgy_Levels.review_key
       AND premise.premise_key =
          Evaluation_Ctgy_Levels.premise_key
       AND Employee_Snapshot.employee_key =
          Evaluation_Ctgy_Levels.employee_key
       AND Evaluation_Ctgy_Attributes.point_key =
          Evaluation_Ctgy_Levels.point_key
       AND ( office.office_code = 4322 ))
                       Evaluation_Previous_Year_1
       ON KXos3_Assess_CurrentYear.employee_id =
       Evaluation_Previous_Year_1.employee_id) KXos1
       LEFT OUTER JOIN (SELECT DISTINCT office.office_code
                                        AS Office_Code,
                                        office.office_name
                                                                     AS
                                        Office_Name,
                                        premise.premise_id
                                                                     AS
                                        Premise_ID,
                                        premise.loc_short_name
                                                                     AS
                       Common_Premise_Name,
                                        Employee_Snapshot.employee_id
                                                                     AS
                                        Employee_ID,
                                        Evaluation_Attributes.review_desc
                                                                     AS
                                        Evaluation_ID,
                                        Evaluation_Attributes.version
                                                                     AS
                       Evaluation_EvalDate,
Evaluation_Attributes.review_desc_version
                             AS
Evaluation_ID_and_EvalDate,
Evaluation_Attributes.review_type
                             AS
Evaluation_Type,
Evaluation_Attributes.review_desc_long
                             AS
Evaluation_Name,
Evaluation_Attributes.review_group
                             AS
Evaluation_Group,
Evaluation_Attributes.review_category
                             AS
Evaluation_Category,
Evaluation_Attributes.review_vendor
                             AS
Evaluation_Vendor,
Evaluation_Ctgy_Attributes.point_desc
                             AS
Ctgy_Identifier,
Evaluation_Ctgy_Attributes.point_subject_area
                             AS
Ctgy_Subject_Area_Code,
Evaluation_Ctgy_Attributes.point_item_type
                             AS c16,
CASE
  WHEN Evaluation_Ctgy_Levels.attain_code =
       'NA' THEN '0'
  ELSE Evaluation_Ctgy_Levels.attain_code
END
                             AS
Attained_Level_Code,
Employee_Snapshot.pel_part_code
                             AS
LPP__Participation_Code,
Employee_Snapshot.pel_part
                             AS
LPP__Participation
FROM   rzaj.office Office,
rzaj.premise Premise,
rzaj.emp_snapshot Employee_Snapshot,
rzaj.evaluation_info Evaluation_Attributes,
rzaj.evaluation_item Evaluation_Ctgy_Attributes,
rzaj.evaluation_fact Evaluation_Ctgy_Levels
WHERE  Evaluation_Attributes.review_desc = 'PAELL'
AND Dateadd(year, 2, Cast(
Evaluation_Ctgy_Levels.eval_date AS DATE)) =
{d '2014-02-02'}
AND Evaluation_Ctgy_Attributes.point_subject_area =
'Tender'
AND Employee_Snapshot.pel_part_code IN ( '01', '02' )
AND office.office_key =
Evaluation_Ctgy_Levels.office_key
AND Evaluation_Attributes.review_key =
Evaluation_Ctgy_Levels.review_key
AND premise.premise_key =
Evaluation_Ctgy_Levels.premise_key
AND Employee_Snapshot.employee_key =
Evaluation_Ctgy_Levels.employee_key
AND Evaluation_Ctgy_Attributes.point_key =
Evaluation_Ctgy_Levels.point_key
AND ( office.office_code = 4322 ))
Evaluation_Previous_Year_2
ON KXos1.employee_id = Evaluation_Previous_Year_2.employee_id 

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Apparently it's the ODBC way of specifying a date literal, so this works fine:
Yep.  It has been there since I believe SQL Server 6.5
Avatar of 25112

ASKER

Jim Horn and others..
thank you- will implement ideas and the tools helped.
Thanks for the split.  Good luck with the code.  -Jim