Import only required data records

Power BI
#1

I am trying to import data from a PostgreSql source but I only want the records where a particular column starts with a specific character string.

For example I only want those records for a particular customer identified by their customer code.

I am trying to use SQL Select Where statement in the query editor but the syntax is not right.

Example: select * where customer.info LIKE ‘A0001%’ (customer.info is the column name)

Regards

Paul

#2

Hi @Paul

Are you including what table you’re selecting from?

SELECT *
FROM Customer
WHERE Customer.Info LIKE ‘A0001%’

If you are, is Customer.Info the whole column name or is the Customer part the table name? If it’s the whole column name, it will likely need to be wrapped in [ ] as the period is probably a reserved character. At least it is in TSQL, I’m not familiar with Postgre.

#3

Hi

Thank you for your reply.

Yes customer.info is the whole column name but it contains a variety of fields separated by a ~ character which I unpack later in the process and I am using the first five characters as the selector.

I am importing from a specific table called “processed_data new_data_translated”.

Best regards

Paul

#4

Then you most likely will need to bracket it, same with your table name as this has a space. For example:

SELECT *
FROM [processed_data new_data_translated]
WHERE [Customer.Info] LIKE ‘A0001%’

Does that work for you?

#5

I now get the following error.

DataSource.Error: ODBC: ERROR [42601] ERROR: syntax error at or near “[”;

regards

Paul

#6

Apologies, can you try:

SELECT *
FROM “processed_data new_data_translated”
WHERE “Customer.Info” LIKE ‘A0001%’

From reading online it appears Postgre uses quotes rather than brackets. If that doesn’t work I’m out of ideas I’m afraid as not familiar with it.

#7

Hi @PaulBoyes. I don’t know PostgresSQL at all, but using the LIKE operator in TransactSQL (used by Microsoft SQL Server) is a slow operator. I’d prefer to use a LEFT-= operator instead, so your WHERE clause would be something like

WHERE left(Customer.Info, 5) = ‘A0001’

Regardless, if you have access to a DBA (or someone else) who regularly accesses your database, get a copy of one of their queries that works and adjust the values as desired.

Hope it helps.
Greg

#8

Hi again

I tried your suggestion, see below.

SELECT * FROM (processed_data new_data_translated) WHERE left (Customer.Info,5) = ‘A0001’

I now get the following error message

ODBC: ERROR [42601] ERROR: syntax error at or near “)”;
Error while executing the query.

Regards

Paul

#9

Again, I don’t know PostgresSQL at all, but I doubt you should have brackets around your table name after the FROM. I’d follow-up with your DBA can help craft a query that works in your environment.
Greg

#10

Greg

Thank you very much for your suggestions.

The problem is, of course, that there is a space in the name of the table.

I will follow up on your suggestion.

Best regards

Paul