trimming field value

[John192]

Ok, so now how to resolve this 'syntax error like 'token <end of statement> was not valid valid tokens 0 ,'


Thanks

[MFisher]

i am getting syntax error like 'token <end of statement> was not valid valid tokens 0 ,

i tried this
CREATE VIEW QTEMP/FILE3 > ( FLD1 , FLD2 , FLD3 , FLD4 ) AS > ( SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2 > FROM QTEMP/FILE1 a > JOIN QTEMP/FILE2 b > ON on A.FLD1 = INT(NULLIF(REGEXP_REPLACE(B.FLD1,'[^0-9]',''),''))

Thanks

What is the purpose of the '>' in your code ?

[John192]

it did not appear here when I copied it from my last (post #10) adding that suggested regular expression may be because I missed to use code blocks for this SQL query :-

[code ]


CREATE VIEW mylib/FILE3 ( FLD1 , FLD2 , FLD3 , FLD4 ) AS ( SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2) FROM LIBa/FILE1 a JOIN LIBb/FILE2 b ON
A.FLD1 = INT(NULLIF(REGEXP_REPLACE(B.FLD1,'[^0-9]',''),''))

[/code]


I am using above SQL query for join in my SQL/400 green screen ,and getting that error -'syntax error like 'token <end of statement> was not valid valid tokens 0 ,'




Thanks

[MFisher]

You changed your parentheses from the post 10 version.

[John192]

Ok, thanks now this view file3 got created in my library but when I ran sql query select * from mylib/file3 I am getting results like:-

FLD1 (File1) FLD1(File2)

12 ABCDE012

5,678 XY005678


1 001 J


1 001

1 ABCDEFG1


So though matching numeric number from field1 of file 2 is getting displayed here but again with the unwanted text along with it like in above examples (for 5678 - XY is unwanted and for 12 that ABCDE is unwanted and for 1 those 5 blanks between 001 and J are unwanted) is getting displayed in the Query result on the screen.



Thanks

[MFisher]

When you create a View, do you give it a name that identifies that it is a view, and not a file ? Such a VIEW3 ?
It is confusing to have you refer to file3, that is a view.

[John192]

yes i created it as a view only but in my own library.

[MFisher]

I am active on a SQL Server forum. They are pretty insistent that when this type of troubleshooting help is requested, the requester post the File creation statements, and sample data insert statements, so that others can easily recreate the same scenario by copy-pasting into their own system and all looking at and working with the same data.

Try to visualize and write your posts from the perspective of someone not familiar with your files and data.
That also includes formatting your posts so the code and data are easy to read.

[MFisher]

Isn't it selecting the fields that you asked for?

SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2

[John192]

File1 ( have field1 let's say it's name is X - Data type Decimal length 6, it has other fields as well but we are currently not bothered about those fields)

file2( have fiield1 let's say it's name is Y- Data type is Character with 9 length - it too have other fields in this file but currently we are not bothered about those fields)


Just for example some dummy Data in File1's field X:-

1) 2044
2) 32,018
3)106



Just for example some dummy Data in File2's field Y:-

*
*B
*END
001 F
001 V
*
*
ZC057882
ZD071304
ZW002711
ABCDE


So the requirement is to join these two fields ( of these two files) in such a way that for that the number( whose max length is 6 and data type is decimal) in this field X we have to find it's corresponding match in Field Y ( of file2) provided when we search a match in Field Y ( of file2) then we should extract only the equivalent number part from file2's field Y.

So If I get below kind of records :-

field X field Y

2044 000002044 ----it's fine.
2044 00space2044 ---it's not fine
2044 2044ABCDE ---it's not fine



Thanks

[John192]

Isn't it selecting the fields that you asked for?

SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2

it does select but I don't get the result which I want as I explained it in my post #25



Thanks

[MFisher]

Your SELECT asks for the fields as they are in the file. If you want modified results, modify your SELECT.

[John192]

i have modified it even though it does not display the matching records the way i want as explained in post #25

thanks

[John192]

for this i meant to say for my post #20 though i was able to view fields as per my selection but as i showed it's not showing the data in acceptable format i mean it showed like mentioned earlier -FLD1 (File1) FLD1(File2)

12 ABCDE012

5,678 XY005678

which is not acceptable as per explanation in my post #25

thanks

[MFisher]

You have a simple SELECT of the fields. Modify the SELECT to present the fields in the format you want.

[John192]

How to modify to get desired result so that i just get records matching as per explanation in my Post #25

Thanks