ENGINE SQLORA SET CONNECTION_ATTRIBUTES EJSWAR
SQL SQLORA
select distinct INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER,
INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE,
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE,
OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID,
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID,OFFICERS.CAD_EMP_ID
from EMPLOYEES, EMP_SERVICE_HISTORIES, INCIDENTS, INCIDENT_SUPPLEMENTS, INCIDENT_TYPE_CODES, OFFENSES , OFFICERS
where INCIDENTS.INCIDENT_ID=INCIDENT_SUPPLEMENTS.INC_INCIDENT_ID and
INCIDENTS.INCIDENT_ID=OFFENSES.INC_INCIDENT_ID and
EMPLOYEES.EJS_EMP_ID=EMP_SERVICE_HISTORIES.EJS_EMP_ID and
INCIDENT_TYPE_CODES.CODE=INCIDENTS.ITC_CODE and
INCIDENT_SUPPLEMENTS.CREATOR_ID = EMPLOYEES.LOGIN_ID and
INCIDENTS.ITC_CODE = INCIDENT_TYPE_CODES.CODE AND
offenses.OFFNSE_CD_OFFENSE_STATUS_CODE = '03' and
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE = 'A'
-*and INCIDENT_SUPPLEMENTS.APPROVAL_DATE >= trunc(sysdate) -2 and INCIDENT_SUPPLEMENTS.APPROVAL_DATE < trunc(sysdate)

[COLOR:RED]//Add the Beg_date_issue and End_date_issue[/COLOR]

AND APPROVAL_DATE FROM '&BEG_DATE_ISSUED.EVAL' TO '&END_DATE_ISSUED.EVAL'

AND OFFICERS.OFFICER_ID=EMPLOYEES.OFFICER_ID
group by EMPLOYEES.LNAME,INCIDENT_SUPPLEMENTS.APPROVAL_DATE, INCIDENTS.INC_REPORT_NUMBER,
INCIDENT_SUPPLEMENTS.CREATOR_ID, INCIDENT_TYPE_CODES.DESCRIPTION, OFFENSES.OFFNSE_CD_OFFENSE_STATUS_CODE,
INCIDENT_SUPPLEMENTS.ISC_STATUS_CODE, INCIDENTS.INCIDENT_ID, INCIDENTS.ITC_CODE, EMPLOYEES.FNAME,
OFFENSES.INC_INCIDENT_ID, EMPLOYEES.LOGIN_ID, EMP_SERVICE_HISTORIES.EJS_EMP_ID, EMPLOYEES.EJS_EMP_ID,
EMP_SERVICE_HISTORIES.SRV_ASSIGN_CODE , EMPLOYEES.OFFICER_ID,OFFICERS.CAD_EMP_ID;
-*and ROWNUM <= 10;  

quote:

Originally posted by QuickLearner:
Solved the WHERE CLAUSE has to be added to the TABLE FILE

 
-SET &WhereEmpNum = IF '&WhereEmpNum.EVAL' EQ '' THEN '' ELSE  'AND EMPLOYEE_NBR = ' | '''' | '&WhereEmpNum.EVAL' | '''';
-SET &WhereSite = IF '&WhereStore.EVAL' EQ '' THEN '' ELSE  'AND STORE = ' | '''' |  '&WhereStore.EVAL' | '''';
-TYPE WhereSite =(&WhereSite);
-TYPE WhereEmpNum =(&WhereEmpNum);


-INCLUDE hr_shared_sqlora
SQL
SELECT
    EMPLOYEE_NBR,
    NAME,
    JOBCODE,
    EARN_CODE,
    ATTEND_DATE,
    EARNS_END_DT,
    PAY_END_DT,
    PAY_END_DT + 5 AS ORA_PAY_END,
    PUNCH_TIME_IN,
    PUNCH_TIME_OUT,
    UPDATED_BY,
    UPDATE_DTTM,
    HOURS_QTY,
    STORE,
    CAST(COST_CENTRE AS CHAR(10)) AS COST_CENTRE,
    TL_SOURCE,
    SEQ_NBR,
    PAYROLL_SOURCE
    FROM TPDATA.TABLENAME
   WHERE TL_SOURCE IN ( 'T', 'A')
    AND BRAND = 'WEN'
    AND PAY_END_DT IN
         (SELECT DISTINCT PAY_END_DT
           FROM TPDATA.TABLENAME
            WHERE ATTEND_DATE BETWEEN '&BegDate3' AND '&EndDate3'
             AND TL_SOURCE IN ( 'T', 'A')
             AND BRAND = 'WEN'
           &WhereEmpNum
           &WhereSite
          )
&WhereEmpNum
&WhereSite
ORDER BY EMPLOYEE_NBR, ATTEND_DATE, SEQ_NBR
;
TABLE
ON TABLE HOLD AS PNCHPULL FORMAT ALPHA
END
-RUN

 

quote:

Originally posted by Wep5622:
You can add the variable right where you tried to do just that. Didn't that work? Of course, you would need to use SQL syntax for your condition and not FOCUS syntax.

Any problem you apparently encountered in doing so is most likely due to your use of .EVAL where you just want variable substitution.

As people in many programming languages chant: ".EVAL is evil!".

That's probably no less true for dialog manager.

EVAL is definitely a good candidate for SQL injection, especially in the scenario of pass-through SQL (as is the case here). For example, someone might just send the string '1 OR 1 IN (DELETE FROM EMPLOYEES)' as a value, which then becomes part of the SQL query after evaluation.