how to implement sub queries in webi

I have this query and I want to know the procedure/steps to be followed in implementing this sql in webi , below is sql, basically what i have done is without taking the sub-query i have just taken the count(c1.no) and min(c1.dt) without including the min(c2.dt) but it gives me the error as invlaid column type for second column where we the count(c1.no) .... can anyone please help me with this process as how i could implement it ... would greatly appreciate your help !

thanks in advance for your time

SELECT
  D6.DS,                        
  COUNT(C1.NO),                        
  MIN(C1.DT-(SELECT MIN(C2.DT)
        FROM C2
        WHERE C1.NO  =  C2.NO
        AND C2.ST  =  700
        GROUP BY C1.NO)),       
  MAX(C1.DT-(SELECT MIN(C2.DT)
        FROM C2
        WHERE C1.NO  =  C2.NO
        AND C2.ST  =  700
        GROUP BY C1.NO)),            
  AVG(C1.DT-(SELECT MIN(C2.DT)
        FROM C2
        WHERE C1.NO  =  C2.NO
        AND C2.ST  =  700
        GROUP BY C1.NO))            
FROM
  C1,
  D4,
  D6
WHERE
       C1.TM  =  D4.EM
   AND D4.TT <> 'JIK'
   AND D4.TT  =  D6.TT
GROUP BY
  D6.DS
ORDER BY
  D6.DS
Radhs74Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Reza RadConsultant, TrainerCommented:
could you paste DESC of your tables here?
Radhs74Author Commented:
I DO not have access to the db backend  where i can  get the desc ... any other way is possible , please let me know, i am only given user rights to create or design the reports

thanks
Radhs74Author Commented:
Can anyone help me with this as I got to complete this assignment asap

thanks
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Helena Markováprogrammer-analystCommented:
It is rather difficult to help you without needed informations. Maybe the problem is in GROUP BY C1.NO):

SELECT
  D6.DS,                        
  COUNT(C1.NO),                        
  MIN(C1.DT-(SELECT MIN(C2.DT)
        FROM C2
        WHERE C1.NO  =  C2.NO
        AND C2.ST  =  700
        GROUP BY C1.NO)),  -- >      you can try it with GROUP BY C2.NO
  MAX(C1.DT-(SELECT MIN(C2.DT)
        FROM C2
        WHERE C1.NO  =  C2.NO
        AND C2.ST  =  700
        GROUP BY C1.NO)),   -- >      you can try it with GROUP BY C2.NO          
  AVG(C1.DT-(SELECT MIN(C2.DT)
        FROM C2
        WHERE C1.NO  =  C2.NO
        AND C2.ST  =  700
        GROUP BY C1.NO))  -- >      you can try it with GROUP BY C2.NO            
FROM
  C1,
  D4,
  D6
WHERE
       C1.TM  =  D4.EM
   AND D4.TT <> 'JIK'
   AND D4.TT  =  D6.TT
GROUP BY
  D6.DS
ORDER BY
  D6.DS
Radhs74Author Commented:
ok I will try and update you

thanks
Radhs74Author Commented:
I am having issues in this part  where I got to take min(c1.dt-(select min(c2.dt) part as it is not taking that
it is giving the error as invalid identifier even though it is one of the column dt in the table c1
MIN(C1.DT) -(SELECT MIN(C2.DT)
        FROM C2
        WHERE C1.NO  =  C2.NO
        AND C2.ST  =  700
        GROUP BY C1.NO)),  -- >      you can try it with GROUP BY C2.NO
I tried what henka has suggested also ... but its not working ..... I need help , your help would be greatly appreciated. I got to complete this assignment asap ....!

thanks
Radhs74Author Commented:
hi henka

those links are not working ... can you give me any other urls please, i am getting 404 error page not found

thanks
Radhs74Author Commented:
can anyone define the above sql using the analytic functions please

thanks
SharathData EngineerCommented:
can you try like this?
SELECT   DS, 
         COUNT(NO), 
         MIN(COl_1), 
         MAX(COL_2), 
         AVG(COl_3) 
FROM     (SELECT D6.DS, 
                 C1.NO, 
                 C1.DT - (SELECT   MIN(C2.DT) 
                          FROM     C2 
                          WHERE    C1.NO = C2.NO 
                                   AND C2.ST = 700 
                          GROUP BY C1.NO) AS COl_1,  -- >      you can try it with GROUP BY C2.NO 
                 C1.DT - (SELECT   MIN(C2.DT) 
                          FROM     C2 
                          WHERE    C1.NO = C2.NO 
                                   AND C2.ST = 700 
                          GROUP BY C1.NO),   -- >      you can try it with GROUP BY C2.NO            
                 C1.DT - (SELECT   MIN(C2.DT) 
                          FROM     C2 
                          WHERE    C1.NO = C2.NO 
                                   AND C2.ST = 700 
                          GROUP BY C1.NO)  -- >      you can try it with GROUP BY C2.NO             
          FROM   C1, 
                 D4, 
                 D6 
          WHERE  C1.TM = D4.EM 
                 AND D4.TT <> 'JIK' 
                 AND D4.TT = D6.TT) AS t1 
GROUP BY DS 
ORDER BY DS

Open in new window

Helena Markováprogrammer-analystCommented:
I am suprised that given link don't work. I have tested it right now and it is all right.
Radhs74Author Commented:
Hi sharath

I will try and let you know

thanks
rads
Radhs74Author Commented:
sharath i am getting sql command not properly ended .... see the sql everything seems to look ok for me

SELECT DCA,  COUNT(PNO),  Min(CDT1),  Max(CDT2),  Avg(CDT3)
 from ( SELECT
   TD006.DCA  AS DCA,
   TC001.PNO AS PNO,
   TC001.CMDT - (SELECT MIN(GC002.TRT)   FROM GC002.TRT  WHERE  TC001.PNO=GC002.PNO AND GC002.OA=5            GROUP BY GC002.PNO) AS CDT1,
   TC001.CDT - (SELECT MIN(GC002.TRT)   FROM GC002.TRT  WHERE  TC001.PNO=GC002.PDNO AND GC002.OA=5            GROUP BY GC002.PNO) AS CDT2,
   TC001.CDT - (SELECT avg(GC002.TRT)   FROM GC002.TRT  WHERE  TC001.PNO=GC002.PNO AND GC002.OA=5            GROUP BY GC002.PNO) AS CDT3
 FROM  
  TC001,
  TD004,
  TD006  
WHERE
   TC001.CAR  =  @prompt('Enter Car:','A','Tc001\Car',Mono,Free,Persistent,,User:0)
   AND    TD004.CT  <>  'JIK'
   AND    TBD004.CT  =  TD006.CT
   AND    TC001.ITM  =  TD004.IEM)   AS T2
GROUP BY  TD006.DSCA

thanks
rads
Radhs74Author Commented:
i have taken out the trt  here "FROM GC002.TRT " but it is still the same error
Radhs74Author Commented:
sharath

I have taken out the alias for the sql that is "AS T2" and also the group by clause in the last , I gave as "GROUP BY  DSCA" instead of "GROUP BY  TD006.DSCA"
now it is giving the error as "ORA -00923 Inconsistent data types expected number got date WIS-10901
actually speaking the datatypes for TC001.CMDT  and GC002.TRT is date datatype , so i am not sure where the issue is .... can you let me know where I got to check for

thanks
rds
Radhs74Author Commented:
Sharath I am trying this sql in webi not in the ordinary sql environment as count function is not working in webi
SharathData EngineerCommented:
try removing table alias.
SELECT DCA,  COUNT(PNO),  Min(CDT1),  Max(CDT2),  Avg(CDT3)
 from ( SELECT 
   TD006.DCA  AS DCA,
   TC001.PNO AS PNO,
   TC001.CMDT - (SELECT MIN(GC002.TRT)   FROM GC002.TRT  WHERE  TC001.PNO=GC002.PNO AND GC002.OA=5            GROUP BY GC002.PNO) AS CDT1,
   TC001.CDT - (SELECT MIN(GC002.TRT)   FROM GC002.TRT  WHERE  TC001.PNO=GC002.PDNO AND GC002.OA=5            GROUP BY GC002.PNO) AS CDT2,
   TC001.CDT - (SELECT avg(GC002.TRT)   FROM GC002.TRT  WHERE  TC001.PNO=GC002.PNO AND GC002.OA=5            GROUP BY GC002.PNO) AS CDT3
 FROM  
  TC001,
  TD004,
  TD006  
WHERE
   TC001.CAR  =  @prompt('Enter Car:','A','Tc001\Car',Mono,Free,Persistent,,User:0)
   AND    TD004.CT  <>  'JIK'
   AND    TBD004.CT  =  TD006.CT
   AND    TC001.ITM  =  TD004.IEM)
GROUP BY  DSCA

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Radhs74Author Commented:
I did it is giving error as inconsistent datatypes .... sharath

I have taken out the alias for the sql that is "AS T2" and also the group by clause in the last , I gave as "GROUP BY  DSCA" instead of "GROUP BY  TD006.DSCA"
now it is giving the error as "ORA -00923 Inconsistent data types expected number got date WIS-10901
actually speaking the datatypes for TC001.CMDT  and GC002.TRT is date datatype , so i am not sure where the issue is .... can you let me know where I got to check for .I tried the conversion functions also but to no avail

thanks
rds
Radhs74Author Commented:
hi all

i got the issue resolved basically it was the data type which it is not accepting in webi

thanks to all
rads
SharathData EngineerCommented:
Didn't get time to look into this question? I hope this issue is resolved. let me know.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.