Nsryjdtyk

I am creating a program that manipulates SQL queries. At the moment it's very simple and all I want to do is define a variable with the SQL statement inside it as a string.

E.G.

str = "SELECT DISTINCT 

pv.project_id,

gps.period_name,

gps.period_year,

rbse.resource_source_id,

rbse.alias as resource_name,

DECODE(pjo_plan_version_utils.get_time_phased_code(pv.plan_version_id), 'G', 

       pld.quantity, pjo_plan_version_utils.spread_amount('L', pld.start_date, pld.end_date, gps.start_date, gps.end_date, pld.quantity)

) as hours,

(131.4-pld.quantity)/131.4 unallocated_percentage

FROM

    pjf_rbs_elements rbse,

    gl_period_statuses gps,

    pjo_plan_line_details pld,

    pjo_planning_elements pe,

    pjo_plan_versions_vl pv,

    pjo_plan_types_vl pt

WHERE

    1=1

    AND pe.rbs_element_id = rbse.rbs_element_id 

    AND pld.planning_element_id = pe.planning_element_id 

    AND pv.plan_version_id = pe.plan_version_id

    AND pv.current_plan_status_flag = 'Y'

    AND ((gps.start_date <= pld.end_date)

    AND (gps.end_date >= pld.start_date))

    AND gps.adjustment_period_flag = 'N'

    AND gps.application_id = 10037

    AND pv.plan_type_id = pt.plan_type_id

    AND pt.plan_type_code = 'PROJECT_PLAN'"

I am getting invalid syntax errors so I am clearly not defining it correctly. What is the best way to do this? For insight, the script will analyse str to identify tables and columns and return anything that matches a list.

The string in multiline so you need to use triple quotes or else you get an EOL. Also you shouldn't name a variable str because it is already a function.

string = """SELECT DISTINCT 

pv.project_id,

gps.period_name,

gps.period_year,

rbse.resource_source_id,

rbse.alias as resource_name,

DECODE(pjo_plan_version_utils.get_time_phased_code(pv.plan_version_id), 'G', 

       pld.quantity, pjo_plan_version_utils.spread_amount('L', pld.start_date, pld.end_date, gps.start_date, gps.end_date, pld.quantity)

) as hours,

(131.4-pld.quantity)/131.4 unallocated_percentage

FROM

    pjf_rbs_elements rbse,

    gl_period_statuses gps,

    pjo_plan_line_details pld,

    pjo_planning_elements pe,

    pjo_plan_versions_vl pv,

    pjo_plan_types_vl pt

WHERE

    1=1

    AND pe.rbs_element_id = rbse.rbs_element_id 

    AND pld.planning_element_id = pe.planning_element_id 

    AND pv.plan_version_id = pe.plan_version_id

    AND pv.current_plan_status_flag = 'Y'

    AND ((gps.start_date <= pld.end_date)

    AND (gps.end_date >= pld.start_date))

    AND gps.adjustment_period_flag = 'N'

    AND gps.application_id = 10037

    AND pv.plan_type_id = pt.plan_type_id

    AND pt.plan_type_code = 'PROJECT_PLAN'"""

Your string concatenation is incorrect.

Try something like this:

str = "line1" 

      "line2" 

      "line3" 



#output : no spaces or new lines

line1line2line3

or (but you can not indent by doing below way, if you indent, the indentation becomes part of concatenation)

str = """line1

line2

line3"""



#output : new lines

line1

line2

line3