[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: [sqr-users] SQR members- suggestion needed for a code change - Rajesh
Yup, you had indicated to me earlier that there is no need for the
conversions I am doing but this is a very large module and the variables are
used elsewhere and in a different way. So, I should not touch any other
thing than what is needed i.e to take care of the phone-numbers and
area-code to make the process run properly.
But, yeah you gave a great suggestion and will keep that in mind in future
implementations. And Dan, I can use your code snippet for a lot of stuff
that need to be done over here.
Thanks,
Rajesh.
On Thu, Nov 19, 2009 at 10:06 AM, Barry E. Cohen <bcohen@univision.net>wrote:
> Glad to have been a help Rajesh,
>
> As an FYI, there is no need to give an alias in the select statement, to
> your selected values, if your not doing any processing in the select. Or
> even move those values to a variable. They can be referenced through-out the
> program as they were selected, unless of course you're going to be using
> them in various sqc procedures, then moving to variables can make things
> easier. Also because you did an nvl() in your select statement with the
> area_code, and phone_nbr, you will get a value there '0' even if none was
> stored in the DB, so EVERY phone number will have a look-up done, even if
> none was stored in the DB. I also really liked Don's translate, as it really
> takes care of everything not numeric, that may be stored within the
> phone_nbr field. I would have written it like the below
>
> begin-procedure get_temp_clients
> begin-select
> tci_crs_id
> tci_client_name_mod
> cpn_area_code
> cpn_phone_nbr
> tci_pr_client_nbr
> tci_client_branch_nbr
> nvl(tci_fed_id,'NO FED') &fed_id
>
> let $remove_chars = translate(&cpn_area_code, '0123456789', '')
> let $new_area_code = translate(&cpn_area_code, $remove_chars, '')
>
> let $remove_chars = translate(&cpn_phone_nbr, '0123456789', '')
> let $new_phone_nbr = translate(&cpn_phone_nbr, $remove_chars, '')
>
> if length($new_area_code) > 0 and length($new_phone_nbr) > 0
> do get_full_phone_match
> end-if
> if length($new_phone_nbr) > 0
> do get_phone_nbr_match
> end-if
>
> end-select
> end-procedure
>
>
> _____________________
> Barry E. Cohen
> Senior Programmer Analyst
> Univision HR and Financial Systems
>
> Office: 201-287-4287
>
> Please consider the environment before printing this email
>
> -----Original Message-----
> From: sqr-users-bounces+bcohen=univision.net@sqrug.org [mailto:
> sqr-users-bounces+bcohen <sqr-users-bounces%2Bbcohen>=univision.net@
> sqrug.org] On Behalf Of Rajesh Subramaniam
> Sent: Thursday, November 19, 2009 9:19 AM
> To: This list is for discussion about the SQR database reporting language
> from Hyperion Solutions.
> Subject: Re: [sqr-users] SQR members- suggestion needed for a code change -
> Rajesh
>
> Hi guys,
>
> Thanks for your input on this. I got so many ideas and at last implemented
> Barry's idea of using the translation function.
>
> I added this to the code along with other changes I did and ran the file
> which takes 5 hours to finish execution. The run was successful.
>
> Thanks again!!!
>
> Code Snippet of the change I did using tranlsation()-
>
> begin-procedure get_temp_clients
> begin-select
> tci_crs_id &crs_id
> tci_client_name_mod &client_name
> nvl(cpn_area_code,0) &area_code
> nvl(cpn_phone_nbr,0) &phone_nbr
> tci_pr_client_nbr &client_nbr
> tci_client_branch_nbr &branch_nbr
> nvl(tci_fed_id,'NO FED') &fed_id
>
> move &crs_id to #crs_id
> move &client_name to $client_name
> move &area_code to $area_code
> move &phone_nbr to $phone_nbr
> move &client_nbr to $client_nbr
> move &branch_nbr to $branch_nbr
> move &fed_id to $fed_id
>
> let $new_area_code = translate($area_code, '()-/.#~*_ ', '')
> let $new_phone_nbr = translate($phone_nbr, '()-/.#~*_ ', '')
> if length($new_area_code) > 0 and length($new_phone_nbr) > 0
> do get_full_phone_match
> end-if
> if length($new_phone_nbr) > 0
> do get_phone_nbr_match
> end-if
>
> end-select
> end-procedure
>
> -Rajesh.
>
> On Wed, Nov 18, 2009 at 4:09 PM, Don Mellen <donm@ontko.com> wrote:
>
> > You can also genericize the translate to do any string of numbers that
> > might also contain text, such as...
> >
> > begin-procedure just_numbers(:$txt)
> > let $remove_chars = translate($txt, '0123456789', '') let $txt =
> > translate($txt, $remove_chars, '') end-procedure
> >
> >
> > On Wed, Nov 18, 2009 at 12:56 PM, Barry E. Cohen <bcohen@univision.net
> > >wrote:
> >
> > > I'd likely just run 'translate'...
> > >
> > > Let $phone_nbr = translate($phone_nbr, '()-/ ', '')
> > >
> > > That would eliminate any of the special characters your likely to
> > encounter
> > > in a phone number in one shot rather than having to repeat the
> > > process
> > with
> > > 'replace'.
> > >
> > >
> > > Next you could evaluate for length, to postulate on how much of the
> > number
> > > you have.
> > >
> > > Let #phone_len = length($phone_nbr)
> > > evaluate #phone_len
> > > when = 11
> > > parse accordingly
> > > break
> > > when = 10
> > > parse accordingly
> > > break
> > > when = 7
> > > parse accordingly
> > > break
> > > End-evaluate
> > >
> > > Ask 10 different programmers how to handle a situation, and you'll
> > > likely end up with 10 different answers...
> > >
> > > _____________________
> > > Barry E. Cohen
> > > Senior Programmer Analyst
> > > Univision HR and Financial Systems
> > >
> > > Office: 201-287-4287
> > >
> > > Please consider the environment before printing this email
> > >
> > > -----Original Message-----
> > > From: sqr-users-bounces+bcohen=univision.net@sqrug.org [mailto:
> > > sqr-users-bounces+bcohen <sqr-users-bounces%2Bbcohen>=univision.net@
> > > sqrug.org] On Behalf Of Simon, Jason (MBS)
> > > Sent: Wednesday, November 18, 2009 12:23 PM
> > > To: This list is for discussion about the SQR database
> > > reportinglanguage from Hyperion Solutions.
> > > Subject: RE: [sqr-users] SQR members- suggestion needed for a code
> > > change
> > -
> > > Rajesh
> > >
> > > There are probably many different ways to do this. But if I had to
> > format
> > > a phone number right now I would convert them to a string remove any
> > > characters by looping the string multiple times through the replace
> > function
> > > each time removing a non number item. Then I would use the edit to
> > > format the phone number to the format I wanted.
> > >
> > > Here is the help file for both of the functions I would use.
> > >
> > >
> > >
> > > REPLACE
> > > Inspects the contents of source_value and replaces all occurrences
> > > of from_string with to_string and returns the modified string.
> > >
> > > Syntax: dst_var = replace(source_value, from_string, to_string)
> > >
> > > source_value = date or text literal, column, variable, or expression
> > >
> > > from_string = text literal, column, variable, or expression
> > >
> > > to_string = text literal, column, variable, or expression
> > >
> > > dst_var = text variable
> > >
> > > Example: let $replaced = replace($paragraph, 'good', 'excellent')
> > >
> > >
> > >
> > > EDIT
> > >
> > > Formats source_value according to edit_mask and returns a string
> > containing
> > > the result.
> > >
> > > Syntax: dst_var = edit(source_value, edit_mask)
> > >
> > > source_value = Any literal, column, variable, or expression
> > >
> > > edit_mask = text literal, column, variable, or expression
> > >
> > > dst_var = text variable
> > >
> > > Example: let $phone = edit(&phone, '(xxx) xxx-xxxxx') let $price =
> > > edit(#price, '999.99') let $today = edit($date, 'DD/MM/YYYY')
> > >
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: sqr-users-bounces+jason.simon=mortgagefamily.com@sqrug.org
> > > [mailto:sqr-users-bounces+jason.simon<sqr-users-bounces%2Bjason.simon>
> > > <sqr-users-bounces%2Bjason.simon><sqr-users-bounces%2Bjason.simon>=
> > > mortgagefamily.com@sqrug.org] On Behalf Of Rajesh Subramaniam
> > > Sent: Wednesday, November 18, 2009 12:11 PM
> > > To: This list is for discussion about the SQR database reporting
> > > languagefrom Hyperion Solutions.
> > > Subject: Re: [sqr-users] SQR members- suggestion needed for a code
> > > change
> > -
> > > Rajesh
> > >
> > > Hi,
> > >
> > > Thanks for your suggestions, I tried implementing them and observed
> > > that,
> > >
> > > to_number() works fine even if the input is without single quotes
> > > i.e not
> > a
> > > string. This is for inputs that are numbers and do not have any
> > > special character or spaces between them.
> > >
> > > If there are special characters in between the numbers then a 0 is
> > > displayed if it is within single quotes or else gives an error message.
> > >
> > > For eg-
> > > Sample code-
> > >
> > > let $areacode = '3456'
> > > let $alphaNumericAreaCode = 'A3456'
> > > let $areacode1= 3456 // area code without single quotes i.e not a
> > string,
> > > just like the inputs in our program nssmatch.sqr !let
> > > $alphaNumericAreaCode1 = A3456 //alpha numeric area code without
> > > single quotes i.e not a string and got an error for this. So, I have
> > > commented
> > it.
> > >
> > > let $conv = to_number($areacode)
> > > let $conv1 = to_number($areacode1)
> > > let $alphanum = to_number($alphaNumericAreaCode) !let $alphanum1 =
> > > to_number($alphaNumericAreaCode1)
> > >
> > > display $conv
> > > display $conv1
> > > display $alphanum
> > > !display $alphanum1
> > >
> > > end-procedure
> > >
> > > OUTPUT-
> > > 3456.000000000000000
> > > 3456.000000000000000
> > > 0.000000000000000
> > >
> > > And you were right about just using the &variable in my if statement.
> > >
> > > So, yeah I found out how it works but I was just wondering whether
> > > there
> > is
> > > any other way to do this without using to_number().
> > >
> > > -Rajesh
> > > On Wed, Nov 18, 2009 at 10:20 AM, the dragon <ceprn@hotmail.com>
> wrote:
> > >
> > > >
> > > > The problem may stems from the fact that the phone number is not
> > > usually a
> > > > number column, but may contain a special character, such as a ., /
> > > > or
> > > - in
> > > > it. You can add a debug statement that shows the values being
> > > processed.
> > > > Additionally, you can use the &variable column variable in your
> > > > if statement, rather than changing it to a $character and then
> > > > back to a
> > > number
> > > > with to_number().
> > > >
> > > >
> > > >
> > > > Best bet, add a show of the variable before you get to the
> > > > statement
> > > that
> > > > blows up and see what you get.
> > > >
> > > >
> > > >
> > > > Also, nvl only catches null values, it does not catch spaces, and
> > > > if
> > > you're
> > > > using PeopleSoft, the database is full of records with single spaces.
> > > You
> > > > might want to check the database and determine the type of the
> > > > columns you're dealing with.
> > > >
> > > >
> > > >
> > > > peace,
> > > >
> > > > clark 'the dragon' willis
> > > >
> > > > PSA: Salary <> Slavery. If you earn a salary, your employer is
> > > > renting
> > > your
> > > > services for 40 hours a week, not purchasing your soul. Your time
> > > > is
> > > the
> > > > only real finite asset that you have, and once used it can never
> > > > be recovered, so don't waste it by giving it away.
> > > >
> > > > I work to live; I don't live to work.
> > > >
> > > > "Time is the coin of your life. It is the only coin you have, and
> > > > only you can determine how it will be spent. Be careful lest you
> > > > let other people spend it for you." -- Carl Sandburg (1878 - 1967)
> > > >
> > > > It is impossible to defeat an ignorant man in argument. -- William G.
> > > > McAdoo
> > > > Religion is regarded by the common people as true, by the wise as
> > > false,
> > > > and
> > > > by the rulers as useful. -- Seneca
> > > >
> > > > "I distrust those people who know so well what God wants them to
> > > > do
> > > because
> > > > I notice it always coincides with their own desires." - Susan B.
> > > Anthony
> > > >
> > > > "Does religion calm your fears of death?" "No, but death will calm
> > > > my
> > > fear
> > > > of religions."
> > > >
> > > >
> > > > NOTICE: The information contained in this message is protected
> > > > under United States and international copyright laws and is
> > > > intended for the
> > > personal
> > > > and
> > > > confidential use of the addressee. If you are not the addressee
> > > > or an agent of the addressee, you are hereby notified that you
> > > > have received this message in error and that any review,
> > > > dissemination, distribution,
> > > taking
> > > > of
> > > > any action in reliance upon or copying of this message without the
> > > express
> > > > written permission of the sender or his agent is strictly
> > > > prohibited
> > > and in
> > > > violation of referenced statutes. If you have received this
> > > > message
> > > in
> > > > error, please notify me (the sender) immediately and you are
> > > instructed to
> > > > delete this message from any computer it resides on, including
> > > > mail servers.
> > > >
> > > > SIGNATURE :
> > > > Do not meddle in the affairs of dragons, for you are crunchy and
> > > > taste
> > > good
> > > > with ketchup.
> > > > You've got to be a Spirit; don't need no Ghosts.
> > > > Do or Do Not; There is NO Try.
> > > > Any clod can have facts, but having an opinion is an art !
> > > > A conclusion is simply the place where someone got tired of thinking.
> > > > When a continuing state of defiance and disorder can not be
> > > > checked by
> > > the
> > > > rules, then new and sometimes drastic rules must be forged to
> > > > maintain order; the alternative is anarchy.
> > > > M. Clark Willis III - Sr Systems Analyst, Sr Consultant
> > > ceprn@hotmail.com
> > > > aolim:ceprn ICQ 6633136
> > > >
> > > > Help stamp out SPAM!! http://www.cauce.org The senders of
> > > > unsolicited commercial e-mail to me at any site are
> > > liable
> > > > for a $500 per message cost under USC 47, which may be found at
> > > > http://www.law.cornell.edu/uscode/47
> > > >
> > > > Contents of this message, in it's entirety, (c) 2000 - 2009 M.
> > > > Clark Willis III / Dragon Enterprises inc. All rights reserved.
> > > >
> > > >
> > > >
> > > > > Date: Wed, 18 Nov 2009 10:03:28 -0500
> > > > > From: epsilon11@gmail.com
> > > > > To: sqr-users@sqrug.org
> > > > > Subject: [sqr-users] SQR members- suggestion needed for a code
> > > change -
> > > > Rajesh
> > > > >
> > > > > Hi guys,
> > > > >
> > > > > In the below code, if I use to_number() on the area_code and
> > > > phone_number,
> > > > > the process that uses this piece of code ends in an error.
> > > > >
> > > > > To prevent this error due to the use of to_number(), I changed
> > > > > the
> > > if
> > > > > condition to check $area_code != 0 and $phone_nbr != 0 instead
> > > > > of
> > > > > to_number($area_code) > 0 and to_number($phone_nbr) > 0 i.e
> > > > > changed
> > > the
> > > > if
> > > > > condition in the last section of the code to-
> > > > >
> > > > > if $area_code != 0 and $phone_nbr != 0 do get_full_phone_match
> > > > > end-if This solves the issue, but is there any better way of
> > > > > doing this?
> > > Any
> > > > > suggestions would be greatly appreciated.
> > > > >
> > > > > Thanks,
> > > > > Rajesh.
> > > > >
> > > > > Code-
> > > > > _____
> > > > >
> > > > > begin-procedure get_temp_clients begin-select tci_crs_id &crs_id
> > > > > tci_client_name_mod &client_name
> > > > > nvl(cpn_area_code,0) &area_code
> > > > > nvl(cpn_phone_nbr,0) &phone_nbr
> > > > > tci_pr_client_nbr &client_nbr
> > > > > tci_client_branch_nbr &branch_nbr nvl(tci_fed_id,'NO FED')
> > > > > &fed_id
> > > > >
> > > > >
> > > > > move &crs_id to #crs_id
> > > > > move &client_name to $client_name move &area_code to $area_code
> > > > > move &phone_nbr to $phone_nbr move &client_nbr to $client_nbr
> > > > > move &branch_nbr to $branch_nbr move &fed_id to $fed_id if
> > > > > to_number($area_code) > 0 and to_number($phone_nbr) > 0 do
> > > > > get_full_phone_match end-if
> > > > >
> > > > > end-select
> > > > > end-procedure
> > > > >
> > > > > _______________________________________________
> > > > > sqr-users mailing list
> > > > > sqr-users@sqrug.org
> > > > > http://www.sqrug.org/mailman/listinfo/sqr-users
> > > >
> > > > _________________________________________________________________
> > > > Hotmail: Trusted email with powerful SPAM protection.
> > > > http://clk.atdmt.com/GBL/go/177141665/direct/01/
> > > >
> > > > _______________________________________________
> > > > sqr-users mailing list
> > > > sqr-users@sqrug.org
> > > > http://www.sqrug.org/mailman/listinfo/sqr-users
> > > >
> > >
> > > _______________________________________________
> > > sqr-users mailing list
> > > sqr-users@sqrug.org
> > > http://www.sqrug.org/mailman/listinfo/sqr-users
> > >
> > >
> > >
> > > _______________________________________________
> > > sqr-users mailing list
> > > sqr-users@sqrug.org
> > > http://www.sqrug.org/mailman/listinfo/sqr-users
> > >
> > > The information contained in this e-mail and any attached documents
> > > may be privileged, confidential and protected from disclosure. If
> > > you are not the intended recipient you may not read, copy,
> > > distribute or use this information. If you have received this
> > > communication in error, please notify the sender immediately by
> > > replying to this message and then delete it from your system
> > >
> > > _______________________________________________
> > > sqr-users mailing list
> > > sqr-users@sqrug.org
> > > http://www.sqrug.org/mailman/listinfo/sqr-users
> > >
> >
> >
> >
> > --
> > ----------------------------------------------------------------------
> > - Donald Mellen | Ray Ontko & Co. - Richmond, IN -
> > http://www.ontko.com/ donm@ontko.com | "In the beginning, there was
> > nothing, which exploded"
> > (Terry Pratchett)
> >
> > _______________________________________________
> > sqr-users mailing list
> > sqr-users@sqrug.org
> > http://www.sqrug.org/mailman/listinfo/sqr-users
> >
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>
> The information contained in this e-mail and any attached
> documents may be privileged, confidential and protected from
> disclosure. If you are not the intended recipient you may not
> read, copy, distribute or use this information. If you have
> received this communication in error, please notify the sender
> immediately by replying to this message and then delete it
> from your system
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
>
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users