Simple SQL Question

[Max Hattenback]

Hey Das,

I would expect the query you are using to return a SQL error, since you are using an aggregate function alongside non aggregate columns and not using a group by or partition by.

try:

SELECT max(CC_SEQ),CC_CREDIT_NUM,CC_TOKEN
FROM cust_cc
GROUP BY CC_CREDIT_NUM,CC_TOKEN
WHERE CC_CCODE=@[iRowCust.CCODE]
AND CC_CURRENT=1

This should return the max cc seq for each unique combination of CC_CREDIT_NUM,CC_TOKEN that matches the where criteria.
________________________________
From: omnisdev-en <omnisdev-en-bounces at lists.omnis-dev.com> on behalf of Das Goravani <goravanis at gmail.com>
Sent: Wednesday, October 26, 2022 3:44 PM
To: OmnisDev List - English <omnisdev-en at lists.omnis-dev.com>
Subject: Simple SQL Question


I have tried to look this up, scoured others existing code, to no avail

How do you

Select the highest Primary Key value
With a couple other columns
Where it’s for a certain customer, and "current" is true

I have this so far:

SELECT max(CC_SEQ),CC_CREDIT_NUM,CC_TOKEN
FROM cust_cc
WHERE CC_CCODE=@[iRowCust.CCODE]
AND CC_CURRENT=1

It’s not working, doing it in an exec direct using the statement object

It’s returning no rows when I know there is one that matches
_____________________________________________________________
Manage your list subscriptions at https://lists.omnis-dev.com
Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com

[https://carrubba-image.s3-us-west-2.amazonaws.com/logo_smaller.png]