Query needed

Hi, I've a prob in my office.i have a table with the following fields,

table : acc_file
acc_cod nvarchar(6) P.key
acc_name nvarchar(30),
level int,
acc_mas nvarchar(6) which should any one of the previously entered
values of the field acc_cod(reference).

the rows will be (example)

ASSET ASSETS 0
LIAB LIABILITIES 0
curass current assets 1 Asset
Fixass fixed assets 1 Asset
curlia current liability 1 LIAB
BANKBAL BANLBALANCES 2 curass.

I want the rows to be displayed or ordered by the acc_mas and level.As
per the accounting standards the level field is given . for instance
it needed to be

ASSET ASSETS 0 ' base level
curass current assets 1 Asset
BANKBAL BANLBALANCES 2 curass.
Fixass fixed assets 1 Asset
LIAB LIABILITIES 0 ' base level
curlia current liability 1 LIAB

HOW IT WILL BE PERFORMED

LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET

I want that the query should return the values as per the acc_mas and
level as the example given above.Is it posiable thru the query .

With Thanks .
RaghuOn 8 Nov 2003 06:02:39 -0800, raghuraman_ace@.rediffmail.com
(Raghuraman) wrote:

>Hi, I've a prob in my office.i have a table with the following fields,
>table : acc_file
>acc_cod nvarchar(6) P.key
>acc_name nvarchar(30),
>level int,
>acc_mas nvarchar(6) which should any one of the previously entered
>values of the field acc_cod(reference).
>the rows will be (example)
>ASSET ASSETS 0
>LIAB LIABILITIES 0
>curass current assets 1 Asset
>Fixass fixed assets 1 Asset
>curlia current liability 1 LIAB
>BANKBAL BANLBALANCES 2 curass.
>I want the rows to be displayed or ordered by the acc_mas and level.As
>per the accounting standards the level field is given . for instance
>it needed to be
>
>ASSET ASSETS 0 ' base level
>curass current assets 1 Asset
>BANKBAL BANLBALANCES 2 curass.
>Fixass fixed assets 1 Asset
>LIAB LIABILITIES 0 ' base level
>curlia current liability 1 LIAB
>HOW IT WILL BE PERFORMED
>
>LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
>LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET
>I want that the query should return the values as per the acc_mas and
>level as the example given above.Is it posiable thru the query .
>
>With Thanks .
>Raghu
Is that level a fixed quantity or could there also be level 3, level 4
etc?|||Yes friend , every acc_cod (pk field )may have multiple levels 3,4,5,etc
and each level may also have inner levels starting from (1,2,3, etc)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>Hi, I've a prob in my office.i have a table with the following fields,
>table : acc_file
>acc_cod nvarchar(6) P.key
>acc_name nvarchar(30),
>level int,
>acc_mas nvarchar(6) which should any one of the previously entered
>values of the field acc_cod(reference).
>the rows will be (example)
>ASSET ASSETS 0
>LIAB LIABILITIES 0
>curass current assets 1 Asset
>Fixass fixed assets 1 Asset
>curlia current liability 1 LIAB
>BANKBAL BANLBALANCES 2 curass.
>I want the rows to be displayed or ordered by the acc_mas and level.As
>per the accounting standards the level field is given . for instance
>it needed to be
>
>ASSET ASSETS 0 ' base level
>curass current assets 1 Asset
>BANKBAL BANLBALANCES 2 curass.
>Fixass fixed assets 1 Asset
>LIAB LIABILITIES 0 ' base level
>curlia current liability 1 LIAB
>HOW IT WILL BE PERFORMED
>
>LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
>LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET
>I want that the query should return the values as per the acc_mas and
>level as the example given above.Is it posiable thru the query .
>
>With Thanks .
>Raghu
>Is that level a fixed quantity or could there also be level 3, level 4
>etc?
>On 09 Nov 2003 13:20:51 GMT, Raghu Raman <raghuraman_ace@.rediffmail.com> wrote:

>Yes friend , every acc_cod (pk field )may have multiple levels 3,4,5,etc
>and each level may also have inner levels starting from (1,2,3, etc)
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!

In that case I'd be interested to see some ideas, as well. It's doable
if you know how many levels there are, but if you don't I don't think
sql server can do this with a straight query.

The following link has a discussion of this problem.

http://www.sqlteam.com/item.asp?ItemID=8866|||Hi - this sounds like something that I did recently, which required a
self cross join unioned with a self cross join and recursed. Um...
yeah anyway it was a bit wierd.

If you fancy posting some sample create and inserts we could maybe
have a bash at making something work for you.

Lyndon Hills <lyndon@.nospam.tenegi.com> wrote in message news:<squvqvc41es2fi1673mhinkcfjqbttnihq@.4ax.com>...
> >Hi, I've a prob in my office.i have a table with the following fields,
> >table : acc_file
> >acc_cod nvarchar(6) P.key
> >acc_name nvarchar(30),
> >level int,
> >acc_mas nvarchar(6) which should any one of the previously entered
> >values of the field acc_cod(reference).
> >the rows will be (example)
> >ASSET ASSETS 0
> >LIAB LIABILITIES 0
> >curass current assets 1 Asset
> >Fixass fixed assets 1 Asset
> >curlia current liability 1 LIAB
> >BANKBAL BANLBALANCES 2 curass.
> >I want the rows to be displayed or ordered by the acc_mas and level.As
> >per the accounting standards the level field is given . for instance
> >it needed to be
> >ASSET ASSETS 0 ' base level
> >curass current assets 1 Asset
> >BANKBAL BANLBALANCES 2 curass.
> >Fixass fixed assets 1 Asset
> >LIAB LIABILITIES 0 ' base level
> >curlia current liability 1 LIAB
> >HOW IT WILL BE PERFORMED
> >LEVEL 0 IS THE BASE LEVEL OF THE ASSET, LIAB, ETC
> >LEVEL 1 IS THE NEXT LEVEL OF THE ASSET IE, CURRENT ASSET, FIXED ASSET
> >I want that the query should return the values as per the acc_mas and
> >level as the example given above.Is it posiable thru the query .
> >With Thanks .
> >Raghu
> >Is that level a fixed quantity or could there also be level 3, level 4
> >etc?
> >On 09 Nov 2003 13:20:51 GMT, Raghu Raman <raghuraman_ace@.rediffmail.com> wrote:
> >Yes friend , every acc_cod (pk field )may have multiple levels 3,4,5,etc
> >and each level may also have inner levels starting from (1,2,3, etc)
> >*** Sent via Developersdex http://www.developersdex.com ***
> >Don't just participate in USENET...get rewarded for it!
> In that case I'd be interested to see some ideas, as well. It's doable
> if you know how many levels there are, but if you don't I don't think
> sql server can do this with a straight query.
> The following link has a discussion of this problem.
> http://www.sqlteam.com/item.asp?ItemID=8866|||>> As per the accounting standards the level field [sic]is given. <<

In the accounting systems I have seen, the account codes are fixed
length strrings of digits, arranged in a hierarchy, like the the Dewey
Decimal system, so you just sort them numerically. Do a GOOGLE on
"Uniform Chart of Accounts" and you will get all of the various state
requirements in the US.