Universe table query

[shrey3a]

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

[ray.wurlod]

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.

Code: Select all

SELECT "TYPE","F2" FROM VOC GROUP BY "TYPE","F2" HAVING COUNT(*) > 1;

I also checked with a space between COUNT and (*), and it still worked OK.

[shrey3a]

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.

Code: Select all

SELECT "TYPE","F2" FROM VOC GROUP BY "TYPE","F2" HAVING COUNT(*) > 1;

I also checked with a space between COUNT and (*), and it still worked OK.

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 data

SELECT CASE_NUMBER FROM DS_UV_STG_ALIS
GROUP BY
CASE_NUMBER
HAVING
COUNT(*) > 1;

Thanks

[shrey3a]

Its working as below

SELECT CASE_NUMBER FROM "DS_UV_STG_ALIS" GROUP BY CASE_NUMBER HAVING COUNT(*) > 1;

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.

Code: Select all

SELECT "TYPE","F2" FROM VOC GROUP BY "TYPE","F2" HAVING COUNT(*) > 1;

I also checked with a space between COUNT and (*), and it still worked OK.

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 data

SELECT CASE_NUMBER FROM DS_UV_STG_ALIS
GROUP BY
CASE_NUMBER
HAVING
COUNT(*) > 1;

Thanks

[ray.wurlod]

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.