Query needed

[Raghuraman]

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(referen ce).

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

[Lyndon Hills]

Re: Query needed

On 8 Nov 2003 06:02:39 -0800, raghuraman_ace@ rediffmail.com
(Raghuraman) wrote:
[color=blue]
>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(referen ce).
>
>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[/color]
Is that level a fixed quantity or could there also be level 3, level 4
etc?

[Raghu Raman]

Re: Query needed

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!

[Lyndon Hills]

Re: Query needed

[color=blue]
>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(referen ce).
>
>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:[/color]
[color=blue]
>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![/color]

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

[WangKhar]

Re: Query needed

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:<squvqvc41 es2fi1673mhinkc fjqbttnihq@4ax. com>...[color=blue][color=green]
> >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(referen ce).
> >
> >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:[/color]
>[color=green]
> >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![/color]
>
> 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[/color]

[--CELKO--]

Re: Query needed

>> 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.