issue with WITH clause and throing eroor like WITH IS NOT ALLOWED IN A SUBQUERY OR A VIEW

Database
Enthusiast

issue with WITH clause and throing eroor like WITH IS NOT ALLOWED IN A SUBQUERY OR A VIEW

Hi 

am facing an issue with WITH clause and throing eroor like WITH IS NOT ALLOWED IN A SUBQUERY OR A VIEW

pls post syntax to use in BTEQ scrip?

how to use with clause on DBC.DISKSPACE . any help will be appreciated

INSERT INTO TD_DISK_USG

(

DATABASENAME

,CUR_DT

,PRMNT_SPC_ALCTD

,PRMNT_SPC_USD

,RMNG_SPC_GBS

,PRCNT_SPC_USD

,DSK_EFCNCY

)

SELECT

DATABASENAME

,CURRENT_DATE

,SUM(MAXPERM)    AS PRMNT_SPC_ALCTD

,SUM(CURRENTPERM) AS PRMNT_SPC_USD

,(SUM(MAXPERM) - SUM(CURRENTPERM))/(1024*1024*1024) AS RMNG_SPC_GBS

,SUM(CURRENTPERM)/SUM(MAXPERM)*100  AS PRCNT_SPC_USD

,(AVG(CURRENTPERM)/(.1+MAX(CURRENTPERM))*100) AS DSK_EFCNCY

FROM DBC.DISKSPACE

GROUP BY DATABASENAME

HAVING PRMNT_SPC_ALCTD > 0

WITH PRMNT_SPC_ALCTD,PRMNT_SPC_ALCTD,PRCNT_SPC_USD

ORDER BY PRCNT_SPC_USD

;

 

5 REPLIES
Enthusiast

Re: issue with WITH clause and throing eroor like WITH IS NOT ALLOWED IN A SUBQUERY OR A VIEW

Can with clause option be inserted? I think they are for reporting only. I am not sure, since the output can be variegated. Let us learn from others who may have done this.

Teradata Employee

Re: issue with WITH clause and throing eroor like WITH IS NOT ALLOWED IN A SUBQUERY OR A VIEW

What are you trying to accomplish with the WITH clause? The syntax above does not match any WITH syntax in Teradata SQL. 

Enthusiast

Re: issue with WITH clause and throing eroor like WITH IS NOT ALLOWED IN A SUBQUERY OR A VIEW

Thanks Raja. i'm trying to gather data about the Database and Table space utilization. i have modified the query since the above query is not working.i can see the same results when am running bothe select statments.

INSERT INTO V_TRDT_DSK_USG 

(

DATABASENAME

,CUR_DT

,PRMNT_SPC_ALCTD

,PRMNT_SPC_USD

,RMNG_SPC_GBS

,PRCNT_SPC_USD

,DSK_EFCNCY

)

SELECT

DATABASENAME     

,CURRENT_DATE                         

,SUM(MAXPERM)    AS PRMNT_SPC_ALCTD

,SUM(CURRENTPERM) AS PRMNT_SPC_USD 

,(SUM(MAXPERM) - SUM(CURRENTPERM))/(1024*1024*1024) AS RMNG_SPC_GBS

,SUM(CURRENTPERM)/SUM(MAXPERM)*100  AS PRCNT_SPC_USD    

,(AVG(CURRENTPERM)/(.1+MAX(CURRENTPERM))*100) AS DSK_EFCNCY          

FROM DBC.DISKSPACE                                        

GROUP BY DATABASENAME

HAVING PRMNT_SPC_ALCTD > 0

;

(am using ORDER BY in next step) 

if am using or not using WITH clause, returning same results. Can i go-ahead and use this query or will it make any data diff?

Enthusiast

Re: issue with WITH clause and throing eroor like WITH IS NOT ALLOWED IN A SUBQUERY OR A VIEW

As far as I see, it does not make a difference.

As for the calculations , I suggest to have a look at the recommendations in this link.

https://forums.teradata.com/forum/database/space-in-the-database

 I am not able to understand why you have .1 +

Enthusiast

Re: issue with WITH clause and throing eroor like WITH IS NOT ALLOWED IN A SUBQUERY OR A VIEW

Thanks Raja. i have gone through the link and it is very usefull. this pseudo query is provided by businees as per their requirement. will update you once i get info