Bill of material

I have a table with data
create table a
(
a01 char(4),
a02 char(4),
a03 int
);
insert into a
values('a','b',1);
insert into a
values('a','c',1);
insert into a
values('x','c',1);
insert into a
values('c','g',1);
insert into a
values('b','h',1);
insert into a
values('h','k',1);
insert into a
values('b','k',1);
insert into a
values('g','k',1);
if my entry where value "k", i want show result
a01,a03
a 2
x 1
how to do ....
-
thanks!Get a copy of TREES & HIERARCHIES IN SQL for help.|||thanks a lot!
this my tree sql parent to find child ,but i want child to find parent,
do you have any idea?
ALTER PROCEDURE BOMIA_PPart
(
@.MODE NVARCHAR(12)
)
AS
SET NOCOUNT ON
CREATE TABLE #TEMP_A
(
LVL INT,
PARENT NVARCHAR(12) ,
PARTNO NVARCHAR(12) ,
M CHAR(2),
P CHAR(2),
PARTDESC NVARCHAR(40) ,
UNIT NVARCHAR(3) ,
USAGE REAL,
LOCATION NVARCHAR(18) ,
SXRQ NVARCHAR(10),
SSRQ NVARCHAR(10)
)
DECLARE @.ENDTREE INT
DECLARE @.NLVL INT
SELECT @.ENDTREE = 0
SELECT @.NLVL=1
INSERT INTO #TEMP_A(LVL,PARENT,PARTNO,P,USAGE,LOCATI
ON,SXRQ,SSRQ)
SELECT 1,IB001 ,IB003 ,IB031,IB004,IB011,IB008,IB009
FROM BOMIB
WHERE IB001=@.MODE
WHILE (@.ENDTREE =0 )
BEGIN
SELECT @.NLVL=@.NLVL+1
INSERT INTO #TEMP_A(LVL,PARENT,PARTNO,P,USAGE,LOCATI
ON,SXRQ,SSRQ)
SELECT @.NLVL,
A.IB001 ,
A.IB003 ,
A.IB031,
A.IB004,
A.IB011,
A.IB008,
A.IB009
FROM BOMIB A, #TEMP_A B
WHERE A.IB001 =B.PARTNO
AND B.LVL=@.NLVL -1
AND B.P!='1'
IF NOT EXISTS (SELECT PARTNO COLLATE database_default FROM #TEMP_A WHERE
LVL =@.NLVL)
SELECT @.ENDTREE=1
END
UPDATE #TEMP_A
SET M=AA070,
PARTDESC=AA020,
UNIT=AA050
FROM INVAA,#TEMP_A
WHERE AA010=PARTNO
SELECT
CAST(REPLICATE('.',LVL)+CAST(LVL AS NVARCHAR(2)) AS CHAR(12)) AS ',
PARENT AS ',
PARTNO AS ',
M,
CASE WHEN P='1' THEN '-'
WHEN P='2' THEN '+'
WHEN P='3' THEN '*'
ELSE '.'
END AS P,
PARTDESC AS ',
UNIT AS ',
ROUND(USAGE,2) AS ',
SXRQ AS ',
SSRQ AS ',
LOCATION AS '
FROM #TEMP_A
DROP TABLE #TEMP_A
SET NOCOUNT OFF
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121909782.095848.154870@.g14g2000cwa.googlegroups.com...
> Get a copy of TREES & HIERARCHIES IN SQL for help.
>|||Did you bother to follow up my posting? TREES & HIERARCIES IN SQL
might be good read before you post again .sql

BEST UDF to handle char or varchar to smalldatatime converstion pr

Hi Experts,
I am importing data from text files, data is about 800GB, yeah thats right.
in different file and in different varchar format. like 01/01/2004 jan 17,
200, mm/dd/yy. it had bad data and good data, but there are millions and
millions records, and it has all kind of problems any body can think of.
is there any site or way to get any function which handle these problems,
or if any of you ever had chance to see this kinda function and pass to me.
thanking you in advace for your help..
-PermoodYou can use ISDATE to determine if the specified string can be converted to
a SQL Server datetime datatype: Examples:
SELECT ISDATE('1/01/2004')
SELECT ISDATE('jan 17, 200')
SELECT ISDATE('mm/dd/yy')
SELECT ISDATE('01/01/04')
You might consider performing data type validation and conversion as part of
your ETL process before importing data into SQL Server. This will likely
perform better than using an intermediate SQL Server staging table when you
have a lot of bad data and large volumes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"-Permood" <Permood@.discussions.microsoft.com> wrote in message
news:FD3FF986-932F-4FA0-9AD1-E027327AA100@.microsoft.com...
> Hi Experts,
> I am importing data from text files, data is about 800GB, yeah thats
> right.
> in different file and in different varchar format. like 01/01/2004 jan 17,
> 200, mm/dd/yy. it had bad data and good data, but there are millions and
> millions records, and it has all kind of problems any body can think of.
> is there any site or way to get any function which handle these problems,
>
> or if any of you ever had chance to see this kinda function and pass to
> me.
>
> thanking you in advace for your help..
> -Permood
>|||Thanks Dan,
here is problem, i am very new in my company, some 'DEVELOPERS' don't want
to change their way of doing and they decided to use sql staging, and
transformationg. I dont' have much experience with SQL Server either. So I
wanted to see if some thing is already there, i means any function, which and
handle most date problems and then i and enhance it according to my needs.
but thank you for your time and suggestions.
"Dan Guzman" wrote:
> You can use ISDATE to determine if the specified string can be converted to
> a SQL Server datetime datatype: Examples:
> SELECT ISDATE('1/01/2004')
> SELECT ISDATE('jan 17, 200')
> SELECT ISDATE('mm/dd/yy')
> SELECT ISDATE('01/01/04')
> You might consider performing data type validation and conversion as part of
> your ETL process before importing data into SQL Server. This will likely
> perform better than using an intermediate SQL Server staging table when you
> have a lot of bad data and large volumes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "-Permood" <Permood@.discussions.microsoft.com> wrote in message
> news:FD3FF986-932F-4FA0-9AD1-E027327AA100@.microsoft.com...
> > Hi Experts,
> >
> > I am importing data from text files, data is about 800GB, yeah thats
> > right.
> > in different file and in different varchar format. like 01/01/2004 jan 17,
> > 200, mm/dd/yy. it had bad data and good data, but there are millions and
> > millions records, and it has all kind of problems any body can think of.
> >
> > is there any site or way to get any function which handle these problems,
> >
> >
> > or if any of you ever had chance to see this kinda function and pass to
> > me.
> >
> >
> > thanking you in advace for your help..
> > -Permood
> >
>
>

BEST UDF to handle char or varchar to smalldatatime converstion pr

Hi Experts,
I am importing data from text files, data is about 800GB, yeah thats right.
in different file and in different varchar format. like 01/01/2004 jan 17,
200, mm/dd/yy. it had bad data and good data, but there are millions and
millions records, and it has all kind of problems any body can think of.
is there any site or way to get any function which handle these problems,
or if any of you ever had chance to see this kinda function and pass to me.
thanking you in advace for your help..
-Permood
You can use ISDATE to determine if the specified string can be converted to
a SQL Server datetime datatype: Examples:
SELECT ISDATE('1/01/2004')
SELECT ISDATE('jan 17, 200')
SELECT ISDATE('mm/dd/yy')
SELECT ISDATE('01/01/04')
You might consider performing data type validation and conversion as part of
your ETL process before importing data into SQL Server. This will likely
perform better than using an intermediate SQL Server staging table when you
have a lot of bad data and large volumes.
Hope this helps.
Dan Guzman
SQL Server MVP
"-Permood" <Permood@.discussions.microsoft.com> wrote in message
news:FD3FF986-932F-4FA0-9AD1-E027327AA100@.microsoft.com...
> Hi Experts,
> I am importing data from text files, data is about 800GB, yeah thats
> right.
> in different file and in different varchar format. like 01/01/2004 jan 17,
> 200, mm/dd/yy. it had bad data and good data, but there are millions and
> millions records, and it has all kind of problems any body can think of.
> is there any site or way to get any function which handle these problems,
>
> or if any of you ever had chance to see this kinda function and pass to
> me.
>
> thanking you in advace for your help..
> -Permood
>