Recursive Statement Returns "Unsupported Data Type

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Recursive Statement Returns "Unsupported Data Type

Author

Topic

MTaft
Starting Member

4 Posts

Posted - 2009-07-08 : 11:42:04

I am using MS SQL Server 2005 to do a query against the db.

I am trying to do a BOM explosion with a recursive SQL statement, but the results come back with the first column as "Unsupported Data Type".

Data example:
Table: PARTMTL
(PARENT) (CHILD)
PARTNUM REVLvl MTLSEQ MTLPARTNUM QTYPER
---------- ------ ------ -------------- ------
NC006000 B 60 NC00M021 1.00000000
NC006000 B 70 NC00M022 1.00000000
NC006000 B 80 NC006IFU 1.00000000
NC006000 B 90 NC006001 1.00000000
NC006000 B 100 NC00M031-09 1.00000000
NC006000 B 120 NC006305 1.00000000
NC006000 B 130 NC006306 1.00000000
NC006000 B 140 NC006303 1.00000000
NC006000 B 150 NC006304 1.00000000
NC006000 C 60 NC00M021 1.00000000
NC006000 C 70 NC00M022 1.00000000
NC006000 C 80 NC006IFU 1.00000000
NC006000 C 90 NC006001 1.00000000
NC006000 C 100 NC00M031-09 1.00000000
NC006000 C 120 NC006305 1.00000000
NC006000 C 130 NC006306 1.00000000
NC006000 C 140 NC006303 1.00000000
NC006000 C 150 NC006304 1.00000000
NC006303 A 10 NC006301 1.00000000
NC006303 A 20 NC006305 1.00000000
NC006303 A 30 NC00M015-05 1.00000000
NC006303 A 40 NC00M015-03 1.00000000
NC006303 A 50 NC00M016-05 2.00000000
NC006303 A 60 NC006139 1.00000000
NC006303 A 70 NC00M021 1.00000000
NC006303 A 80 NC00M022 1.00000000
NC006303 B 10 NC006301 1.00000000
NC006303 B 20 NC006305 1.00000000
NC006303 B 30 NC00M015-05 1.00000000
NC006303 B 40 NC00M015-03 1.00000000
NC006303 B 50 NC00M016-05 2.00000000
NC006303 B 60 NC006139 1.00000000
NC006303 B 70 NC00M021 1.00000000
NC006303 B 80 NC00M022 1.00000000
NC006140 H 10 NC006141 1.00000000
NC006140 H 20 NC006143 1.00000000
NC006140 H 30 NC006144 1.00000000
NC006140 J 10 NC006141 1.00000000
NC006140 J 70 NC006147 1.00000000
NC006140 J 80 NC006148 1.00000000
NC006140 J 90 NC006105-1 1.00000000
NC006140 J 100 NC006105-2 1.00000000
NC006140 K 10 NC006141 1.00000000
NC006140 K 70 NC006147 1.00000000
NC006140 K 80 NC006148 1.00000000
NC006140 K 90 NC006105-1 1.00000000
NC006140 K 100 NC006105-2 1.00000000

SQL Statement:

WITH RPL(PARTNUM, MTLPARTNUM, QTYPER) AS (SELECT PARTMTL.PARTNUM, PARTMTL.MTLPARTNUM, PARTMTL.QTYPER
FROM dbo.partmtl
WHERE (PARTMTL.PARTNUM = 'NC006000')
UNION ALL
SELECT CHILD.partnum, CHILD.mtlpartnum, CHILD.qtyper
FROM RPL AS PARENT CROSS JOIN
dbo.partmtl AS CHILD
WHERE (PARENT.MTLPARTNUM = CHILD.partnum))
SELECT DISTINCT PARTNUM, MTLPARTNUM, QTYPER
FROM RPL AS RPL_1
ORDER BY PARTNUM, MTLPARTNUM, QTYPER

Results:
PARTNUM MTLPARTNUM QTYPER
----------------------- --------------- ----------
<Unsupported Data Type> 5212563-1 2.00000000
<Unsupported Data Type> 5212563-3 1.00000000
<Unsupported Data Type> NC004072-058 1.00000000
<Unsupported Data Type> NC004074-125 1.00000000
<Unsupported Data Type> NC004074-160 1.00000000
<Unsupported Data Type> NC004074-175 1.00000000
ETC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 11:50:48

whats the datatype of PARTNUM?

MTaft
Starting Member

4 Posts

Posted - 2009-07-08 : 11:52:25

varchar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 11:54:16

where you exporting this data from some other rdbms?

MTaft
Starting Member

4 Posts

Posted - 2009-07-08 : 11:55:53

No... that is how it's set up in the database I'm hooked up to. I'm not trying to move or modify the data in any way, just query it. Am I misunderstanding your question?

MTaft
Starting Member

4 Posts

Posted - 2009-07-08 : 12:22:45

Somehow I fixed this myself. I changed the statement to read:

WITH RPL AS (SELECT PARTMTL.PARTNUM, PARTMTL.MTLPARTNUM, PARTMTL.QTYPER
FROM dbo.partmtl
WHERE (PARTMTL.PARTNUM = 'NC006000')
UNION ALL
SELECT CHILD.partnum, CHILD.mtlpartnum, CHILD.qtyper
FROM RPL AS PARENT CROSS JOIN
dbo.partmtl AS CHILD
WHERE (PARENT.MTLPARTNUM = CHILD.partnum))
SELECT DISTINCT PARTNUM, MTLPARTNUM, QTYPER
FROM RPL AS RPL_1
ORDER BY PARTNUM, MTLPARTNUM, QTYPER

Thank you!!

jltImpact
Starting Member

1 Post

Posted - 2011-08-29 : 07:05:22

You can cast the fields in your sub query. For example instead of

SELECT CHILD.partnum, CHILD.mtlpartnum, CHILD.qtyper

use

SELECT Cast(CHILD.partnum as varchar(20)) as partnum, CAST(CHILD.mtlpartnum as varchar(20)) as mtlpartnum, CAST(CHILD.qtyper as int) as qtyper

jlt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources