Anyone good with SQL?

JohnB47

flaneurs_lobster said:

Old programmer in me is shouting "get the input data validation tightened up".

Makes this type of problem unnecessarily hard if the conditional tests can have any old rubbish thrown at them. 

Not sure what you mean by validation but in my case, the input is derived from a CSV file supplied by my credit card company. I and maybe they, have no control over how the various companies supply their names. Sometimes the same company name is uppercase, sometimes it's lower, sometimes mixed, sometimes with additional characters. No idea why it changes.

For info, I take the CSV, convert it to xlxs format and run a macro that converts the format of the fields to suit the target database table, then I append the data to that table. Then I run the SQL in the database that allocates a 'Type' number to the various spend categories. Then the database forms are use to select/display the spend/totals by type.

I suppose I could run a macro on the CSV or XLXS (or add to my existing macro) that would change all company names to upper or lower case and that would avoid me having to change my rather large SQL by adding 'upper' in lots of places. (Edit: I'd still have to make lots of changes actually.) Perhaps that's a question for another thread. Still wouldn't get rid of the extra characters like Fred's.

Anyway It's been good to see the solutions quoted here.

victor2

Probably a whole new project you may not even wish to tackle, but you could bypass using Excel altogether and just develop a routine in Libre office to import the data into the database.
I have no idea what capabilities Libre office has, but it could be worth a new thread on here if you wished to explore it...

Ergates

Excel can open .csv files without converting.  and a macro can be written to just ingest the data straight from an external file (if you know the file format, which presumably you do).

From a data perspective, it would make more sense to standardise the data on load rather than at point of use - 

flaneurs_lobster

JohnB47 said:

flaneurs_lobster said:

Old programmer in me is shouting "get the input data validation tightened up".

Makes this type of problem unnecessarily hard if the conditional tests can have any old rubbish thrown at them. 

Not sure what you mean by validation but in my case, the input is derived from a CSV file supplied by my credit card company. I and maybe they, have no control over how the various companies supply their names. Sometimes the same company name is uppercase, sometimes it's lower, sometimes mixed, sometimes with additional characters. No idea why it changes.

No chance they also supply a company identifier code as well as the name? CoRegNo would be good. 

JohnB47

Thanks for the ideas. I'll look into those.

Vitor

Depending on size/scope of system, you'd generally not put the literal text of the supplier name in the Spend table, but instead had a supplier table with unique numeric code for each supplier, along with the standard name text. Possibly also a 'parent' code field so you can build a hierarchy of suppliers. The supplier's code number would go into the Spend table.

Of course your input data processing will need to a process to add new suppliers and correct variations on the name of existing suppliers to link to their code number.

flaneurs_lobster

Vitor said:

Depending on size/scope of system, you'd generally not put the literal text of the supplier name in the Spend table, but instead had a supplier table with unique numeric code for each supplier, along with the standard name text. Possibly also a 'parent' code field so you can build a hierarchy of suppliers. The supplier's code number would go into the Spend table.

Of course your input data processing will need to a process to add new suppliers and correct variations on the name of existing suppliers to link to their code number.

Back in the day that chunk of analysis would have cost one of my clients a day's billing.....

Shimrod

JohnB47 said:

Thanks for the quick replies folks. Obviously I'll play around with those suggestions but I need to check my understanding.

A couple of you have referred to 'changing' the case and "Another vote to change case using upper or lower." I don't actually want to change the entries in the "Description" field. I want the SQL to inspect and find those records where that field has, say Fred, or FRED or fred in them and then carry out a procedure accordingly.

Anyway, are you saying that this code would work (given my simple example):

UPDATE "tblSpend"

SET "Type" = '4'

WHERE (upper("Description") Like upper('%Freds Groceries%'))

I can't quite see why that would do what I want. But then what do I know! Thanks again.

What flavour of database are you using? Postgresql and Snowflake both have an ilike operator which performs a case insensitive compare. So your query becomes: 

UPDATE "tblSpend"

SET "Type" = '4'

WHERE ("Description" iLike '%Freds Groceries%')

JohnB47

Shimrod said:

JohnB47 said:

Thanks for the quick replies folks. Obviously I'll play around with those suggestions but I need to check my understanding.

A couple of you have referred to 'changing' the case and "Another vote to change case using upper or lower." I don't actually want to change the entries in the "Description" field. I want the SQL to inspect and find those records where that field has, say Fred, or FRED or fred in them and then carry out a procedure accordingly.

Anyway, are you saying that this code would work (given my simple example):

UPDATE "tblSpend"

SET "Type" = '4'

WHERE (upper("Description") Like upper('%Freds Groceries%'))

I can't quite see why that would do what I want. But then what do I know! Thanks again.

What flavour of database are you using? Postgresql and Snowflake both have an ilike operator which performs a case insensitive compare. So your query becomes: 

UPDATE "tblSpend"

SET "Type" = '4'

WHERE ("Description" iLike '%Freds Groceries%')

Interesting. It's  LibreOffice Base database. I'm very much an amateur with it. I'll check if it has that operator

onomatopoeia99

Vitor said:

Depending on size/scope of system, you'd generally not put the literal text of the supplier name in the Spend table, but instead had a supplier table with unique numeric code for each supplier, along with the standard name text. Possibly also a 'parent' code field so you can build a hierarchy of suppliers. The supplier's code number would go into the Spend table.

Of course your input data processing will need to a process to add new suppliers and correct variations on the name of existing suppliers to link to their code number.

Or "use third normal form in your database design".