0

I'm just starting to learn SQL, and so far (since two days ago) i managed to solve most of my problems, but now i need help.

The query below results in 1 column and row: "12345" and some extra rows with the value 0, that i need to get rid of.

SELECT product_id FROM sale_order_line WHERE order_id IN (SELECT id FROM sale_order WHERE name='S00062')

So i tried the following:

SELECT product_id FROM sale_order_line WHERE order_id IN (SELECT id FROM sale_order WHERE name='S00062') AND NOT product_id='0'

And now i get the result '12345' i was expecting. But when i try to get the name of the the column related to '12345' from another table....:

SELECT name FROM product_template WHERE product_variant_ids IN (SELECT product_id FROM sale_order_line WHERE order_id IN (SELECT id FROM sale_order WHERE name='S00062') AND NOT product_id='0')

I get an error saying: "Cannot compare data type of integer to string". Somehow it appears the code above tries to read product_id with all the 0s before they're all removed. I'm guessing.

But, if i do it directly, like:

SELECT name FROM product_template WHERE product_variant_ids = '12345'

It works! How can i fix this? Please forgive my newbie coding skills.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Telmo
  • 127
  • 8
  • Please provide your version of Postgres and basic table definitions (`CREATE TABLE` statements showing data types and constraints) with any quession like this. – Erwin Brandstetter Jun 19 '20 at 22:36
  • Thanks for replying. I actually don't know my postgresql version. Can i check it from SQL? I'm accessing my company ERP (ODOO), online version, which i'm just begining to understand the database structure and i need to extract some data that i can't from the ERP itself. So i'm using a Python connector for it. So basically i need to extract data from a database i don't know, with a programming language i don't know also... – Telmo Jun 19 '20 at 23:32
  • Ask Postgres: `SELECT version();` – Erwin Brandstetter Jun 19 '20 at 23:33
  • It returned Unknown function version. – Telmo Jun 19 '20 at 23:34
  • *No* Postgres version would say that. Looks like you are using a different RDBMS. – Erwin Brandstetter Jun 19 '20 at 23:35
  • It's a connector to the ERP. It has plenty of limitations. I can't make more than one query at once, for example. – Telmo Jun 19 '20 at 23:36

1 Answers1

0

It would seem that product_template.product_variant_ids and sale_order_line.product_id are of incompatible data type, one of integer and the other a string type (text, varchar, ...) - as the error message indicates.

Make it a habit to table-qualify all column names in queries involving multiple columns.

Guessing from the column names, product_id is integer, and product_variant_ids is a string type, which is most probably a design error. If it actually holds a single valid integer in string representation, like your final query suggests, then making some more assumptions about PK constraints in your data model, your query could work like this:

SELECT pt.name
FROM   sale_order       so
JOIN   sale_order_line  sol ON sol.order_id = so.id
JOIN   product_template pt  ON pt.product_variant_ids::int = sol.product_id  -- !
WHERE  so.name = 'S00062'
AND    NOT sol.product_id = 0;  -- numeric literal without

Note the cast to integer: product_variant_ids::int. But this is just putting lipstick on a pig. The column should be type integer to begin with.

Then again, the name product_variant_ids indicates multiple values, hinting at int[], an array of integer. Then you need to do more ...

Use JOIN notation instead of nesting multiple levels of x IN (subquery). Much simpler, and typically much faster.

Both are identical as long as the subquery returns distinct values. Else, the IN notation folds duplicates, while joins do not. This difference also makes x IN (subquery) more sophisticated to execute internally and hence more expensive.

Finally, a column named product_variant_ids typically indicates misguided database design in any case. Typically, you don't cram multiple values in a single column. If that column has a string type on top of it, that's a double no-no. You have to look into relational design and database normalization some more.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I'm still in the process of understanding your code :) but, and although i know very very little of SQL, i agree with you in what i could understand. I have used JOIN notations in other queries, but i found it more confusing to 'read'. And since i can only use one query per time, i used subqueries. But i'll do better next time. I think the connector limitations will be the biggest problem. When i ran your script now, i got the error: "Invalid column name 'product_variant_ids::int' for table 'product_template'." – Telmo Jun 20 '20 at 00:21
  • @Telmo: Again, not a Postgres error message. This is Postgres-specific cast syntax. See: https://stackoverflow.com/a/13676871/939860 Try with standard SQL `ON cast(pt.product_variant_ids AS integer) = sol.product_id`. But find out the exact RDBMS and version you are dealing with before you proceed. Makes no sense this way ... – Erwin Brandstetter Jun 20 '20 at 00:24