Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Borislav Borissov
  Where is Borislav Borissov?
 Sofia
 Bulgaria
 Borislav Borissov
 To: Binod Binani
  Where is Binod Binani?
 Kolkata
 India
 Binod Binani
 Tags
Subject: RE: Recursive SQL query
Thread ID: 395017 Message ID: 395391 # Views: 34 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Tuesday, January 14, 2014 3:49:05 PM         
   


> >
> >
> > DECLARE @MGROUP TABLE (ICODE Int, CNAME Char(25), CPS Char(1), PICODE Int, CAL Char(1))
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1012,'CURRENT ASSETS'   ,'P', 0,   'A')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1001,'FIXED   ASSETS'   ,'P', 0,   'A')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1002,'CAPITAL'  ,'P', 0,   'L')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1004,'O/S LIABILITIES'  ,'P', 0,   'L')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1005,'SUNDRY DEBTORS'   ,'S', 1012, '')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1009,'SUNDRY CREDITORS' ,'S', 1004, '')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1011,'DRAWINGS' ,'S', 1002, '')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1041,'BANK BALANCES'    ,'S', 1012, '')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1049,'BANK OVERDRAFTS'  ,'S', 1004, '')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1023,'PLANT & MACH'     ,'S', 1001, '')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1024,'COMPUTERS','S', 1001, '')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1055,'LOCAL DEBTORS'    ,'S', 1005, '')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1056,'FOREIGN DEBTORS'  ,'S', 1005, '')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1091,'GOVT. O/S','S', 1009, '')
> > INSERT INTO @MGROUP (ICODE, CNAME, CPS, PICODE, CAL) VALUES (1092,'NON GOVT O/S'     ,'S', 1009, '')
> > 
> > 
> > ;WITH Cte_Mgrup (cName, iCode, cAl, pIcode, ChildKey)
> > AS
> > (
> >   SELECT cName, iCode, cAl, pIcode, CAST(icode as varchar(8000)) AS ChildKey FROM @MGROUP WHERE PICODE = 0
> >   UNION ALL
> >   SELECT MGROUP.cName, MGROUP.iCode, MGROUP.cAl, MGROUP.pIcode, 
> >          Cte_Mgrup.ChildKey + CAST(MGROUP.icode as varchar(8000)) AS ChildKey
> >   FROM @MGROUP  MGROUP
> >   INNER JOIN Cte_Mgrup ON MGROUP.PICODE = Cte_Mgrup.iCode
> > )
> > 
> > 
> > SELECT * FROM Cte_Mgrup ORDER BY ChildKey
> > 

> >
> >
> > -----------------
> > Borislav Borissov
> >
> > Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
> > The only thing normal about database guys is their tables.
>
> Sir
>
> Plz help.
>
> 1. How to use it (Cte_mgrup) in vfp.I mean is it any way to fetch data from
> 'cte_mgrup' to vfp cursor.
> 2. i wish to update cAL with Primary Group cAL.
> here A=Assets, L=Liability
>
> I had tried but i found Cte is not updatable.
>
> Please.
>
>
> *Exchange of $1 Create only $1, But Exchage of one Ideas Makes different two Ideas*

CTEs are not updatatble,
you should update the table you select from.

-----------------
Borislav Borissov

Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.

ENTIRE THREAD

Recursive SQL query Posted by Binod Binani @ 1/9/2014 3:14:55 PM
RE: Recursive SQL query Posted by Borislav Borissov @ 1/9/2014 3:34:28 PM
RE: Recursive SQL query Posted by Binod Binani @ 1/10/2014 7:30:51 AM
RE: Recursive SQL query Posted by Binod Binani @ 1/14/2014 3:13:42 PM
RE: Recursive SQL query Posted by Borislav Borissov @ 1/14/2014 3:49:05 PM