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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Yep. It has been there since I believe SQL Server 6.5
ASKER
Jim Horn and others..
thank you- will implement ideas and the tools helped.
thank you- will implement ideas and the tools helped.
Thanks for the split. Good luck with the code. -Jim
ASKER
example.txt