qb icon indicating copy to clipboard operation
qb copied to clipboard

Distinct with chunk() breaks

Open Daemach opened this issue 3 years ago • 1 comments

The following code yields an error: Incorrect syntax near '*'

qb.from('mars_in_tamarac t')
        .join('veritiadmin.dbo.accounts a', 't.account_id', 'a.portfolioCode')
        .selectRaw('a.id as accountID,account_id,openDate,closeDate,description,costBasis,quantity,proceeds,realizedGainLoss,stgl,ltgl')
        .where('closeDate', '<', account.dFirstAPXTrade)
        .distinct()
        .orderby('closeDate')
        .chunk(190, (chunk)=>{
          systemOutput('syncing #account.portfolioCode# tamarac')
          qb.from('veritiadmin.dbo.accounts_gainLoss').insert(chunk)
        } )

SQL:

SELECT COUNT(DISTINCT *) AS "aggregate" FROM [mars_in_tamarac] AS [t]  INNER JOIN [veritiadmin].[dbo].[accounts] AS [a] ON [t].[account_id] =  [a].[portfolioCode] WHERE [closeDate] < {ts '2020-12-14 00:00:00'}

Daemach avatar Sep 17 '21 11:09 Daemach