DSXchange

Hi, Thanks for the response. The query is not commented out. I just gave two hyphens before the query. SELECT TABLE_ID, CC_ID, CC_TEST.DERIVE(TN||T_CODE) AS CC_ID_NEW FROM VEN_DETAILS WHERE REGION IS NOT NULL AND (NVL(CC_ID,0) <> NVL(DERIVE(TN||T_CODE),0)) The error was "ORA-00904 string: inval...

Hi, I am calling a SQL query which has a FUNCTION (DERIVE) in the Oralce OCI stage (Custom SQL statement). The query is running fine from command line. -- SELECT TABLE_ID, CC_ID, CC_TEST.DERIVE(TN||T_CODE) AS CC_ID_NEW FROM VEN_DETAILS WHERE REGION IS NOT NULL AND (NVL(CC_ID,0) <> NVL(DERIVE(TN||T_C...

Hi Ray,

Thank You!

It did work...after a long struggle.
I used: Field(StageVar3,'|',2,1) which derived me BBB.

Now, I see how can i get the desired output. Will keep you all posted on the final result.

Thanks.
Kiran Kumar

Thanks, James.

This is working.
I'm trying to find how can we read just the string "BBB" out of it.

Any help!

In general, is there a way to read the value before a comma and after a comma separately?

Yes, James.

I'm trying to figure it out. Its failing all the time

Inline Field() functions, perhaps. First use "," as the delimiter and then take the second "|" delimited field from the result. ... Craig, I tried this option as - Field(Result,'|',2). This is returning "5/24/11,CCC" I think this is wrong. Per the need it should have r...

Thanks, Ray & Mobashshar

It worked...
However, can you please suggest how to read just the date value from the entire record "AAA|1/05/09,BBB|5/24/11,CCC|2/22/12"

Like just the 1/5/09 - 5/24/11 - 2/22/12 from the entire string.
The required output is used in another column.

Hi, I am facing an issue - can you please help. KEY DESC DATE 123 AAA 1/05/09 123 BBB 5/24/11 123 CCC 2/22/12 452 AA 5/2/04 452 BBB 3/6/06 631 AAAA 5/6/10 I have sorted the data on KEY and obtained as below 123 AAA|1/05/09,BBB|5/24/11,CCC|2/22/12 452 AA|5/2/04,BBB|3/6/06 631 AAAA|5/6/10 I am struck ...

Sorry, Craig.

My Mistake. The issue is resolved.

It worked. But, I am unable to trap the dynamic values.

can you please share any clue?

Your Field() function call is syntactically correct. Is your target a sequential file? If so, then you need to use CHAR(10) as the record delimiter. If not, you need to give us a better explanation of exactly what it is you are trying to accomplish, i.e. what your requirement is. My Target is an Or...

Hi, This actually worked with a Field function. there was some issue with the delimiter I'm passing in and I corrected it. Now, I have another issue in-line with this. the RECORD values varies and it has a comma as a delimiter as rec1,rec2,rec3,rec4.....and so on. Can you please suggest how can i pu...

I am reading records and if it matches a condition I am dumping them in a single column (which is as above) with a comma separated in between them.

Hi, I have a requirement to separate out record which has a comma in it. RECORD: rec1,rec2,rec3 the output I need be rec1 rec2 rec3 I tried to read the RECORD using filed function as Field(RECORD,',',1):CHAR(13):Field(RECORD,',',2):CHAR(13):Field(RECORD,',',3) And the server is LINUX But, I am unabl...