Formatting numbers to have a leading zero (1 Viewer)

[blacksaibot]

I have an INSERT query like so:

INSERT INTO table1 (column1, column2, column3, column4, column5) 

SELECT (field1, field2, field3, field4, field1 & "" && field2)  

FROM mastertable

According to the format desired by a client, column5 from table1 needs to have field1 and field2 concatenated and represented as two-digit numbers if they're a one digit (has a leading zero).

Is there a way to achieve this?

 

[plog]

Is there a way to achieve this?

Yes, but that's not how databases work: You don't store redundant data nor calculated data. This is both.

Instead, whenever you need to use what you want Field5 to hold, you generate it by looking at Field1 and Field2.

Field 5 should not be a field in your table. What happens when field 1 gets updated? Field 5 would then be wrong. So, instead you calculate it whenever you need to use or display it, that way its always right.

 

[blacksaibot]

Yes, but that's not how databases work: You don't store redundant data nor calculated data. This is both.

I understand your concerns.

It's not up to me what data gets stored. I'm just the guy writing queries and isn't paid to ask questions.

And those fields would NEVER get updated. Once those fields are assigned they're assigned for life.

So, instead you calculate it whenever you need to use or display it, that way its always right.

That's what I'm doing. Calculating it whenever I need it. I need it for this table I'm building in my query to report it to client who wants it displayed like that.

I'd probably question this process if I were tampering with the master table, but I am not. I'm creating a new table form the mastertable so I can export it and send it to the client who wants it displayed that way.

 

[plog]

It's not up to me what data gets stored. I'm just the guy writing queries and isn't paid to ask questions....those fields would NEVER get updated

Put 'Never' in all caps, underline, bold it and highlight it if you want, it still won't convince me that the data won't change. Also, it absolutely is your job to decide how data get's stored: you're building the table.

You need to calculate it in a SELECT query or on the Report itself, not for insert into a table.

 

[namliam]

I understand your concerns.

It's not up to me what data gets stored. I'm just the guy writing queries and isn't paid to ask questions.

You are paid to fix the issues the customer is having, in a proper way....
Not just blindly do what someone thinks that is the way they want things...

Push back and fix it so that it works "properly"

To insert it into a table you do it the same way you would do in a "proper" select query

 

[blacksaibot]

Put 'Never' in all caps, underline, bold it and highlight it if you want, it still won't convince me that the data won't change.

It would only change if some one did something they're not supposed to do. And if that were to happen, thank God the correct numbers would be stored in that concatenated field so it could be fixed.

NEVER

You need to calculate it in a SELECT query or on the Report itself, not for insert into a table.

Okay. You got your point across. However, no one bothered to even tell me how to calculate it in the first place.

 

[plog]

SELECT IIf(Len(field1)=1, "0") & field1 & "-" & IIf(Len(field2)=1, "0") & field2 AS field5
FROM mastertable

 

[namliam]

I told you its the same as in a normal select, which I guessed you might know...

Personally I use Format(Yourfield, "00")

All kidding aside though there is reason to the madness of all the resistance you are recieving to trying to store this in a table....