Data View SQL Generating #1854
Description
System Info
Using pandasai 3.0.0, trying to create data view request for my PostgreSQL DB (created in accordance of your Data View Example) got error from DB while executing request.
🐛 Describe the bug
"Error: Failed to execute query for 'postgres' with: SELECT\n COUNT(*)\nFROM (\n SELECT\n \"orders_id\" AS \"orders_id\",\n \"orders_order_date\" AS \"orders_order_date\",\n \"orders_total_amount\" AS \"orders_total_amount\",\n \"orders_status\" AS \"orders_status\",\n \"products_name\" AS \"products_name\",\n \"products_category\" AS \"products_category\",\n \"products_unit_price\" AS \"products_unit_price\",\n \"products_stock_level\" AS \"products_stock_level\",\n \"customers_segment\" AS \"customers_segment\",\n \"customers_country\" AS \"customers_country\",\n \"customers_join_date\" AS \"customers_join_date\"\n FROM (\n SELECT\n \"orders\".\"id\" AS \"orders_id\",\n \"orders\".\"order_date\" AS \"orders_order_date\",\n \"orders\".\"total_amount\" AS \"orders_total_amount\",\n \"orders\".\"status\" AS \"orders_status\",\n \"products\".\"name\" AS \"products_name\",\n \"products\".\"category\" AS \"products_category\",\n \"products\".\"unit_price\" AS \"products_unit_price\",\n \"products\".\"stock_level\" AS \"products_stock_level\",\n \"customers\".\"segment\" AS \"customers_segment\",\n \"customers\".\"country\" AS \"customers_country\",\n \"customers\".\"join_date\" AS \"customers_join_date\"\n FROM (\n SELECT\n \"id\",\n \"order_date\",\n \"total_amount\",\n \"status\"\n FROM \"orders\"\n ) AS \"orders\"\n JOIN (\n SELECT\n \"name\",\n \"category\",\n \"unit_price\",\n \"stock_level\"\n FROM \"products\"\n ) AS \"products\"\n ON \"orders\".\"product_id\" = \"products\".\"id\"\n JOIN (\n SELECT\n \"segment\",\n \"country\",\n \"join_date\"\n FROM \"customers\"\n ) AS \"customers\"\n ON \"orders\".\"customer_id\" = \"customers\".\"id\"\n )\n) AS \"sales_analytics\""
I found out that this request is missing of alias after "customer_id" = "customers"."id"\n ). This look like some view request generation issue.
Add .yaml file of my view example. It completely repeats your example from the documentation.
Maybe I am wrong, thanks for help or advice!