Does any SQL have the idea of "ONLY"?

[#1]

ONLY, as I understand it:

If , (for a given row, dimension, or group) has ONLY one distinct value of {Expression}, return it, else (more than one), return null.

I'm wondering if any flavor of SQL has the equivalent idea?

I think the answer is no, -- there is no direct , "out of the box" equivalent.

However, I think the same can be achieved manually, doing something like this. This is would-be SQL Server syntax -- but I don't know if the syntax is correct, I'm asking the idea in principle.... I'd be curious if any flavor of relational database supported this idea of "ONLY", but we use SQL Server.

SELECT

(CASE WHEN COUNT(DISTINCT Expression)>1 NULL() ELSE MAX(Expression) END) as OnlyExpression

GROUP BY {Dimension}

[HirisH_V7]

Hi,

The Only Function

Use of ONLY Function

Check this,

-Hirish

[#3]

Thanks Hirish, but do those list examples of SQL - style ONLY statements? I think they only discuss the idea of Qlikview's "ONLY" -- which I feel pretty confident I understand. Note my comment to Henric in the first link you posted.

I know a SQL question is not really a Qlikview question -- but I've never seen the idea of "ONLY" used *outside* of Qlikview. So I want to ask the Qlikview community first.

[sunny_talwar]

I think there are others way to achieve the result shown by Only() function. These are some of them:

=Sum(DISTINCT FieldName)

=Avg(FieldName)

=Max(FieldName)

=Min(FieldName)

.

.

.

If SQL doesn't have Only(), I am sure other options would still be available to get the same result.

[sunny_talwar]

Look at the following example:

[marcus_sommer]

If the calculations or transformations couldn't be done within the database per SQL and/or the efforts would be too expensive it is recommended to use the Preceding Load‌ and making all requirements within the qv logic - it's easy and fast.

- Marcus

[#7]

I know how ONLY in Qlikview works.

You demonstrate only a part of the ONLY function -- that it will return the unique value if there is only one. I'm more interested in the other part  -- that it will return null if there is more than one unique value.

I'm asking if SQL can do it. Can you write a SQL script that achieves ONLY? I think that I got it right in my first question.

[#8]

I also like Preceding Loads, but that's not my question.

You say "If the calculations or transformations couldn't be done within the database per SQL..." But that's precisely what I'm asking. Can the "ONLY" function be achieved using SQL alone?

I think it should be possible, indeed I propose a solution in my original question. I'm wondering if anyone can tell me if it's right or wrong -- and/or improve on it.

Forget Qlikview for a moment -- and if that's not acceptable for purposes of Qlikview community discussion, I'm fine if someone closes the question...

[marcus_sommer]

I don't know if any database provides a (proprietary) only-function - rather not - but I'm sure you could create queries which return such a result (maybe you could apply an user-defined function for it) and it could be that you need several steps to get your wanted output. But whatever you do you will need (rather slowly) aggregation loads with group by (whereby only() requirred it in qlikview, too).

But what is the aim of that question, for what do you need it? The most transforming-parts are in qlikview a lot faster then in a "normal" sql database (unless group by loadings which are in qlikview (mostly ?) single-threaded an dtherefore relative slow) - especially if by large datasets incremenal.loadings are implemented.

- Marcus

[jagan · Accepted solution]

Hi,

Try like this, it is working in Oracle

SELECT

     Dimension1,

     Dimension2,

  CASE WHEN COUNT(DISTINCT Dimension3) > 1 THEN

  NULL

  ELSE MAX(Dimension3)

  END as value

FROM TableName

GROUP BY

Dimension1,

     Dimension2;

Regards,

Jagan.