Need Select In Certain Domains For Space/GB & # Of Files

[tux242]

My select statement syntax must be incorrect. I am trying to get the number of files and space in GB for all but a specific domain and I am using a <> clause for the one I want to exclude, I also need to do this select run exclusively for the excluded domain itself, here is what i am using:

SELECT node_name where node_name<>'YELLOW',CAST(FLOAT(SUM(physical_mb)) / 1024 AS DEC(8,2))as "Space in GB", SUM(num_files)as"Number of files" FROM occupancy GROUP BY node_name

SELECT node_name from nodes where node_name='YELLOW',CAST(FLOAT(SUM(physical_mb)) / 1024 AS DEC(8,2))as "Space in GB", SUM(num_files)as"Number of files" FROM occupancy GROUP BY node_name

but this one below seems to work:

SELECT node_name,CAST(FLOAT(SUM(physical_mb)) / 1024 AS DEC(8,2))as "Space in GB", SUM(num_files)as"Number of files" FROM occupancy GROUP BY node_name

and this seems to work for the not equal to part of the domain node listing:

SELECT node_name from nodes where node_name<>'YELLOW'


Help Anyone!!!

Thanks,
TUX242

[rore]

Hi,
You have to put the 'where' after the 'from' in your SQL statements.

Like your query that works:

SELECT node_name from nodes where node_name<>'YELLOW'

Rudy

[tux242]

SELECT node_name from nodes where node_name<>'YELLOW',CAST(FLOAT(SUM(physical_mb)) / 1024 AS DEC(8,2))as "Space in GB",SUM(num_files)as"Number of files" FROM occupancy GROUP BY node_name
ANR2908E Unexpected SQL punctuation token - ','.

|
.................................................. ....V........
SELECT node_name from nodes where node_name<> 'YELLOW' ,CAST(FLO

ANS8001I Return code 3.

So maybe its my punctuation?

[rore]

Your sql statement is invalid.
You have two 'from' in your statement, and you have mixed the what you want to obtain (SELECT) with the condition (WHERE).

I think you want to do this:
SELECT node_name,CAST(FLOAT(SUM(physical_mb)) / 1024 AS DEC(8,2))as "Space in GB",SUM(num_files)as"Number of files" FROM occupancy where node_name<>'YELLOW' GROUP BY node_name

Take a look at this: http://www.firstsql.com/tutor2.htm


Hope this helps,
Rudy

[tux242]

I pasted wrong twice, time to start over, I am looking for a specific domain_name to be excluded not node_name...

SELECT node_name,CAST(FLOAT(SUM(physical_mb)) / 1024 AS DEC(8,2))as "Space in GB",SUM(num_files)as"Number of files" FROM occupancy where domain_name<>'YELLOW' GROUP BY node_name
ANR2940E The reference 'DOMAIN_NAME' is an unknown SQL column name.

|
..........................V....................... .............
les" FROM occupancy where domain_name<> 'YELLOW' GROUP BY node_n

ANS8001I Return code 3.

[rore]

Voila!:

SELECT node_name,CAST(FLOAT(SUM(physical_mb)) / 1024 AS DEC(8,2))as "Space in GB",SUM(num_files)as"Number of files" FROM occupancy where node_name not in (select node_name from nodes where domain_name='YELLOW') GROUP BY node_name

Rudy

[tux242]

That seems to work great TYVM!!! Sorry I'm so rusty. TUX242