Hi,
I'm trying to use having count(*) = 1 clause in universe table but somehow its failing. Can we use group by , having clause with DS Universe tables
SELECT CASE_NUMBER,LOSS_RPT_NUMBER FROM DS_UV_STG_ALIS
group by
CASE_NUMBER,LOSS_RPT_NUMBER
HAVING
count (*) > 1
Thanks
Universe table query
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes you can.
What error message do you receive from UniVerse?
I just ran a similar query (the only difference being the column names) in UniVerse.
I also checked with a space between COUNT and (*), and it still worked OK.
What error message do you receive from UniVerse?
I just ran a similar query (the only difference being the column names) in UniVerse.
Code: Select all
SELECT "TYPE","F2" FROM VOC GROUP BY "TYPE","F2" HAVING COUNT(*) > 1;
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I'm running the query in UNIVErse stage and its failing , I've checked the table I'm abl to view the data in job where I'm inserting the dataray.wurlod wrote:Yes you can.
What error message do you receive from UniVerse?
I just ran a similar query (the only difference being the column names) in UniVerse.I also checked with a space between COUNT and (*), and it still worked OK.Code: Select all
SELECT "TYPE","F2" FROM VOC GROUP BY "TYPE","F2" HAVING COUNT(*) > 1;
SELECT CASE_NUMBER FROM DS_UV_STG_ALIS
GROUP BY
CASE_NUMBER
HAVING
COUNT(*) > 1;
Thanks
Its working as below
SELECT CASE_NUMBER FROM "DS_UV_STG_ALIS" GROUP BY CASE_NUMBER HAVING COUNT(*) > 1;
SELECT CASE_NUMBER FROM "DS_UV_STG_ALIS" GROUP BY CASE_NUMBER HAVING COUNT(*) > 1;
shrey3a wrote:I'm running the query in UNIVErse stage and its failing , I've checked the table I'm abl to view the data in job where I'm inserting the dataray.wurlod wrote:Yes you can.
What error message do you receive from UniVerse?
I just ran a similar query (the only difference being the column names) in UniVerse.I also checked with a space between COUNT and (*), and it still worked OK.Code: Select all
SELECT "TYPE","F2" FROM VOC GROUP BY "TYPE","F2" HAVING COUNT(*) > 1;
SELECT CASE_NUMBER FROM DS_UV_STG_ALIS
GROUP BY
CASE_NUMBER
HAVING
COUNT(*) > 1;
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Technically the double quotes are only required when an identifier (table name, column name, etc.) is a reserved word or in the vocabulary (VOC) file. It's always safe to use them, however.
TYPE is a reserved word in UniVerse/SQL, it is used in CREATE TABLE statement to specify the hashing algorithm used in an underlying static hashed file. Similarly, F2 is a reserved word, but only insofar as it exists in the VOC file data portion so could be mistaken for something else.
TYPE is a reserved word in UniVerse/SQL, it is used in CREATE TABLE statement to specify the hashing algorithm used in an underlying static hashed file. Similarly, F2 is a reserved word, but only insofar as it exists in the VOC file data portion so could be mistaken for something else.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.