Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
how to do an ISPF F P'.' in a DB2 query? 1
- Thread starter Crox
- Start date
Nice question Crox, and one that I don't think there is a solution for in straight SQL. The p'.' function in ISPF covers invalid characters which is (I think) basically anything that's not on your keyboard, or in other words some sort of hex value.
You are therefore looking to search through a CHAR column character by character and test to see if that character is not in ('A','B','C' etc.) or to see if that character is of the hex value '01', '02' etc.
I've considered the CHR and ASCII functions and got nowhere and looked at STRIP TRANSLATE etc. but haven't been able to come up with a solution. I'm not saying it can't be done, but it's beaten me I'm afraid.
You might try posting in the ANSI SQL forum as there's some members in there who I'm sure think in SQL!
Alternatively, you could run a query to get the data, export it to TSO and run an edit macro against it!
Sorry to not have been much help.
Marc
You are therefore looking to search through a CHAR column character by character and test to see if that character is not in ('A','B','C' etc.) or to see if that character is of the hex value '01', '02' etc.
I've considered the CHR and ASCII functions and got nowhere and looked at STRIP TRANSLATE etc. but haven't been able to come up with a solution. I'm not saying it can't be done, but it's beaten me I'm afraid.
You might try posting in the ANSI SQL forum as there's some members in there who I'm sure think in SQL!
Alternatively, you could run a query to get the data, export it to TSO and run an edit macro against it!
Sorry to not have been much help.
Marc
- Thread starter
- #3
Hi Marc,
Most of the time you come up with something good so don't bother...
An edit macro in the batch or some smart usage of the sort utility can help perhaps.... That would also help and be good enough. I don't know if the p'.' search can be done in the batch with an ispf edit macro... never used it that way.
Regards,
Crox
Most of the time you come up with something good so don't bother...
An edit macro in the batch or some smart usage of the sort utility can help perhaps.... That would also help and be good enough. I don't know if the p'.' search can be done in the batch with an ispf edit macro... never used it that way.
Regards,
Crox
-
1
- #4
Truusvlugindewind
Programmer
Hai Crox
I'd go for a CTE with recursive SQL. Just build a query which searches for all printable characters. When your finished, just reverse the condition, or not
Are you well? It's been a while.
I'd go for a CTE with recursive SQL. Just build a query which searches for all printable characters. When your finished, just reverse the condition, or not
Are you well? It's been a while.
- Thread starter
- #5
Hi Truus, yes it's been a while. Everything almost normal... Still not a multimiljonaire so yes, I suffer a lot.....
Thanks for your reply but I realy don't know what you mean.. can you 'speak' some code?
A private message about work would be nice....
Regards,
Crox
Still not a multimiljonaire so yes, I suffer a lot..... Thanks for your reply but I realy don't know what you mean.. can you 'speak' some code?
A private message about work would be nice....
Regards,
Crox
sathyarams
IS-IT--Management
Crox, Just curious. Did you find any SQL solution for this? Do you mind sharing it if you did. Thx
For db2 resoruces visit More DB2 questions answered at &
For db2 resoruces visit More DB2 questions answered at &
- Thread starter
- #7
hi, I did find some solutions, but it didn't seem suitable for the job because it would cost too much cpu.
Some solutions people send on some other sites (mvshelp / mvsforums) are useable for a small amount of data. The convert construction you can find there, is ok.
regards,
Crox
Some solutions people send on some other sites (mvshelp / mvsforums) are useable for a small amount of data. The convert construction you can find there, is ok.
regards,
Crox
- Status