Hi All, im new in Uipath and i was about to add an existing oracle sql query. just curious how to declare the sql script as a query command?
i am able to run simple sql query enclose e.g " sql command " somehow i am not sure how will i execute the below sql command as i undertand it should be enclose in double quotes.
CREATE GLOBAL TEMPORARY TABLE tmptbl_policy_list ON COMMIT PRESERVE ROWS
AS
SELECT
--A.SEQUENCE_NUMBER,
CASE a.portfolio_type
WHEN '1' THEN
'TREATY'
ELSE
'FACULTATIVE'
END AS "PORTFOLIO TYPE",
a.underwriting_year AS uy,
to_number(a.contract_number) AS "CONTRACT NUMBER",
a.insured_name AS "DESCRIPTION",
TO_CHAR(a.inception_date, 'DD-MON-YY') AS "INCEPTION DATE",
TO_CHAR(a.expiry_date, 'DD-MON-YY') AS "EXPIRY DATE",
e.lob_description AS line,
h.branch_name AS branch,
i.sub_branch_name AS "SUB BRANCH",
CASE a.type_of_contract
WHEN '1' THEN
'PROP'
ELSE
'NON-PROP'
END AS ptcr,
f.premium_currency AS currency,
to_number(a.share_signed) AS "SHARE SIGNED %",
to_number(f.epi_whole) AS "EPI WHOLE",
to_number(f.original_gwp) AS premium,
to_number(j.max_liability) AS "MAX LIABILITY",
to_number(j.max_liability *(a.share_signed / 100)) AS "MAX LIABILITY - OUR SHARE",
upper(b.description) AS status,
a.cedant_code AS "CEDANT CODE",
c.broker_cedant_name AS "CEDANT NAME",
nvl(a.broker_code, '-') AS "BROKER CODE",
nvl(g.broker_cedant_name, '-') AS "BROKER NAME",
a.domicile_country_code AS "DOMICILE COUNTRY CODE",
d.country_description AS "DOMICILE COUNTRY",
CASE a.retrocession_ind
WHEN 'E' THEN
'YES'
WHEN 'R' THEN
'OLD POLICY'
ELSE
'NO'
END AS "IS RETROCEEDED?"
FROM
inwards_policy a
INNER JOIN contract_status b ON a.contract_status = b.contract_status
INNER JOIN broker_cedant c ON a.cedant_code = c.broker_cedant_code
INNER JOIN country d ON a.domicile_country_code = d.country_code
INNER JOIN lob e ON a.lob_code = e.lob_code
LEFT JOIN inwards_policy_premium f ON a.sequence_number = f.inwards_policy_sequence_no
LEFT JOIN broker_cedant g ON a.broker_code = g.broker_cedant_code
LEFT JOIN branch h ON a.branch_group_code = h.branch_group_code
AND a.branch_code = h.branch_code
LEFT JOIN sub_branch i ON a.branch_group_code = i.branch_group_code
AND a.branch_code = i.branch_code
AND a.sub_branch_code = i.sub_branch_code
LEFT JOIN inwards_policy_liability j ON a.sequence_number = j.inwards_policy_sequence_no
–WHERE
– A.CONTRACT_NUMBER IN (2299570, 2352623,2336516)
ORDER BY
a.portfolio_type,
a.contract_number,
a.underwriting_year;
BEGIN
–Insert Line to create temporary table
–Rename table name with what was created above
as_xlsx.query2sheet('SELECT * FROM tmptbl_Policy_List');
–Rename output file name with reportname timespamp-.xls
as_xlsx.save('REPORT_DIR', TO_CHAR(SYSDATE, 'DD-MM-YYYY-HH24-MI-SS')
|| '-Policy_List'
|| '.xlsx');
–Rename table name with what was created above
EXECUTE IMMEDIATE ( 'TRUNCATE TABLE tmptbl_Policy_List' );
EXECUTE IMMEDIATE ( 'COMMIT' );
EXECUTE IMMEDIATE ( 'Drop TABLE tmptbl_Policy_List' );
EXECUTE IMMEDIATE ( 'COMMIT' );
END;
CREATE OR REPLACE PROCEDURE POLICY_LIST;
BEGIN
SELECT FUNCTION_ID FROM RMS_FUNCTION_TEXT
WHERE FUNCTION_NAME ='Receipt Enquiry;
END POLICY_LIST;
ashokkarale
MD_Farhan1
Ajay_Mishra
postwick
Dheerendra_vishwakarma
Anil_G
chandreshsinh.jadeja
Gautham_Pattabiraman
vrdabberu
aravindbalineni123