CODEWITHSUNDEEP
sqlt-sqlsybase
John Janssen
John Janssen

Reputation: 323

Syntax errors in sybase query

I have never used sybase before until today. I have a query in Access that is using First() and has multiple iif statements. I hoped for the best and pasted my query in and I am getting errors on the First() section. I removed the First's from the query, and I am getting errors on the iif statements.

Here is my query:

SELECT Client.ClientId, First(Trans.SysId) AS FirstOfSysId, Client.Name1,
Relation.Name1, Debtor.Name1, Account.AcctId, First(Trans.PostDate) AS FirstOfPostDate,
Sum(IIf([TransType]="uc",[Amount],IIf([TransType]="CA",[Amount],IIf([TransType]="RAC",
[Amount],IIf([TransType]="UCBR",[Amount],[amount]*-1))))) AS TransAmount, 
Trans.CheckId,Relation.RelId
FROM (((Trans INNER JOIN Account ON Trans.SysAcctId = Account.SysId) 
INNER JOIN Debtor ON Account.SysDtrId = Debtor.SysId) 
INNER JOIN Relation ON Account.SysRelId = Relation.SysId) INNER JOIN Client ON
Relation.SysClientId = Client.SysId
WHERE (((Trans.TransType)="CA" Or (Trans.TransType)="NC" Or (Trans.TransType)="UC" Or
(Trans.TransType)="AC" 
Or (Trans.TransType)="UCB" Or (Trans.TransType)="RAC" Or (Trans.TransType)="UCB" Or
(Trans.TransType)="UCBR"))
GROUP BY Client.ClientId, Client.Name1, Relation.Name1, Debtor.Name1, Account.AcctId, 
Trans.CheckId, Relation.RelId
HAVING (((Client.ClientId)=[Forms]![frmCredit]![Combo112])
AND ((Sum(IIf([TransType]="uc",[Amount],IIf([TransType]="CA",[Amount],
IIf([TransType]="RAC", [Amount],IIf([TransType]="UCBR",[Amount],[amount]*-1))))))>0 
Or (Sum(IIf([TransType]="uc",[Amount],IIf([TransType]="CA",[Amount],
IIf([TransType]="RAC", [Amount],IIf([TransType]="UCBR",[Amount],[amount]*-1))))))<0)
AND ((Relation.RelId) Not Like "281099"))
ORDER BY Relation.Name1, Account.AcctId, Trans.CheckId

[Edit] A more readable query has been added to http://sqlfiddle.com/#!2/4b98e0/1 [/Edit]

I can't find documentation on converting the first function, but with the IIF should I convert that to a Case when statement? I saw an article here, but I am not sure if it is the most efficient, since it is from 1999...

The error I am getting is Syntax error near 'FIRST' on line 1. Which based on the article here I am using it in the right syntax.

Any help on using First in the right syntax and if I should use a Case When instead of the iif is greatly appreciated.

Upvotes: 0

Views: 775

Answers (1)

jpw
jpw

Reputation: 44881

Based on what I think the query does I think you can rewrite it as below. I don't have access to any Sybase server to validate the query, so I tried it on MS SQL but I commented out some stuff with /* */ that might not be needed with Sybase.

It might be completely wrong, or it might be correct. If it's wrong, please let me know in a comment and I'll remove my answer.

SELECT 
    Client.ClientId, 
    FIRST_VALUE(Trans.SysId) /* OVER (ORDER BY Trans.Sysid) */ AS FirstOfSysId, 
    Client.Name1,
    Relation.Name1, 
    Debtor.Name1, 
    Account.AcctId, 
    FIRST_VALUE(Trans.PostDate) /* OVER (ORDER BY Trans.postdate) */ AS FirstOfPostDate,
    SUM(CASE 
          WHEN [TransType] IN ('uc', 'CA', 'RAC', 'UCBR') THEN AMOUNT 
          ELSE AMOUNT * -1 
       END
       ) AS TransAmount, 
    Trans.CheckId,
    Relation.RelId
FROM Trans 
INNER JOIN Account ON Trans.SysAcctId = Account.SysId
INNER JOIN Debtor ON Account.SysDtrId = Debtor.SysId 
INNER JOIN Relation ON Account.SysRelId = Relation.SysId
INNER JOIN Client ON Relation.SysClientId = Client.SysId

WHERE Trans.TransType IN ('CA','NC','UC','AC','UCB','RAC','UCB','UCBR')

GROUP BY 
    Client.ClientId, Client.Name1, 
    Relation.Name1, Debtor.Name1, 
    Account.AcctId, Trans.CheckId, 
    Relation.RelId /* , trans.sysid, trans.postdate */

HAVING 
    1=1 --AND (((Client.ClientId)=[Forms]![frmCredit]![Combo112])
    AND SUM(
          CASE WHEN [TransType] IN ('uc', 'CA', 'RAC', 'UCBR') THEN AMOUNT 
          ELSE AMOUNT * -1 END
          ) <> 0 
    AND Relation.RelId <> '281099'

ORDER BY Relation.Name1, Account.AcctId, Trans.CheckId

My reasoning here is that 

Sum(IIf([TransType]="uc",[Amount],IIf([TransType]="CA",[Amount],IIf([TransType]="RAC",
[Amount],IIf([TransType]="UCBR",[Amount],[amount]*-1))))) AS TransAmount,

just takes theamountifTransTypeis any of'uc', 'CA', 'RAC', 'UCBR'elseAmount*-1 and this should be equivalent with:

SUM(CASE WHEN [TransType] IN ('uc', 'CA', 'RAC', 'UCBR') THEN AMOUNT 
        ELSE AMOUNT*-1 END) AS TransAmount,

and in thehavingclause this:

((Sum(IIf([TransType]="uc",[Amount],IIf([TransType]="CA",[Amount],
IIf([TransType]="RAC", [Amount],IIf([TransType]="UCBR",[Amount],[amount]*-1))))))>0 
Or (Sum(IIf([TransType]="uc",[Amount],IIf([TransType]="CA",[Amount],
IIf([TransType]="RAC", [Amount],IIf([TransType]="UCBR",[Amount],[amount]*-1))))))<0)

just checks if the expression (sum(amount)) is either> 0or< 0and this should be equivalent with:

SUM(CASE WHEN [TransType] IN ('uc', 'CA', 'RAC', 'UCBR') THEN AMOUNT 
        ELSE AMOUNT*-1 END) <> 0

Upvotes: 1

Related Questions