Customer Portal

Passing Date into a Graph File

  1. CloverDX Customer Portal
  2. Forum
  3. Passing Date into a Graph File
Avatar
nrmvijay1990
  • Answered
Follow
Hi,

This is Vijay and I am new to Graph file coding. So it would be gr8 if you could help me on this.

I have a graph file which uses SQL query to connect to DB and generate a data file. I wish to pass a parameter date to the graph file accordingly from which date, details can be extracted.

I have attached my graph file here. It would be gr8 if you could suggest me in this regard...

<?xml version="1.0" encoding="UTF-8"?>
<Graph name="COS_VALUATION_DTLS">

<Global>
<property id = "date" name= "date" />
<Metadata id="bodyMeta" previewAttachmentCharset="ISO-8859-1">
<Record fieldDelimiter="|" name="COS_VALUATION_DTLS" recordDelimiter="\n" recordSize="-1" type="delimited">
<Field name="APPREFID" size="16" type="string" />
<Field name="PROPSEQNO" size="2" type="string" />
<Field name="PROMOTIONCODE" size="16" type="string" />
<Field name="PRODDEFCODE" size="4" type="string" />
<Field name="VALUERCODE" size="5" type="string" />
<Field name="VALUATIONSTATUS" size="4" type="string" />
<Field name="VALUATIONDATE" size="10" type="string" />
<Field name="VALUATIONREFNO" size="16" type="string" />
<Field name="OCCUPANYTYPE" size="4" type="string" />
<Field name="PURCHASETYPECODE" size="4" type="string" />
<Field name="YEAROFCOMPLETION" size="4" type="string" />
<Field name="AGEOFPROPERTY" size="3" type="string" />
<Field name="GROSSAREA" size="5" type="string" />
<Field name="NETAREA" size="5" type="string" />
<Field name="GARDENFLAG" size="1" type="string" />
<Field name="FLATROOFFLAG" size="1" type="string" />
<Field name="PURCHASEPRICE" size="19" type="string" />
<Field name="FORMALVALUATIONPRICE" size="19" type="string" />
<Field name="OPENMARKETVALUE" size="19" type="string" />
<Field name="FORCEDSALEVALUE" size="19" type="string" />
<Field name="INSURANCEVALUE" size="19" type="string" />
<Field name="FORMALVALUATIONGFA" size="19" type="string" />
<Field name="OPENMARKETVALUEGFA" size="19" type="string" />
<Field name="FORCEDSALEVALUEGFA" size="19" type="string" />
<Field name="INSURANCEVALUEGFA" size="19" type="string" />
<Field name="FORMALVALUENETAREA" size="19" type="string" />
<Field name="OPENMARKETVALUENETAREA" size="19" type="string" />
<Field name="FORCEDSALEVALUENETAREA" size="19" type="string" />
<Field name="INSURANCEVALUENETAREA" size="19" type="string" />
<Field name="VALUATIONFEE" size="19" type="string" />
<Field name="RETSOVALFLAG" size="1" type="string" />
<Field name="ORDEREDBY" size="40" type="string" />
<Field name="RECIEVEDBY" size="20" type="string" />
<Field name="REQUESTDATE" size="10" type="string" />
<Field name="RECIEVEDDATE" size="10" type="string" />
<Field name="CREATEDDATE" size="19" type="string" />
<Field name="EDITEDDATE" size="19" type="string" />
<Field name="SURROUNDINGCODE" size="4" type="string" />
<Field name="TRANSPORTATIONCODE" size="4" type="string" />
<Field name="FACILITYCODE" size="4" type="string" />
<Field name="MARKETABILITYCODE" size="4" type="string" />
<Field name="MAINTAINANCECONDITIONCODE" size="4" type="string" />
<Field name="VALUATIONTYPE" size="10" type="string" />
<Field name="VALUATIONSEQNO" size="2" type="string" />
<Field name="PRINTFLAG" size="1" type="string" />
<Field name="REASONCODE" size="20" type="string" />
<Field name="VALUTIONREPORTTYPE" size="5" type="string" />
<Field name="VALUATIONTASK" size="5" type="string" />
<Field name="PROPERTYRISKTYPECODE" size="4" type="string" />
<Field name="VALUATIONCATEGORY" size="4" type="string" />
<Field name="VERSION" size="20" type="string" />
<Field name="FINALVALUATIONFLAG" size="1" type="string" />
<Field name="LEASETEARMEXPYEAR" size="4" type="string" />
<Field name="APPLICATIONFORM" size="1" type="string" />
<Field name="CREATEDBY" size="15" type="string" />
<Field name="EDITEDBY" size="15" type="string" />
</Record>
</Metadata>
<Metadata id="footerMeta">
<Record name="footer" recordDelimiter="\r\n" recordSize="-1" type="fixed">
<Field name="TRAILER" size="1" type="string"/>
<Field name="CHECKSUM" size="16" type="string"/>
<Field name="HASHCOUNT" size="27" type="string"/>
</Record>
</Metadata>
<Metadata id="headerMeta" previewAttachmentCharset="ISO-8859-1">
<Record name="header" previewAttachmentCharset="ISO-8859-1" recordDelimiter="\r\n" recordSize="-1" type="fixed">
<Field name="HEADER" size="1" type="string"/>
<Field name="SYSTEM" size="10" type="string"/>
<Field name="COUNTRY" size="2" type="string"/>
<Field name="FILENAME" size="50" type="string"/>
<Field format="yyyy-MM-dd" name="SYSDATE" size="10" type="date"/>
</Record>
</Metadata>

<Connection dbConfig="${coshome}/config/download/conn/${country}.oracle.cfg" id="JDBC0" type="JDBC"/>
</Global>
<Phase number="1">
<Node dbConnection="JDBC0" id="INPUT1" sqlQuery="SELECT RPAD('H',1), RPAD('COS',10),RPAD('${header}',2),RPAD('cos_legalvalu_valu_dtls_dy.dat',50),RPAD(TO_CHAR(SYSDATE,'YYYY-MM-DD'),10) FROM DUAL " type="DB_INPUT_TABLE" />
<Node append="false" fileURL="${coshome}/${country}/download/pdw/cos_legalvalu_valu_dtls_dy.dat" id="OUTPUT1" type="FIXLEN_DATA_WRITER"/>
<Edge fromNode="INPUT1:0" id="INEDGE1" metadata="headerMeta" toNode="OUTPUT1:0" />
</Phase>
<Phase number="2">
<Node dbConnection="JDBC0" id="INPUT2" sqlQuery="SELECT RPAD(NVL(TO_CHAR(APPREFID),' '),16),
RPAD(NVL(TO_CHAR(PROPSEQNO),' '),2),
RPAD(NVL(TO_CHAR(PROMOTIONCODE),' '),16),
RPAD(NVL(TO_CHAR(PRODDEFCODE),' '),4),
RPAD(NVL(TO_CHAR(VALUERCODE),' '),5),
RPAD(NVL(TO_CHAR(VALUATIONSTATUS),' '),4),
RPAD(NVL(TO_CHAR(VALUATIONDATE,'YYYY-MM-DD'),' '),10),
RPAD(NVL(TO_CHAR(VALUATIONREFNO),' '),16),
RPAD(NVL(TO_CHAR(OCCUPANCYTYPE),' '),4),
RPAD(NVL(TO_CHAR(PURCHASETYPECODE),' '),4),
RPAD(NVL(TO_CHAR(YEAROFCOMPLETION),' '),4),
RPAD(NVL(TO_CHAR(AGEOFPROPERTY),' '),3),
RPAD(NVL(TO_CHAR(GROSSAREA),' '),5),
RPAD(NVL(TO_CHAR(NETAREA),' '),5),
RPAD(NVL(TO_CHAR(GARDENFLAG),' '),1),
RPAD(NVL(TO_CHAR(FLATROOFFLAG),' '),1),
RPAD(NVL(TO_CHAR(PURCHASEPRICE),' '),19),
RPAD(NVL(TO_CHAR(FORMALVALUATIONPRICE),' '),19),
RPAD(NVL(TO_CHAR(OPENMARKETVALUE),' '),19),
RPAD(NVL(TO_CHAR(FORCEDSALEVALUE),' '),19),
RPAD(NVL(TO_CHAR(INSURANCEVALUE),' '),19),
RPAD(NVL(TO_CHAR(FORMALVALUATIONGFA),' '),19),
RPAD(NVL(TO_CHAR(OPENMARKETVALUEGFA),' '),19),
RPAD(NVL(TO_CHAR(FORCEDSALEVALUEGFA),' '),19),
RPAD(NVL(TO_CHAR(INSURANCEVALUEGFA),' '),19),
RPAD(NVL(TO_CHAR(FORMALVALUENETAREA),' '),19),
RPAD(NVL(TO_CHAR(OPENMARKETVALUENETAREA),' '),19),
RPAD(NVL(TO_CHAR(FORCEDSALEVALUENETAREA),' '),19),
RPAD(NVL(TO_CHAR(INSURANCEVALUENETAREA),' '),19),
RPAD(NVL(TO_CHAR(VALUATIONFEE),' '),19),
RPAD(NVL(TO_CHAR(RETSOVALFLAG),' '),1),
RPAD(NVL(TO_CHAR(ORDEREDBY),' '),40),
RPAD(NVL(TO_CHAR(RECEIVEDBY),' '),20),
RPAD(NVL(TO_CHAR(REQUESTDATE,'YYYY-MM-DD'),' '),10),
RPAD(NVL(TO_CHAR(RECEIVEDDATE,'YYYY-MM-DD'),' '),10),
RPAD(NVL(TO_CHAR(CREATEDDATE,'YYYY-MM-DD HH:MI:SS'),' '),19),
RPAD(NVL(TO_CHAR(EDITEDDATE,'YYYY-MM-DD HH:MI:SS'),' '),19),
RPAD(NVL(TO_CHAR(SURROUNDINGCODE),' '),4),
RPAD(NVL(TO_CHAR(TRANSPORTATIONCODE),' '),4),
RPAD(NVL(TO_CHAR(FACILITYCODE),' '),4),
RPAD(NVL(TO_CHAR(MARKETABILITYCODE),' '),4),
RPAD(NVL(TO_CHAR(MAINTAINANCECONDITIONCODE),' '),4),
RPAD(NVL(TO_CHAR(VALUATIONTYPE),' '),10),
RPAD(NVL(TO_CHAR(VALUATIONSEQNO),' '),2),
RPAD(NVL(TO_CHAR(PRINTFLAG),' '),1),
RPAD(NVL(TO_CHAR(REASONCODE),' '),20),
RPAD(NVL(TO_CHAR(VALUTIONREPORTTYPE),' '),5),
RPAD(NVL(TO_CHAR(VALUATIONTASK),' '),5),
RPAD(NVL(TO_CHAR((SELECT (CASE WHEN PROP.PROPERTTYPECODE = '8' THEN '04' ELSE DECODE(DEV.BLUECHIPPROPERTY,'Y','01','03') END) FROM COS_MORT_PROPERTY_DTLS PROP, COS_MAS_DEVELOPMENT DEV WHERE PROP.DEVELOPMENTCODE = DEV.DEVELOPMENTCODE AND PROP.APPREFID = VALUATION.APPREFID)),' '),4) PROPERTYRISK,
RPAD(NVL(TO_CHAR(VALUATIONCATEGORY),' '),4),
RPAD(NVL(TO_CHAR(VERSION),' '),20),
RPAD(NVL(TO_CHAR(FINALVALUATIONFLAG),' '),1),
RPAD(NVL(TO_CHAR(LEASETERMEXPYEAR),' '),4),
RPAD(NVL(TO_CHAR(APPLICATIONFORM),' '),1),
RPAD(NVL(TO_CHAR(' '),' '),15),
RPAD(NVL(TO_CHAR(EDITEDBY),' '),15)
FROM COS_VALUATION_DTLS VALUATION
WHERE (VALUATION.CREATEDDATE > date)" type="DB_INPUT_TABLE"/>
<Node charset="UTF-8" append="true" fileURL="${coshome}/${country}/download/pdw/cos_legalvalu_valu_dtls_dy.dat" id="OUTPUT2" type="DELIMITED_DATA_WRITER"/>
<Edge fromNode="INPUT2:0" id="INEDGE2" metadata="bodyMeta" toNode="OUTPUT2:0" />
</Phase>
<Phase number="3">
<Node dbConnection="JDBC0" id="INPUT3" sqlQuery="SELECT RPAD('T',1), LPAD(to_char(COUNT(*)),16), LPAD(NVL(SUM(PropSeqNo),'0'),27) FROM COS_VALUATION_DTLS WHERE (CREATEDDATE > (select max(startdate) from cos_host_interface where filename='cos_legalvalu_valu_dtls_dy.dat' and startdate not in (select max(startdate) from cos_host_interface where filename='cos_legalvalu_valu_dtls_dy.dat') )) or (editeddate is not null and editeddate>(select max(startdate) from cos_host_interface where filename='cos_legalvalu_valu_dtls_dy.dat' and startdate not in (select max(startdate) from cos_host_interface where filename='cos_legalvalu_valu_dtls_dy.dat') ))" type="DB_INPUT_TABLE"/>
<Node charset="UTF-8" append="true" fileURL="${coshome}/${country}/download/pdw/cos_legalvalu_valu_dtls_dy.dat" id="OUTPUT3" type="FIXLEN_DATA_WRITER"/>
<Edge fromNode="INPUT3:0" id="INEDGE3" metadata="footerMeta" toNode="OUTPUT3:0" />
</Phase>
</Graph>



Thanks and regards,

Vijay Murali

Comments 2

Sort by Date Votes
  • Avatar
    slechtaj
    0
    Comment actions Permalink
    Hi Vijay,

    The functionality you are looking for is a part of commercial version of CloverETL. The component designed for such purposes is DBJoin (receives data from incoming edge, use them to join with data in a database). However, there is a workaround you might take into consideration. You can prepare the query in another component and send the whole query over the edge into DBInput.
  • Avatar
    slechtaj
    0
    Comment actions Permalink
    Above suggested workaround could be done as follows:

    • Prepare the whole query in preceding component and assign it to output record.

    • In DBOutputTable enter the following:

    port:$0.<QueryFiledName>:discrete

    For more information about DBOutputTable please refer to: http://doc.cloveretl.com/documentation/UserGuide/index.jsp?topic=/com.cloveretl.gui.docs/docs/index.html

Please sign in to leave a comment.

Didn't find what you were looking for?

New post