How to bring back one record per account based on one field?

[jcuiskelly]

I need to pull back the first code per account.  I keep getting an error with the following SQL.  The error is 'sqlPrepareWithOptions' status='-9'.  How can this be fixed?  Thank you.

SELECT
A.ACCOUNT,
A.SEQ,
B.DATE,
B.CODE

FROM TABLE1 A INNER JOIN TABLE2 B ON A.ACCOUNT = B.ACCOUNT(SELECT MIN(DATE) AS ORIGINAL,ACCOUNT FROM TABLE2  GROUP BY ACCOUNT) MINRESULTS

WHERE
A.ACCOUNT IN ('1234','5678','90123','4567','89012') AND
A.ACCOUNT = MINRESULTS.ACCOUNT AND
B.DATE = MINRESULTS.ORIGINAL;

If there is a way not to use SQL and use a JOIN query, that would be great.

[rockytopmark]

When you get the error, there should be more detail following the information you posted (you may need to click see Details).  I suggest you check that and see if you get more descriptive information about the cause of your error, and pass on to us if so.

[jcuiskelly]

Is this what is needed?

-<queryProblems>
<message code="901"location="./queries/query/selection/dataItem[1]/expression"severity="error"title="QE-DEF-0459 CCLException"type="expression">QE-DEF-0030 Expression parsing error.
QE-DEF-0359 The query contains a reference to at least one object '[SQL1].[ACCOUNT]' that does not exist.

</message>
<message code="901"location="./queries/query/selection/dataItem[5]/expression"severity="error"title="QE-DEF-0459 CCLException"type="expression">QE-DEF-0030 Expression parsing error.
QE-DEF-0359 The query contains a reference to at least one object '[SQL1].[DATE]' that does not exist.

</message>
<message code="901"location="./queries/query/selection/dataItem[4]/expression"severity="error"title="QE-DEF-0459 CCLException"type="expression">QE-DEF-0030 Expression parsing error.
QE-DEF-0359 The query contains a reference to at least one object '[SQL1].

Code Select Expand

' that does not exist.

</message>
<message code="901"location="./queries/query/selection/dataItem[6]/expression"severity="error"title="QE-DEF-0459 CCLException"type="expression">QE-DEF-0030 Expression parsing error.
QE-DEF-0359 The query contains a reference to at least one object '[SQL1].[CODE]' that does not exist.

</message>
<message code="901"location="./queries/query/selection/dataItem[7]/expression"severity="error"title="QE-DEF-0459 CCLException"type="expression">QE-DEF-0030 Expression parsing error.
QE-DEF-0359 The query contains a reference to at least one object '[SQL1].[CODE]' that does not exist.

</message>
<message code="901"location="./queries/query/selection/dataItem[3]/expression"severity="error"title="QE-DEF-0459 CCLException"type="expression">QE-DEF-0030 Expression parsing error.
QE-DEF-0359 The query contains a reference to at least one object '[SQL1].[DATE]' that does not exist.

</message>
<message code="901"location="./queries/query/selection/dataItem[4]/expression"severity="error"title="QE-DEF-0459 CCLException"type="expression">QE-DEF-0030 Expression parsing error.
QE-DEF-0359 The query contains a reference to at least one object '[SQL1].[CODE]' that does not exist.

</message>
</queryProblems>



Also, I am not sure if this affects the error message or not, I have the following Data Item Expressions which is not part of the SQL.
1) CONCAT(substring ( cast ([DATE], VARCHAR(255)), 1,4) ,substring (cast ([DATE], VARCHAR(255)), 6,2))    I use this so I can have one row per month.
2) CASE WHEN [CODE]='1' THEN COUNT(DISTINCT([CODE])) END
3) CASE WHEN [CODE]='2' THEN COUNT(DISTINCT([CODE])) END
[/code][/code][/code][/code][/code][/code][/code]

[paddhu]

Why don't you have only one join (A with B, sorted by date) and pull the Top 1 row in the outer query...
Sorry, for not having the time to give you a sample query...

[blom0344]

Using top 1 will only fetch exactly 1 row from the set. You want one row for each account.

If you want to achieve this through joins only you need an additional query subject:

Code Select Expand


SELECT
A.ACCOUNT as AJOIN,
min(B.DATE) as BJOIN
from
TABLE1 A INNER JOIN TABLE2 B ON A.ACCOUNT = B.ACCOUNT
group by A.ACCOUNT



and join this object  through AJOIN and BJOIN to the existing table. Fetching either AJOIN or BJOIN will then automatically give you the required result (provided no duplicates on date exist for each account)

In its entirety is would be:

SELECT RESULT.* FROM
(SELECT
A.ACCOUNT,
A.SEQ,
B.DATE,
B.CODE
FROM TABLE1 A INNER JOIN TABLE2 B ON A.ACCOUNT = B.ACCOUNT
WHERE
A.ACCOUNT IN ('1234','5678','90123','4567','89012') ) TABLE_AB
INNER JOIN
(SELECT MIN(DATE) AS ORIGINAL,ACCOUNT FROM TABLE2  GROUP BY ACCOUNT) MINRESULTS
ON 
TABLE_AB.ACCOUNT = MINRESULTS.ACCOUNT AND TABLE_AB.DATE = MINRESULTS.ORIGINAL) RESULT