CODEWITHSUNDEEP
sqlpostgresql
born2net
born2net

Reputation: 24983

PostgeSQL giving me "subquery in FROM must have an alias" error but all sub queries have alias

I hope someone can help as this is very frustraing, I have a long SELECT with sub selects as I need multiple segments and I am getting a missing alias error anytime I add the last INNER JOIN at the bottom, please assist :( thank you for the time:

    SELECT 
    aa.transaction,
    aa.meta_block_timestamp,
    aa.blockchain_transaction_id,
    COUNT(aa.to_val), SUM(aa.value_int) FROM (

  SELECT DISTINCT 
        transactions.transaction, 
        transactions.blockchain_transaction_id,
        transactions.meta_block_timestamp,
        to_val, value_int 
    FROM accounts INNER JOIN transactions
        ON accounts.account = transactions.origin
            INNER JOIN blockchain
                ON blockchain.number = transactions.blockchain_transaction_id
                    INNER JOIN clauses
                        ON transactions.transaction = clauses.transaction_id  
                            WHERE accounts.account = LOWER('0xe0EB562acD5F10A60EB3c37886C58fe90d7086BE') 
    ) as aa             

   INNER JOIN (     
        /* adding this SELECT below causes the problem :( */
        SELECT yy.transaction, count(yy.to_val) FROM (
          SELECT DISTINCT transaction, clauses.to_val FROM transactions 
             INNER JOIN clauses 
                 ON transactions.transaction = clauses.transaction_id
                    WHERE transactions.transaction = LOWER('0x90154fb433a6f2a308fd0b27e50f4a29d22c1cb8c7e4b00cbf608dd0b0019ce8')
                    GROUP BY yy.transaction
          ) as yy 
  ) as BB on aa.transaction = BB.transaction  

  GROUP BY  

      BB.transaction,
      aa.transaction, 
      aa.meta_block_timestamp,
      aa.blockchain_transaction_id
  ORDER BY aa.meta_block_timestamp DESC

Sean

Upvotes: 0

Views: 105

Answers (1)

Vinh Can Code
Vinh Can Code

Reputation: 427

I fixed the SQL, add BB alias, hope it helps. (I really don't know your tables and purpose of SQL)

SELECT 
    aa.transaction,
    aa.meta_block_timestamp,
    aa.blockchain_transaction_id,
    COUNT(aa.to_val), SUM(aa.value_int) FROM (

  SELECT DISTINCT 
        transactions.transaction, 
        transactions.blockchain_transaction_id,
        transactions.meta_block_timestamp,
        to_val, value_int 
    FROM accounts INNER JOIN transactions
        ON accounts.account = transactions.origin
            INNER JOIN blockchain
                ON blockchain.number = transactions.blockchain_transaction_id
                    INNER JOIN clauses
                        ON transactions.transaction = clauses.transaction_id  
                            WHERE accounts.account = LOWER('0xe0EB562acD5F10A60EB3c37886C58fe90d7086BE') 
    ) as aa             

   INNER JOIN (     
        /* adding this SELECT below causes the problem :( */
        SELECT yy.transaction, count(yy.to_val) FROM (
          SELECT DISTINCT transaction, clauses.to_val FROM transactions 
             INNER JOIN clauses 
                 ON transactions.transaction = clauses.transaction_id
                    WHERE transactions.transaction = LOWER('0x90154fb433a6f2a308fd0b27e50f4a29d22c1cb8c7e4b00cbf608dd0b0019ce8')
          ) as yy 
  ) as BB on aa.transaction = BB.transaction  

  GROUP BY  
      BB.transaction,
      aa.transaction, 
      aa.meta_block_timestamp,
      aa.blockchain_transaction_id
  ORDER BY aa.meta_block_timestamp DESC

Upvotes: 1

Related Questions