RE: Dynamic text truncation query

RE: Dynamic text truncation query

 

  

Unfortunately not.
I've slimmed the query down now to this

Select STRP(VIEWNAME,'_') View ,
Locate('SELECT',text) Start_Pos,
LENGTH(text) - Locate('SELECT',text) Remainder,
Locate('flight_stat',text) End_Pos,
SubsTR(text, 86, 115 ) Hard_code,
SubsTR(VARCHAR(text), Locate('SELECT',text), LENGTH(text) - Locate('SELECT',text) ) Dynamic#1,
SubsTR(VARCHAR(text), Locate('SELECT',text), Locate('flight_stat',text) ) Dynamic#2
From Syscat.Views
Where viewname = 'FLIGHT_STATS_VIEW'
;

If you DESCRIBE this query Start_Pos, Remainder and End_Pos are all INTEGER.
The result returned is
VIEW START_POS REMAINDER END_POS HARD_CODE
FLIGHT_STATS_VIEW 86 115 190 SELECT flight_year, Minutes_to_HHMM(flight_time), flights, Minutes_to_HHMM(flight_time / flights) from flight_stat

and then the two strings for Dynamic#1 and Dynamic#2, both 32672 VarChars rather than the truncated string.
Note that the dynamic integer supplied to the SUBSTR as a start position is working; the full text in the text column of the SYSCAT.VIEWS table is
Create View Flight_Stats_View ( year, flight_time, flights, average_flight_time ) as SELECT flight_year, Minutes_to_HHMM(flight_time), flights, Minutes_to_HHMM(flight_time / flights) from flight_stats

Bottom line appears to be that a dynamically supplied integer will work for the first positional parameter of SUBSTR, but not the second.

-----Original Message-----
From: Praveen Chengalathu
[mailto:db2udbdba-ezmlmshield-x73772374.[Email address protected]
Sent: 30 October 2006 16:32
To: LazyDBA Discussion
Subject: RE: Dynamic text truncation query


The issue here appears to be length(text)..

instead of this if you use another locate to find the end of the string( provided you have an ending string delimiter) that should help the substring..

SUBSTR(text, Locate('SELECT',text), LOCATE(';',text))

Praveen

-----Original Message-----
From: Gillis Mark
[mailto:db2udbdba-ezmlmshield-x77808269.[Email address protected]
Sent: 30 October 2006 15:25
To: LazyDBA Discussion
Subject: RE: Dynamic text truncation query


Unfortunately CAST gets rejected as the source text is a 2MB CLOB. I did try using VARCHAR(text) instead of text but the results are the same.

Thanks for trying though !-)

-----Original Message-----
From: Praveen Chengalathu
[mailto:db2udbdba-ezmlmshield-x22596313.[Email address protected]
Sent: 30 October 2006 15:14
To: LazyDBA Discussion
Subject: RE: Dynamic text truncation query


Mark,

I think it should work fine if you use CAST(text) AS VARCHAR..(provided your text length fits within VARCHAR max char limit, 32K.

Sorry I am lazy.. :-)

Praveen




-----Original Message-----
From: Gillis Mark
[mailto:db2udbdba-ezmlmshield-x6260476.[Email address protected]
Sent: 30 October 2006 15:02
To: LazyDBA Discussion
Subject: Dynamic text truncation query


Gurus

I've been looking at this for a couple of hours so I'm not being entirely lazy. I've written some SQL that I was hoping would truncate a text string dynamically. The example below shows what I'm trying to do, but basically it's find a value in the string and then substring FROM that value, to the end of the data string. So if you want to dig out just the query bit from the SYSCAT.VIEWS table TEXT column you 'should' be able to select

SUBSTR(text, Locate('SELECT',text), LENGTH(text) - Locate('SELECT',text) )

and (for one of my simple views) the effect should be identical to saying

SUBSTR(text, 86, 115 )

What is odd here is that the start point is working, but the length bit isn't so doing a DESCRIBE on the 2 statements shows the last one is CLOB 115 and the first, dynamic one, is CLOB 2097152. Sure enough the row coming back is huge with the dynamic length option. BUT it has successfully cut out all the text preceding the first SELECT statement.

Any thoughts / advice?

Cheers

Mark


This e-mail (and any attachments) may contain privileged and/or confidential information. If you are not the intended recipient please do not disclose, copy, distribute, disseminate or take any action in reliance on it. If you have received this message in error please reply and tell us and then delete it. Should you wish to communicate with us by e-mail we cannot guarantee the security of any data outside our own computer systems. For the protection of Legal & General's systems and staff, incoming emails will be automatically scanned.

Any information contained in this message may be subject to applicable terms and conditions and must not be construed as giving investment advice within or outside the United Kingdom.

The following companies are subsidiary companies of the Legal & General Group Plc which are authorised and regulated by the Financial Services Authority for advising and arranging the products shown: Legal & General Partnership Services Limited (insurance and mortgages), Legal & General Insurance Limited (insurance), Legal & General Assurance Society Limited
(life assurance, pensions and investments), Legal & General Unit Trust Managers Limited and Legal & General Portfolio Management Services Limited (investments).

They are registered in England under numbers shown.
The registered office is Temple Court, 11 Queen Victoria Street, London EC4N 4TP.

Legal & General Partnership Services Limited: 5045000 Legal & General Assurance Society Limited: 166055 Legal & General (Unit Trust Managers) Limited: 1009418 Legal & General (Portfolio Management Services) Limited: 2457525 Legal & General Insurance Limited: 423930

They are registered with the Financial Services Authority under numbers shown. You can check this at www.fsa.gov.uk/register

Legal & General Partnership Services Limited: 300792 Legal & General Assurance Society Limited: 117659 Legal & General (Unit Trust Managers) Limited: 119273 Legal & General (Portfolio Management Services) Limited: 146786 Legal & General Insurance Limited: 202050



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




This email and any files transmitted with it are confidential and are intended solely for the use of the individual or entity to whom they are addressed. This communication represents the originator's personal views and opinions, which do not necessarily reflect those of HPI Limited. If you are not the original recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error, and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you received this email in error, please immediately notify [Email address protected] message has been scanned by Anti-Virus. Recipients are advised to apply their own virus checks to this message on delivery.



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




This email and any files transmitted with it are confidential and are intended solely for the use of the individual or entity to whom they are addressed. This communication represents the originator's personal views and opinions, which do not necessarily reflect those of HPI Limited. If you are not the original recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error, and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you received this email in error, please immediately notify [Email address protected] message has been scanned by Anti-Virus. Recipients are advised to apply their own virus checks to this message on delivery.



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

DB2 & UDB email list listserv db2-l LazyDBA home page