Invisible Space In my PSQL database

[Alvin Chong]

I have 3 similar databases in the PSQL which is used for my ERP system.
First database = dbA, Second database dbB, Third database dbC
Mostly we use dbA as our main database to link to the ERP, dbB & dbC is a test database for the ERP system.

Recently, I exported all data from the dbA and import into dbB & dbC to do some testing.
I found something weird that I cannot get the full result in the dbB & dbC.

An example I run the SQL query in the PCC: Select * From PO_DATA_TABLE Where PO_NO = '12345'
dbA gets correct results. (5 records)
dbB gets nothing. (0 record)
dbC gets record but less. (2 records)

But once I change the SQL query to Select * From PO_DATA_TABLE Where LTrim(PO_NO) = '12345'
Now dbA & dbB & dbC can get the correct results.

Not only the table "PO_DATA_TABLE", so far I found 4 tables having this issue.
I told to my ERP System support about this but they cannot fix my problem, they just ask me to apply the hotfixes for the ERP system.

 

[Alvin Chong]

This is my PCC info

PSQL Control Center
PSQL Install Version 12.11.025.
Java Version 1.7.0_51.

This is my server PSQL info

Actian PSQL v12 Server Engine SP1
Version 12.11.025

I did reinstall the Server's PSQL Engine with loading back all the previous data, but it still the same.

 

[mirtheil]

If you change the query to:

Select * From PO_DATA_TABLE Where PO_NO like '%12345%'

Do you get the same results in all of the databases? If so, you can compare the results to determine which ones are missing and what character is left padding the PO_NO.

If this is a data issue, reinstalling the PSQL engine won't change anything.
My guess is that the ERP system is, in some cases, storing the PO_NO with a space (or some other character). The ERP system hotfixes may ignore that character.

Mirtheil

http://www.mirtheil.com

 

[Alvin Chong]

Mirtheil, thanks for your reply.

Yes, I can get the same results in all my database.
The results are totally the same, no space & no extra characters in front of PO_NO.

I take another example.
ROUTER_TABLE has the same issue.

When I try to run the query below and get the result without a problem.

Select * From ROUTER_TABLE Where Router_Type = 'L'

But I try to run the query below and I get zero results.

Select * From ROUTER_TABLE Where Router_Type = 'I'

So I need to run the query like below and I can get the full results.

Select * From ROUTER_TABLE Where LTrim(Router_Type) = 'I'

For now, I don't think I will run the ERP system hotfixes, every time I apply the hotfixes, it will get me more error and trouble.
I hope can fix the problem directly within the PSQL, so I came here asking for expert/professional.
I have created a lot of view table in my database, it could affect my view table results.
I won't know which the table having trouble until my user tell me the record they get is not completely.

Maybe I will try to apply the ERP System hotfixes after few months I cannot get the PSQL's problem fixed.

 

[mirtheil]

What are the data types for these fields like PO_NO or Router_Type? Are there indexes on these fields? Did the DDFs for these tables come from the ERP company or somewhere else? I've seen cases where the DDFs have the wrong datatype and it can cause problems like you're seeing.


Mirtheil

http://www.mirtheil.com

 

[Alvin Chong]

Both data types are CHAR and indexes.
I think the problem is the table, I tried to replace the data file with the original data file and using "INSERT" to import data.
I tried many things on it, but still the same.

 

[mirtheil]

What do you mean by "the problem is the table?" The DDFs are there to describe the data file. The "table" is just a logical definition of the record structure of the file.
What are the indexes on the data file? You can get that information by issuing a BUTIL -STAT on the data file.
It is possible the index on the file is different than what the DDF defines.

Looking back, I'm starting to wonder if this is a data issue. If the 'L' value works but the 'I' value doesn't, then the issue isn't the table structure, indexes, or record layout, it's the data.
A few questions:
- When you replaced the data file with the original data, did it have the problem?
- What was the "INSERT" statement you used to import the data?
- What are the exact differences between the databases?
- What other things have you tried?
- What is your end goal? Do you want to get the three databases in sync? Is there data in the 'bad' databases that needs to be in the 'good' database?

Mirtheil

http://www.mirtheil.com

 

[Alvin Chong]

Let me explain what I know about the relationship between the database and ERP system.
As I said before I have 3 databases, which is 1 databases = 1 company in ERP System.
A new database will be created by ERP System when I creating a new company and it will generate all the required table for the ERP system also.
The data file for some table, I don't know how it works, but there has 2 or more table's source file is the same data file.
Last time I said 1 database is main and 2 databases are for testing, which means one is the main company and another two is test company.
Inside the ERP System, it has a function to copy all the data from a company to another company, the log panel will display it copying the data file to another location for selected company/database.

Hope you can understand my explain at the above, sorry for my poor English.

Back to your question.
Why I said, the problem is the table, this is because the same data file but I replace over to another database and it gives me the different results.
I did check the table's structure which is "Columns", "Indexes" and "Foreign Keys", both tables are totally the same, I think is something wrong with the table.
I want to say sorry about this for my unprofessional explanation, also sorry about if I misunderstanding for it.

Sorry, I don't understand "BUTIL -STAT" use for?

About the DDF, as I know normally the data file extension will be ".MKD", but most of the table's source file has no extension included the source file of "ROUTER_TABLE" & "PO_DATA_TABLE".

- When you replaced the data file with the original data, did it have the problem?
Not sure because there has no record/data line in the original data file

- What was the "INSERT" statement you used to import the data?
"INSERT INTO ROUTER_TABLE (col1,col2,etc.) VALUES ('val1','val2',etc.);"
But I will get some error for this "INSERT" statement I think the cause is the table's data file shared to another table.

- What are the exact differences between the databases?
Much similar as above I explained, all tables and structures are generated by the ERP system.

- What other things have you tried?
I tried the function "Export Table Schema" and execute the exported SQL file, but I get an error to creating indexes.
I tried to copy & replace the data file to another database, but I can get & cannot get the full result to the different database's table.
I tried to export all the data by using "Export Data" and delete all the data then import all the data by using "Import Data", but I get some error.
I tried to export all the data by using "Export Data" and replace the original data file then import the data by using "Import Data", but I get some error also.

- What is your end goal? Do you want to get the three databases in sync? Is there data in the 'bad' databases that needs to be in the 'good' database?
I just think is it possible to fix the problem of getting the full result without using "LTRIM", "RTRIM", "LIKE = '%%'".
I had more than one hundred of excel file which is using ODBC to link the database data for reporting.