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


> > Dear Expert
> >
> > Although this type of event (SQL Qry) might be discussed earlier but still i wish to start
> > from scratch
> >
> > Please Help
> >
> > I m using MS SQL 2005.
> >
> > I have a Master File having Primary group and sub-Group. Sub-Groups datas are under Primary
> > group. here is the codes for sample data creation.
> >
> >
CREATE CURSOR MGROUP (ICODE I, CNAME C(25), CPS C(1), PICODE I, CAL C(1))
> > SELECT MGROUP
> > 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, '')
> > 
> > 
> > ** i wish output as :
> > 
> > Current Asset          1012   A
> >   sundry Debtors       1005   A
> >      Foreign Debtors   1056   A
> >      Local   Debtors   1055   A
> > O/S Liablites          1004   L
> >   Sundry Creditors     1009   L
> >      Govt O/s          1091   L
> >      Non Govt O/S      1092   L
> > 
> > 
> > Please Help.
> > 
> > Thanx in advance
> > 
> >  
> > etc...

> >
> > Earlier i thought i cud be done in fly, but since last two days, i cud not resolve the issue.
> >
> > Please help
> >
> >
> > *Exchange of $1 Create only $1, But Exchage of one Ideas Makes different two Ideas*
>
>
> 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*

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