Help in arriving at a logic

karthik3883 · New User Joined: 19 Oct 2007 Posts: 37 Location: chennai

Hi,

I require a help in arriving at a logic. my input file contains list like below and number of items is not fixed

BBRM
UB
UK
LCB

and i want to make this as ('BBRM','UB','UK','LCB") where condition.

I can able to do this in sort and i want to know how can we conclude it is a last record and finish with close paranthesis ')'

Regards
Karthik

Pandora-Box · Posted: Wed Jun 19, 2013 9:23 am

You always want them as single record?

or is Multiple record ok for you ?

karthik3883 · New User Joined: 19 Oct 2007 Posts: 37 Location: chennai

Hi I want them as a single record so that it can be used in where condition

Pandora-Box · Posted: Wed Jun 19, 2013 10:05 am

ok on a regular basis how many records do you expect in Input file
What is the Maximum and minimum length of records?

FYI query also works if where clause is in multiple records

And sort product is?

karthik3883 · New User Joined: 19 Oct 2007 Posts: 37 Location: chennai

the number of records is not fixed ,it may vary from month to month

Arun Raj · Posted: Wed Jun 19, 2013 11:53 am

karthik3883 · New User Joined: 19 Oct 2007 Posts: 37 Location: chennai

Hi Arun,

Thank you ,

Actually we are doing a FTP from a file,normally a .txt which contains details like

aaaa
vvvv
ccccc

and wont contain trailer

i have to convert this to ('aaaa','vvv','ccccc);

please help me if there is any way to achieve this

Pandora-Box · Posted: Wed Jun 19, 2013 12:52 pm

karthik,

I would strictly suggest you to read the manuals so that you could understand Arun's hints or pointers

Arun Raj · Posted: Wed Jun 19, 2013 2:36 pm

karthik3883,

If you are to use your output to build a "dynamic" SQL WHERE condition, it does not matter if you have the values in a single record or in multiple records. In fact it is pretty much easier to write it in multiple records.

My suggestion was to use the OUTFIL BUILD to format your data records which in turn will write the actual search values in your WHERE clause. And use the HEADER1 and TRAILER1 parameters in the OUTFIL to write the open and close parantheses respectively, but the last value in the WHERE should not have a comma suffixed to it and hence the trick with the TRAILER1 with dummy-end value.

mistah kurtz · Posted: Wed Jun 19, 2013 5:47 pm

Hi karthik3883,

You can achieve what Arun has suggested using this SORT job, assuming that you have DFSORT and maximum length of data is 10.

dick scherrer · Posted: Wed Jun 19, 2013 7:03 pm

Hello,

Need a help
may be the least useful subject posted in a long time . . .

Pretty much everyone who starts a topic is looking for some kind of help . . .

Please use meaningful topic/subject.

Bill Woodger · Posted: Thu Jun 20, 2013 4:19 am

As has been pointed out already, this is much more sensible to do over several lines.

Borrowing a Smart Trick, here is a DATASORT solution.

Yes, then does mean sorting the file, but since it is values for a WHERE clause, I'm assuming there aren't going to be hundreds of thousands of input records.

DATASORT allows you to specify "header records" (FIRST(n)) and "trailer records" (LAST(n)) which retain their original relative positions in the file whilst everything in between is sorted.

With DATASORT you get OPTION EQUALS for nothing. So the "data" can be sorted on the first byte (which by that time is always a single-quote) and the records even stay in the same order. The "non-trailer" records get a comma after the closing quote, the "trailer" record does not.