Page 1 of 1
Universe table query
Posted: Thu Mar 08, 2007 3:44 pm
by 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
Posted: Thu Mar 08, 2007 3:56 pm
by 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.
Posted: Thu Mar 08, 2007 4:08 pm
by shrey3a
ray.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.
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
Posted: Thu Mar 08, 2007 4:13 pm
by shrey3a
Its working as below
SELECT CASE_NUMBER FROM "DS_UV_STG_ALIS" GROUP BY CASE_NUMBER HAVING COUNT(*) > 1;
shrey3a wrote:ray.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.
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
Posted: Thu Mar 08, 2007 6:21 pm
by 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.