Python Oracle SQL script pass argument by using same variable at once

Question

I'm running a Oracle SQL script in using python pandas and wanted to pass a variable using {}.format(var). the thing is I don't want to pass the same variable for the count of braces like {}{}{}.format(var1,var1,var1). However this should work like {}{}{}.format(var1).

is there a way to do this? Here is my SQL Script: So in place of '2018-05-01', I want to pass one variable and '2018-05-30' second variable.

    SELECT * FROM (SELECT SUBSTR(PLACEMENT_DESC,1,INSTR(PLACEMENT_DESC, '.', 1)-1) AS PLACEMENT#, TO_CHAR(SDATE, 'YYYY-MM-DD') AS START_DATE, 
TO_CHAR(EDATE, 'YYYY-MM-DD') AS END_DATE, INITCAP(CREATIVE_DESC)  AS PLACEMENT_NAME, COST_TYPE_DESC AS COST_TYPE, 
UNIT_COST AS UNIT_COST, BUDGET AS PLANNED_COST, BOOKED_QTY AS BOOKED_IMP#BOOKED_ENG FROM TFR_REP.SUMMARY_MV
WHERE (IO_ID = {})
     AND (DATA_SOURCE = 'KM')
     AND (TO_CHAR(SDATE, 'YYYY-MM-DD') BETWEEN
     (CASE WHEN '2018-05-01' BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(SDATE, 'YYYY-MM-DD')
      ELSE (CASE WHEN '2018-05-01' < TO_CHAR(SDATE, 'YYYY-MM-DD') AND '2018-05-01' <= TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(SDATE, 'YYYY-MM-DD')
            ELSE TO_CHAR(SDATE, 'YYYY-MM-DD') END)
      END)
     AND
     (CASE WHEN '2018-05-30' BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN '2018-05-30'
      ELSE (CASE WHEN '2018-05-30' > TO_CHAR(EDATE, 'YYYY-MM-DD') AND '2018-05-01' <= TO_CHAR(SDATE, 'YYYY-MM-DD') THEN TO_CHAR(EDATE, 'YYYY-MM-DD')
            ELSE TO_CHAR(EDATE, 'YYYY-MM-DD') END)
      END))
     AND
     (TO_CHAR(EDATE, 'YYYY-MM-DD') BETWEEN
     (CASE WHEN '2018-05-01' BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN '2018-05-01'
      ELSE (CASE WHEN '2018-05-01' < TO_CHAR(SDATE, 'YYYY-MM-DD') AND '2018-05-01' <= TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(SDATE, 'YYYY-MM-DD')
            ELSE TO_CHAR(SDATE, 'YYYY-MM-DD') END)
      END)
     AND
     (CASE WHEN '2018-05-30' BETWEEN TO_CHAR(SDATE, 'YYYY-MM-DD') AND TO_CHAR(EDATE, 'YYYY-MM-DD') THEN TO_CHAR(EDATE, 'YYYY-MM-DD')
      ELSE (CASE WHEN '2018-05-30' > TO_CHAR(EDATE, 'YYYY-MM-DD') AND '2018-05-01' <= TO_CHAR(SDATE, 'YYYY-MM-DD') THEN TO_CHAR(EDATE, 'YYYY-MM-DD')
            ELSE TO_CHAR(EDATE, 'YYYY-MM-DD') END)
      END))
     AND CREATIVE_DESC IN(SELECT DISTINCT CREATIVE_DESC
                          FROM TFR_REP.SUMMARY_MV))
WHERE Placement_Name Not LIKE '%Pre-Roll%' and  Placement_Name Not LIKE '%Pre–Roll%'

zipa · Accepted Answer · 2018-05-31 15:43:58Z

1

This might be what you are looking for:

a = 'first'
b = 'second'

'{0} {0} {0} {1}'.format(a, b)
#first first first second

answered May 31, 2018 at 15:43

zipa

28k6 gold badges45 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

DKM Over a year ago

How first and second differentiating between 0 and 1?

zipa Over a year ago

@MonikaRai It is the index of the passed arguments a and b and indexing in Python starts with 0

DKM Over a year ago

so inside braces i've to pass 0,1 and 2 and so on

zipa Over a year ago

@MonikaRai Exactly, you should indicate inside the curly braces the index of desired variable.

DKM Over a year ago

great, let me check

Collectives™ on Stack Overflow

Python Oracle SQL script pass argument by using same variable at once

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related