Hi i need some help on select sql 3

[Ernesto_Paez]

Hi everyone,
i have table named empname and a field character named employee.

the employee field values can be as "125, "25", "5150" and so on, it will never has more than 4 characters long.

so i need if the values of that field is len=2, then replace that with a 10 example if "25", then i need it to "1025".

if value ='161',then i need it to be '0161'

if value ="5150", then i need it as "5150"

so here is my select sql ,can you please advise how to get if fixed as i it is working for <3 bit not working for the others, so i can't figure out

Select Iif(len(alltrim(employee))< 3, '10','0')+alltrim(employee) as employee;		
	FROM y:\empname	into Cursor junksalary NOFILTER

thanks a lot in advance

 

[Mike Lewis]

Here is an easier way:

[tt]SELECT RIGHT("10" + ALLTRIM(Employee), 4) AS Employee FROM .... etc
[/tt]

That should work with all cases.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SkipVought]

Hi,

Select Iif(len(alltrim(employee))< 3, '10',Iif(len(alltrim(employee))<4,'0',''))
+alltrim(employee) as employee;

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Olaf Doschke]

What you could do to make this eaeier and not need IIF or ICASE is prefix "10" and take the right 4 characters:
[pre]"25" -> "1025" -> Right(x,4): "1025"
"125" -> "10125" -> Right(x,4): "0125"
"5150" -> "105150" -> Right(x,4): "5150"[/pre]

If that works for you the expression is [tt]Right(Alltrim(employee),4) as employee[/tt].


And by the way, I wouldn't ever trust assertions about data like "no employee has a single digit number". Even when there is such a field rule it could have been added after such records already exist.
You have SQL to find out: [tt]SELECT Distinct Len(alltrim(employee)) as length, count(*) as frequency from empname[/tt]. You might even have NULL vaues.

Now why didnd't IF work: Your IIF does this:
[pre]"25" -> Len("25")=2 [highlight #FCE94F]is <3[/highlight], so prefix "10": "1025"
"125" -> Len("25")=3 [highlight #FCE94F]is not <3[/highlight], so prefix "0": "0125"
"5150" -> Len("5150")=4 [highlight #FCE94F]is not <3[/highlight], so prefix "0": "05150"[/pre]

It's simple to see when you strictly act as the code you write. 0-2 digits get the "10" prefix, the case for 0-1 don't exist, so that case is only for 2 digit employees. Every other with 3 [highlight #FCE94F]or 4[/highlight] digits gets prefix "0", both 3 and 4 are not <3. You have no case for no prefix. As you want 4 digit numbers to get no prefix, you have another case to distinguish.

And let me guess: The SQL returns "0515" in the last case instead of "05150". Look into the width of the result field:

? Len(junksalary.employee)
-> 4

It's 4, isn't it?

As your first result record likely comes from one of the first employees with a short employee number, that get's correctly prefixed and ends with 4 char long value. And that means the last digit of most any employee with 4-digit number get's cut off as the intermediate IIF result is 5 digits long. Your employee "5150" does neither stay "5150" nor gets "05150" but turns into "0515". Because you don't know you shouldn't use any expressions of varying lengths or you don't realize that it's that way.


Some recommendations:
The first step before programming anything: Browse data, do some value checks with things like a GROUP BY or DISTINCT query.
Second step: When there are cases with less than 10 records: Perhaps even cover these cases manually before you write code 10 records are corrected manually easier and faster. Plus that'll correct them permanently, not just your query result. And then, always cover the most frequent case first, because of step 2, what's left over might simply be manually covered again.

You could also do 2 updates, in the first prefix "0" for every number shorter than 4, afterward prefix "1" for those still not having length 4. If thinking about 3 instead of just 2 cases is already an abstraction too much - I have an understanding for this at the moment, the heat here in Germany is draining concentration and focus, I can, fortunately, go into a cooled server room. And ice helps, though I still needed 3 or 4 edits before this finally is as I like.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Ernesto_Paez]

thanks a lot Mike, Olaf and SkipVought
Mike the condition works perfect
Olaf, thanks a lot for the explanation that allow me to see the reason of what works or not
SkipVought thanks a lot as well

 

[Olaf Doschke]

I forgot to fully translate what I said to the expression

prefix "10" and take the right 4 characters:

but Mike has me covered, thanks, Mike.

It would be even simpler if you'd just pad left with "0": [tt]PADL(ALLTRIM(employee),4,"0")[/tt]

With the "10" prefix you could, of course, get double numbers for any employees already in the range 1000 to 1099.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Ernesto_Paez]

Hi Guys,
again looking for your help
after i execute the below.

SELECT RIGHT("10" + ALLTRIM(Employee), 4) AS Employee FROM empname INTO CURSOR junk nofilter

then i am able to get in my cursor the employee numbers with a 0 or a 10 as we discussed above in the previous post
then i want to send this to excel or as a csv file, but the problem is, that even when the cursor has the values as needed example 25 as 1025, 161 as 0161 or 5151 as 5151, then when i send this to excel, i lose the numbers added, so excel will show again 1025 as 25 and 0161 as 161, i need to have them in excel as they are in the cursor junk.
i thou that i was having something wrong but i just simply ran the above select sql and later use.

copy to c:\myfolder\myfile type csv

and the column employee in the excel file appears as 25, 161, so for reason that i don't know, the 0 and 10 in front of the numbers in this case let say 1025 the 10 disappears and it is shown in excel as just 25
any help is very well appreciated, and excuse me for my poor knowledge
Thanks

 

[Olaf Doschke]

Then you're exporting the empname.dbf Select the workarea "junk" before doing that COPY TO:

SELECT junk
copy to c:\myfolder\myfile type csv

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

The worst thing, that could hapen is, excel interprets this as number and cuts off leading 0, but Excel can't know 1025 once was 25, that's only possible, if you have the original dbf selected before the copy to.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Hi,

I have tested and your remark

and the column employee in the excel file appears as 25, 161, so for reason that i don't know, the 0 and 10 in front of the numbers in this case let say 1025 the 10 disappears and it is shown in excel as

seems incorrect, what I see is the 0 in front dissappears when you import into Excel. so 0125 will become in Excel 125, it seems Excel, although you export a string reads 0125 as number.

Koen

 

[Ernesto_Paez]

Hi Olaf,
the problem is that the empname table, the values for employee numbers are for example 25, 161, 345, 5155
so the company that needs this employee number file csv or excel need all the employee numbers to be as character width =4 or number width =4 but i cannot work straight out of that empname.dbf i need to transfer to a cursor as i need to o other things, so before i create the excel file or csv i need to use the cursor as the recordsource for that excel or csv file.
maybe i can ask them if i can instead of making this just like numbers, if i can put a letter, so then 25, still will be 1025 by using the RIGHT("10" + ALLTRIM(Employee), 4) AS Employee and any other employee like 161, maybe add an "A" in front of the 161 and so on each time the employee = to 3 characters, so now something has to be added to the
RIGHT("10" + ALLTRIM(Employee), 4) AS Employee so kind of IiF() or maybe if i scan and enscand will be easy for me to apply the Iif() as i not to good doing this.
if they can accept Letters in front of the 3 characters employee # can you help figure out what can be the changes i need to add to "RIGHT("10" + ALLTRIM(Employee), 4) AS Employee" when the employee = 3 characters ?
thanks in advance

 

[SkipVought]

so the company that needs this employee number file csv or excel need all the employee numbers to be as character width =4

So why not RIGHT("00" + ALLTRIM(Employee), 4). Solves BOTH issues: the company gets 4 character “numbers” and Excel strips the leading zeros.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Koen Piller]

Hi

If you have "the employee field values can be as "125, "25", "5150" and so on, it will never has more than 4 characters long"
and than make a select as

SELECT RIGHT("10" + ALLTRIM(Employee), 4) AS Employee FROM empname INTO CURSOR junk nofilter

you will end up with 0125, 1025,5150 and so on. Meaning the '10' prefix for a 4 digit string of 3 will endup in a prefix of '0' only!
So you must change your logic.

If you insist you can use Vilhelm's procedure copytoxlsx6.prg this will give an Excel 'Employee.xlsx' as attached.

https://files.engineering.com/getfi...ca5-4563-a134-fde7a0174e95&file=employee.xlsx

Vilhelm'procedure is to be downloaded from his blog:

http://praisachion.blogspot.com

Koen

 

[Olaf Doschke]

What you need is a better way than COPY TO to let Excel know it should NOT drop leading zero and NOT interpret the values as numbers.

I talked about this in detail in other threads already and explained why some options to create Excel are better than others, too lazy to point to posts.

CSV doesn't need a fixed width output value, because it means comma separated and the width between two commas can be anything. If you need CSV you could write out the original values. Especially when 25 later should be read in as 25 and not 1025, it makes no sense to prefix that with 10. If you pad numbers with something else than just 0, to prevent excel from shortening them again before you output that to CSV, then you have the problem I outlined. Employee 1025 and 25 are indistinguishable, you can only pad with 0 and you have to "teach" Excel to not interpret 0025 as a number, but keep it as a string, so your output file has 0025 in it instead of 25.

VFP has EXPORT besides COPY to and TYPE CSV besides TYPE XLS, why go the extra route of Excel, if you only want CSV?

If you need fixed width output without commas that's type SDF, not CSV.

Perhaps you should first find out exactly what you need and then ask a new question in a new post. If your customer needs a file compatible to a specific SSIS process based on flat file with a specific format file, that's not CSV, that's a specification they could give you as such a format file for bcp utility would exactly describe how each column has to be in width and even formatting, eg ymd or mdy or whatever date part order in date type data. If you only get told to deliver a flat file, then that's just stupid, because exact specifications exist and one way to output them would be going through SQL server with your dbf data and using exactly that format file. There are really nice ways, so such import/export tasks can become seamless, but this isn't something only one side is responsible for or to blame. Communication about that is important. Like every time, when it comes to IT: specifications.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name