Like Statement Problem on Lookup Table

[TazzMann]

I am trying the following code and not getting any results. The like is looking in a lookup table to see if there is anything similar to what is in the original table. The original Table is populated by a spreadsheet where the user may not enter the value exactly as it appears in the lookup table. Am I doing this correctly, or is there a better way of doing it?

thanks in advance!

Select a.Plan_Number, 
	a.Plan_Name, 
	a.Customer_ID, 
	b.BasisID,  
	a.Last_PE_Before_BBSI,  
	a.PT_Number, 
	a.Accrual_On_Checks, 
	a.Available_On_Checks, 
	a.Can_Accrue, 
	a.Can_Carry_Over, 
	a.Add_Existing_Emp, 
	a.Add_New_Emp
FROM dbo.tmp_PTO_AccesstoSQL a, 
	dbo.tlkp_PTO_Plan_Basis b
Where a.Plan_Basis like  '%' + b.basis_desc + '%'

Tazzmann

 

[donutman]

Give us some example data. Can a lookup result in more than one match?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[TazzMann]

No a Lookup cannot result in more than one match. An Example of lookup data would be the following:

ID Name
1 Anniversary Based
2 Calendar Based

The values entered into the spreadsheet would be either: 'Anniversary' or 'Calendar'

Let me know if you need more. I am stumped.

Thanks,

Tazzmann

 

[SQLBill]

For testing purposes, make this change and run the query:

Where a.Plan_Basis like '%' + 'Anniversary' + '%'

If Anniversary and Calendar were only examples and not real data, replace Anniversary with a real value from the dbo.tlkp_PTO_Plan_Basis.basic_desc column. But put the value exactly as above, don't use '%Anniversary%'.

-SQLBill

Posting advice: FAQ481-4875

 

[donutman]

Do you mean that the user might enter the word 'aniversary' instead of 'anniversary'? And you want to be sure that it matches?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[TazzMann]

Well, donutman, the user might enter 'Anniversary' insead of 'Anniversary Based' so I want to match 'Anniversary' from the lookup table. (i.e. 'Anniversary%')

Make sense?



Tazzmann

 

[TazzMann]

Oh SQLBill, I tried that, and it still did not find a match.

Tazzmann

 

[danceman]

You need a JOIN condiction in your FROM
FROM dbo.tmp_PTO_AccesstoSQL a LEFT OUTER JOIN
dbo.tlkp_PTO_Plan_Basis b ON a.simfield = b.simfield





Attitude is Everything

 

[TazzMann]

danceman, without an EXACT match, the join will never work. That is the problem I am struggling with. They way this whole thing works is:

1. A user fills out certain cells in an excel spreadsheet. one of the fields if for a Paid Time Off (PTO) plan type which is either 'Anniversary Based' or 'Calendar Based' as defined by a lookup table. The user MAY end up putting into the spreadsheet cell 'Anniverary' instead of 'Anniversary Based' or 'Calendar' instead of 'Calendar Based'.

2. I then import this into a temp table in Access, as is, straight from Excel. I then need to get it into 'normalized' tables within SQL Server. SQL server has a lookup table that contains two fields: an id and a description. The temporary table contains just the description, (Which may only be a part of what is in the lookup table, see #1)

To do a join, I would need the description in the temp table to EXACTLY match that which is in the lookup table. This will more than likely NOT happen. So I need to do a 'Like' clause or something similar to lookup the partial match in the lookup table so I may then grab the ID and populate the final SQL table which is normalized and used the ID's and not the actual text.

<deep breath> WHEW!

Hope that helps to explain it more.


Tazzmann

 

[donutman]

It doesn't seem like this is a situation where you really want to perform a join. Aren't you more interested in just correcting data that is incorrectly entered and possibly listing those rows from the Excel spreadsheet that cannot be fixed without human intervention?
I would suggest doing an update on the Excel table (after it's imported) and changing 'anniversary' to 'anniversary based'. Then do a query on that table looking for all rows that do not have 'Anniversary Based' or 'Calendar Based' in that column.
If you still want to perform a join you could then do it without the Like keyword.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLBill]

Now try:

Where a.Plan_Basis like '%Anniversary%'

(I'm trying to narrow down possibilities).

If that doesn't work, I'd say you don't have those values in a.Plan_Basis. Run a query on a.Plan_Basis to list the distinct values.

If it does work, the problem is with the way the %'s are added to the value. It might be ending up as:

like %Anniversary%
vice
like '%Anniversary%'

-SQLBill


Posting advice: FAQ481-4875

 

[TazzMann]

Thanks donutman, I think that is what I will have to do. I don't care to correct the values from the spreadsheet cause in the final table, they won't be used, just their corresponding id number from the lookup table. But I may have to correct them in the intermediate table so that I an do a lookup to get the ids from the lookup table. Oh well...

SQLBill, That works so it has to be something with the way SQL Server is trying to concat the string.

Thanks,

Tazzmann