All Forums Database
Kumar@TD 7 posts Joined 11/14
21 Nov 2014
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
;
 

Raja_KT 1246 posts Joined 07/09
21 Nov 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ToddAWalter 316 posts Joined 10/11
21 Nov 2014

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

Kumar@TD 7 posts Joined 11/14
23 Nov 2014

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?

 
 

Raja_KT 1246 posts Joined 07/09
23 Nov 2014

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 +
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Kumar@TD 7 posts Joined 11/14
24 Nov 2014

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

You must sign in to leave a comment.