An interesting Lastname, Firstname, Middlename challenge

I'm sure you all have seen situations where a field has a combined name
in the format of "Lastname, Firstname Middlename" and I've seen
examples of parsing that type of field. But what happens when the data
within this type of field is inconsistent? Here are some examples

Apple, John A.
Berry John B.
CherryJohn C
Donald John D

How does one parse the data when the data isn't consistent?imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

I'm sure you all have seen situations where a field has a combined name
in the format of "Lastname, Firstname Middlename" and I've seen
examples of parsing that type of field. But what happens when the data
within this type of field is inconsistent? Here are some examples
>
Apple, John A.
Berry John B.
CherryJohn C
Donald John D
>
>
How does one parse the data when the data isn't consistent?

Conditionally. Something along the lines of (I'll use a mixture of
SQL and pseudocode)

select
case when name like '<last>, <first<m>.' then <last>
when name like '<last>, <first<m>' then <last>
when name like '<last<first<m>.' then <last>
when name like '<last<first<m>' then <last>
else name last_name,
case when name like '<last>, <first<m>.' then <first>
when name like '<last>, <first<m>' then <first>
when name like '<last<first<m>.' then <first>
when name like '<last<first<m>' then <first>
else '' first_name,
case when name like '<last>, <first<m>.' then <m>
when name like '<last>, <first<m>' then <m>
when name like '<last<first<m>.' then <m>
when name like '<last<first<m>' then <m>
else '' middle_initial
from (etc.)|||>How does one parse the data when the data isn't consistent? <<

I get a mailing package and save myself a lot of problems -- why
re-invent the wheel?

Group 1 Software , Melissa Data Corporation and SAA are such
companies. They can scrub mailing lists MUCH better than you can --
unless you want to make a major project of it for a few years.

An if...else task

Hi,

Im running a database query and I want to compare the value of a datetime field to see what I wanna do with my data.

Is there any easy way of doing this with something from the Toolbox or would you recommend using a Script Task?

Thank you.

What do you want to compare it to? Can you give more detail about what it is you are trying to do?

-Jamie

|||

I have 100 rows of query data.

The rows contain a datetime field that I wanna check.

If the row happens before 10 o'clock:

- Do something

After 10 o'clock:

- Do something else

Thank you.

|||All depends on what Something and Something Else is.

You could use a conditional split to send the rows to different places or add an additional column with the flag stating the change or use the script task if you want to change the value of a variable if the condition is found.|||

I would say that conditional split is almost certainly going to be what you are looking for.

in your example you would just set a single case wherein your example datetime column is compared to less than or equal to 10 o'clock and all other rows are sent to default (greater than 10 o'clock) ...

An Access OnFormat procedure would be replaced in SQL Reporting Services by what?

In the field, the operators are presented with 5 categories, each category having between 3 and 5 possible selections, for example:

Weather - clear(1), cloudy(2), rain(4) or snow(8).

If the operator selected cloudy and rain, the value of Weather would be 6.

I want to print a report in the office that will list the categories and the available selections for each, putting an "X" next to those selections that were checked by the operators.

In Access, it was easy - I plugged a decode procedure into the OnFormat event...

cnum = obj![WEATHR]
If cnum - 8 >= 0 Then
obj![SNOW] = "X"
cnum = cnum - 8
End If
If cnum - 4 >= 0 Then
obj![RAIN] = "X"
cnum = cnum - 4
End If
If cnum - 2 >= 0 Then
obj![CLOUDY] = "X"
cnum = cnum - 2
End If
If cnum - 1 >= 0 Then
obj![CLEAR] = "X"
End If

Making the change to SQL Reporting Services, I'm clueless...as you can tell, I'm not even sure how to ask the question.

Any help would be greatly appreciated.

There are probably a number of other ways to do this, this is just the first that came to mind.

Add the following on the Code tab of your project (select Report..Report Properties):

Public Function DecodeWeather(ByVal Weather As Integer, ByVal WeatherType As String) As String
DIM cnum AS Integer = Weather
If cnum - 8 >= 0 Then
If WeatherType = "SNOW" Then
Return "X"
Else
cnum = cnum - 8
End If
End If
If cnum - 4 >= 0 Then
If WeatherType = "RAIN" Then
Return "X"
Else
cnum = cnum - 4
End If
End If
If cnum - 2 >= 0 Then
If WeatherType = "CLOUDY" Then
Return "X"
Else
cnum = cnum - 2
End If
End If
If cnum - 1 >= 0 Then
If WeatherType = "CLEAR" Then
Return "X"
End If
End If

End Function

Then add a calculated field to your dataset (on the Layout tab, right click the dataset and choose add) named Snow and add the following to the expression:

=Code.DecodeWeather(Fields!ID.Value, "SNOW")

Repeat for the other fields, passing the appropriate name to the function.

Hope this helps.

|||

Excellent answer!

Thank you.

An Access OnFormat procedure would be replaced in SQL Reporting Services by what?

In the field, the operators are presented with 5 categories, each category having between 3 and 5 possible selections, for example:

Weather - clear(1), cloudy(2), rain(4) or snow(8).

If the operator selected cloudy and rain, the value of Weather would be 6.

I want to print a report in the office that will list the categories and the available selections for each, putting an "X" next to those selections that were checked by the operators.

In Access, it was easy - I plugged a decode procedure into the OnFormat event...

cnum = obj![WEATHR]
If cnum - 8 >= 0 Then
obj![SNOW] = "X"
cnum = cnum - 8
End If
If cnum - 4 >= 0 Then
obj![RAIN] = "X"
cnum = cnum - 4
End If
If cnum - 2 >= 0 Then
obj![CLOUDY] = "X"
cnum = cnum - 2
End If
If cnum - 1 >= 0 Then
obj![CLEAR] = "X"
End If

Making the change to SQL Reporting Services, I'm clueless...as you can tell, I'm not even sure how to ask the question.

Any help would be greatly appreciated.

There are probably a number of other ways to do this, this is just the first that came to mind.

Add the following on the Code tab of your project (select Report..Report Properties):

Public Function DecodeWeather(ByVal Weather As Integer, ByVal WeatherType As String) As String
DIM cnum AS Integer = Weather
If cnum - 8 >= 0 Then
If WeatherType = "SNOW" Then
Return "X"
Else
cnum = cnum - 8
End If
End If
If cnum - 4 >= 0 Then
If WeatherType = "RAIN" Then
Return "X"
Else
cnum = cnum - 4
End If
End If
If cnum - 2 >= 0 Then
If WeatherType = "CLOUDY" Then
Return "X"
Else
cnum = cnum - 2
End If
End If
If cnum - 1 >= 0 Then
If WeatherType = "CLEAR" Then
Return "X"
End If
End If

End Function

Then add a calculated field to your dataset (on the Layout tab, right click the dataset and choose add) named Snow and add the following to the expression:

=Code.DecodeWeather(Fields!ID.Value, "SNOW")

Repeat for the other fields, passing the appropriate name to the function.

Hope this helps.

|||

Excellent answer!

Thank you.

AMO. AggregationDesign.EstimatedPerformanceGain

Hi, why this field is always empty?

I this a "free" field, that can be set from ISV developer as he want?

But it is not persistent. (Is this a bug?)

What a purpose of the field?

After designing aggregations (with the wizard or with AMO), engine will provide a value for the EstimatedPerformanceGain property. This property is read-only from the client (AMO) point of view.

Adrian Dumitrascu

|||

This is not right.

After designing aggregations this field is definitly not set in AMO.

public static void DesignAggregation(MeasureGroup pMg, long pOtimizations, long pAggCount, out long oOptimisations, out long oAggCount) {

double lOptimizations = 0;

double lStorage = 0;

long lAggCount = 0;

bool lHasFinished = false;

AggregationDesign lAd;

lAd = pMg.AggregationDesigns.Add(cDefaultAggrDesign);

lAd.InitializeDesign();

lOptimizations = 0;

while (lOptimizations < pOtimizations && lAggCount < pAggCount && !lHasFinished) {

lAd.DesignAggregations(out lOptimizations, out lStorage, out lAggCount, out lHasFinished);

pDelegate(pMg.Name, ((int)lOptimizations).ToString(), lAggCount.ToString());

}

oOptimisations = (int)lOptimizations;

oAggCount = lAggCount;

lAd.FinalizeDesign();

// The lAd.EstimatedPerformanceGain is always 0.

// If I set it here. It will be not saved on server.

lAd.EstimatedPerformanceGain = (int)lOptimizations;

lAd.Update();

lAd.Refresh();

// Here lAd.EstimatedPerformanceGain is again 0.

pMg.Update();

}

Ammending Data in Field

Hi

New to crystal reports, but familiar with access.

I have been asked to ammend a label that is generated using Crystal 8.5.

Couple of questions:

(1) I cannot see any of the fields for the report, even when I lasso the entire report all I can see is the outline for each box - how can I change this?

(2) For one particular field I have been asked to add a barcode to this. I have the barcode font, and I have copied the exisiting field and changed the font. However, I need to add an '*' to the beginning and end of the exisitng component number - how can I do this?

(3) In the component field, I need to be able to delete any hyphens or spaces in the number (this is for the same field in (2)).

So for example if the component number is:

095-12-6567 7

I need to be able to convert it to:

*0951265677*

Please note that the space or hyphen may not always be present...

Thanks in advance...1 Give more details
2 Create a formula having the code
"*" & {field} & "*" and place it in the details section
(3) Create formula having the code
replace({field},"-","")|||Hi

Thanks for the help - that works like a charm :)

WRT item 1.

In design view everything is invisible, I cannot see text boxs or anything similar. When I lasso everything I see a blue dashed outline showing where it is. I then click at random, somewhere close to the box that I wish to edit/move until I hit the correct one.

When I populate the report with actual data everything (even empty fields) becomes visible|||Did you check this in Preview mode?

ambigous column name message

I am modifying an existing stored procedure in SQL server 2005. I have added a new field to the sp and am now receiving an ambiguous column name message. The column being referred to was in the sp before I modified. The column is on the line above where I added my new field ( EMPLOYEE NUMBER) to the sp. I am at a loss to why I'm getting this error message when executing the sp because this column existed before I modified. Can anyone help me understand why I'm getting this message all of a sudden and/ or where to look for help? Thanks in advance for any light you can help shed on this matter. Code snippet is below:

[BILL DUE DATE], [PAYMENT DATE AND TIME],

[EMPLOYEE NUMBER] )

Msg 209, Level 16, State 1, Procedure spBuildNoReasonLetter, Line 34

Ambiguous column name 'PAYMENT DATE AND TIME'.

There is no way we can 'guess' what is happening without seeing the code.

Please post the entire stored procedure code.

|||

Sorry, here is the sp:

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERPROCEDURE [dbo].[spBuildNoReasonLetter]

AS

setnocounton

TRUNCATETABLE [NO REASON LETTER];

INSERTINTO [NO REASON LETTER] ( [EMPLOYER NUMBER], [EMPLOYER NAME],

[CONTACT PERSON], [ADDRESS LINE 1],

[ADDRESS LINE 2], [ADDRESS LINE 3],

[EMPLOYEE SSN], [SERVICE CODE],

[EMPLOYEE NAME], [BILLING PERIOD],

[BILL DUE DATE], [PAYMENT DATE AND TIME],

[EMPLOYEE NUMBER] )

SELECTDISTINCT [NIGHT BATCH TABLE].[EMPLOYER NUMBER],

[COMPANY ADDRESS].[FULL NAME] AS [EMPLOYER NAME],

[COMPANY ADDRESS].[CONTACT PERSON],

[COMPANY ADDRESS].[ADDRESS LINE 1],

[COMPANY ADDRESS].[ADDRESS LINE 2],

[COMPANY ADDRESS].[CITY]+', '+[COMPANY ADDRESS].[STATE]+' '+[COMPANY ADDRESS].[ZIP CODE] AS [ADDRESS LINE 3],

[NIGHT BATCH TABLE].[EMPLOYEE SSN],

[NIGHT BATCH TABLE].[SERVICE CODE],

[MAIN EMPLOYEE].[FULL NAME] AS [EMPLOYEE NAME],Convert(varchar(10),

[COMPANY PAY TABLE].[BILLING PERIOD START],101)+' Thru '+Convert(varchar(10),[COMPANY PAY TABLE].[BILLING PERIOD END],101)AS [BILLING PERIOD],

[NIGHT BATCH TABLE].[BILL DUE DATE], [PAYMENT DATE AND TIME],

[MAIN EMPLOYEE].[EMPLOYEE NUMBER]

FROM(([NIGHT BATCH TABLE] INNERJOIN [COMPANY ADDRESS] ON([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY ADDRESS].[ADDRESS KEY])AND

([NIGHT BATCH TABLE].[ADDRESS TYPE] = [COMPANY ADDRESS].[ADDRESS TYPE]))

INNERJOIN [MAIN EMPLOYEE] ON [NIGHT BATCH TABLE].[EMPLOYEE SSN] = [MAIN EMPLOYEE].[EMPLOYEE SSN])

INNERJOIN [NO REASON LETTER] ON [MAIN EMPLOYEE].[EMPLOYEE NUMBER] = [NO REASON LETTER].[EMPLOYEE NUMBER]

INNERJOIN [COMPANY PAY TABLE] ON([NIGHT BATCH TABLE].[BILL DUE DATE] = [COMPANY PAY TABLE].[BILL DUE DATE])AND

([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY PAY TABLE].[EMPLOYER NUMBER])

WHERE([NIGHT BATCH TABLE].[REASON CODE]='X'AND

[MAIN EMPLOYEE].[STATUS CODE] In('00','01','09'));

UPDATE NRL

SET NRL.[CARRIER NAME] = CA.[FULL NAME],

NRL.[LOGO PATH] = CA.[CONTACT PERSON],

NRL.[TOLL FREE SERVICE NO] = CA.[TOLL FREE SERVICE NO]

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY ADDRESS] AS CA ON(CA.[ADDRESS KEY] = NRL.[EMPLOYER NUMBER] AND

CA.[ADDRESS TYPE] ='R')

UPDATE NRL

SET NRL.[SORT FIELD1] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

ME.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 1),

NRL.[SORT FIELD2] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

ME.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 2),

NRL.[EMPLOYEE SSN] = dbo.fnReturnFieldOrBlank(ME.[EMPLOYEE SSN], CO.[DO NOT DISPLAY SSN])

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY] AS CO ON(CO.[EMPLOYER NUMBER] = NRL.[EMPLOYER NUMBER])

INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE SSN] = NRL.[EMPLOYEE SSN])

INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])

|||I have changed my code slightly and am now getting a differenet message (new code is below error message:

Msg 4104, Level 16, State 1, Procedure spBuildNoReasonLetter, Line 17

The multi-part identifier "NO REASON LETTER.EMPLOYEE NUMBER" could not be bound.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERPROCEDURE [dbo].[spBuildNoReasonLetter]

AS

setnocounton

TRUNCATETABLE [NO REASON LETTER];

INSERTINTO [NO REASON LETTER] ( [EMPLOYER NUMBER], [EMPLOYER NAME],

[CONTACT PERSON], [ADDRESS LINE 1],

[ADDRESS LINE 2], [ADDRESS LINE 3],

[EMPLOYEE SSN], [SERVICE CODE],

[EMPLOYEE NAME], [BILLING PERIOD],

[BILL DUE DATE], [PAYMENT DATE AND TIME],

[EMPLOYEE NUMBER])

SELECTDISTINCT [NIGHT BATCH TABLE].[EMPLOYER NUMBER],

[COMPANY ADDRESS].[FULL NAME] AS [EMPLOYER NAME],

[COMPANY ADDRESS].[CONTACT PERSON],

[COMPANY ADDRESS].[ADDRESS LINE 1],

[COMPANY ADDRESS].[ADDRESS LINE 2],

[COMPANY ADDRESS].[CITY]+', '+[COMPANY ADDRESS].[STATE]+' '+[COMPANY ADDRESS].[ZIP CODE] AS [ADDRESS LINE 3],

[NIGHT BATCH TABLE].[EMPLOYEE SSN],

[NIGHT BATCH TABLE].[SERVICE CODE],

[MAIN EMPLOYEE].[FULL NAME] AS [EMPLOYEE NAME],Convert(varchar(10),

[COMPANY PAY TABLE].[BILLING PERIOD START],101)+' Thru '+Convert(varchar(10),[COMPANY PAY TABLE].[BILLING PERIOD END],101)AS [BILLING PERIOD],

[NIGHT BATCH TABLE].[BILL DUE DATE], [PAYMENT DATE AND TIME],

[MAIN EMPLOYEE].[EMPLOYEE NUMBER]

FROM(([NIGHT BATCH TABLE] INNERJOIN [COMPANY ADDRESS] ON([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY ADDRESS].[ADDRESS KEY])AND

([NIGHT BATCH TABLE].[ADDRESS TYPE] = [COMPANY ADDRESS].[ADDRESS TYPE]))

INNERJOIN [MAIN EMPLOYEE] ON [NIGHT BATCH TABLE].[EMPLOYEE SSN] = [MAIN EMPLOYEE].[EMPLOYEE SSN])

INNERJOIN [MAIN EMPLOYEE] AS MET ON [NO REASON LETTER].[EMPLOYEE NUMBER] = [MAIN EMPLOYEE].[EMPLOYEE NUMBER]

INNERJOIN [COMPANY PAY TABLE] ON([NIGHT BATCH TABLE].[BILL DUE DATE] = [COMPANY PAY TABLE].[BILL DUE DATE])AND

([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY PAY TABLE].[EMPLOYER NUMBER])

WHERE([NIGHT BATCH TABLE].[REASON CODE]='X'AND

[MAIN EMPLOYEE].[STATUS CODE] In('00','01','09'));

UPDATE NRL

SET NRL.[CARRIER NAME] = CA.[FULL NAME],

NRL.[LOGO PATH] = CA.[CONTACT PERSON],

NRL.[TOLL FREE SERVICE NO] = CA.[TOLL FREE SERVICE NO]

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY ADDRESS] AS CA ON(CA.[ADDRESS KEY] = NRL.[EMPLOYER NUMBER] AND

CA.[ADDRESS TYPE] ='R')

UPDATE NRL

SET NRL.[SORT FIELD1] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

MET.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 1),

NRL.[SORT FIELD2] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

MET.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 2),

NRL.[EMPLOYEE SSN] = dbo.fnReturnFieldOrBlank(ME.[EMPLOYEE SSN], CO.[DO NOT DISPLAY SSN])

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY] AS CO ON(CO.[EMPLOYER NUMBER] = NRL.[EMPLOYER NUMBER])

INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE SSN] = NRL.[EMPLOYEE SSN])

INNERJOIN [MAIN EMPLOYEE] AS MET ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])

|||

Hi,

you are not referencing the Table in the Select clause, therefore you cannot use it in the join part.

BTW. Did I mention that it is horrorible to use space and special characters in defintions ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Now I'm confused.

First you TRUNCATE the [NO REASON LETTER] table.

Then you attempt to use JOIN conditions to that EMPTY table (but there is no previous mention of a JOIN to that table.. What are you trying to accomplish?

INNERJOIN [MAIN EMPLOYEE] AS MET ON [NO REASON LETTER].[EMPLOYEE NUMBER] = [MAIN EMPLOYEE].[EMPLOYEE NUMBER]

Even if it 'could' happen (and it just can't), since the table is empty, this would serve to filter out ALL possible rows and nothing would be inserted.

So what's the point?

|||

One other thing to keep in mind is that if you are going to Alias a table in a join clause, you probably should use it in the join.

INNERJOIN [MAIN EMPLOYEE] AS MET ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])

I would think that you would want to use MET.[EMPLOYEE NUMBER] instead of ME.

Ben Miller

Am I in the right direction?

I am using VFP as my interface and using SQL table as my back-end but recently i got this problem when i add another field in one of my tables, Before it has only 35 field and it goes right, but when I add another 3 fields my problem starts,... if i edit a character fields with all necessary code to update a record and accepted by SQL it has no problem... but when i edit the numeric field which I add, after i refresh my form, it goes back to each original values. at sql table also when i opened nothing happened the old value still there.

Thank you for any help

Hi Madix_t,

Do you add the field via SQL Management Studio or by ALTER TABLE statements via SQL Pass-through?

I assume you use Visual FoxPro Remote Views, or perhaps VFP CursorAdapters to access the data, or are you using SQL Pass-through? Are you using a buffering scheme?

How are you changing the data? After you've typed in the Textbox (or Grid) do you use the VFP TableUpdate() statement to commit the changes? Do you then requery the SQL back end before you refresh the form?

|||

I am using CursorAdapter, and I even tried to recreate the CA, but it's d'same.

Regarding how I save my data, i dont have any problem with all my tables in MS SQL at VFP interface, all necessary steps that SQL will accept my data has been followed ,.. just only this table after I add another fields., and also this is the only table that has 38 fields.

thanks for any help

|||

Hi Madix_t,

Can you duplicate this problem with a new table? Is there anything about the column that's odd such as being accidentally set to read-only or having a constraint that prohibits the update values you've used?

|||

ok..i'll try it, and thank you maam

Am I in the right direction?

I am using VFP as my interface and using SQL table as my back-end but recently i got this problem when i add another field in one of my tables, Before it has only 35 field and it goes right, but when I add another 3 fields my problem starts,... if i edit a character fields with all necessary code to update a record and accepted by SQL it has no problem... but when i edit the numeric field which I add, after i refresh my form, it goes back to each original values. at sql table also when i opened nothing happened the old value still there.

Thank you for any help

Hi Madix_t,

Do you add the field via SQL Management Studio or by ALTER TABLE statements via SQL Pass-through?

I assume you use Visual FoxPro Remote Views, or perhaps VFP CursorAdapters to access the data, or are you using SQL Pass-through? Are you using a buffering scheme?

How are you changing the data? After you've typed in the Textbox (or Grid) do you use the VFP TableUpdate() statement to commit the changes? Do you then requery the SQL back end before you refresh the form?

|||

I am using CursorAdapter, and I even tried to recreate the CA, but it's d'same.

Regarding how I save my data, i dont have any problem with all my tables in MS SQL at VFP interface, all necessary steps that SQL will accept my data has been followed ,.. just only this table after I add another fields., and also this is the only table that has 38 fields.

thanks for any help

|||

Hi Madix_t,

Can you duplicate this problem with a new table? Is there anything about the column that's odd such as being accidentally set to read-only or having a constraint that prohibits the update values you've used?

|||

ok..i'll try it, and thank you maam