Filter on import - Help - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Filter on import - Help

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2003-08-07 : 16:57:31

I am importing data on a nightly basis from the AS/400. This Select query works fine but I now want to exclude certain things that I don't want imported and can't figure out how to filter them out.

The column I would like the filter applied to is the 'ITNBR'. I only want item numbers imported, not any thing else. example of some data:

ITNBR | ITDSC
069383 | TEST0
0123456 | Test1
0234567 | Test2
PIPE | TEST3
001-38 | TEST4

I would like the result to look like:

ITNBR | ITDSC
069383 | TEST0
0123456 | Test1

So how do I skip/filter out anything that contains "-" or chars. The properties for this column is setup as a 'char(15)' for all the tables.

Thanks,

JLM

---------------------------------------------

SELECT TOP 100 PERCENT I.ITNBR, I.ITDSC, II.PITD1, II.PITD2, I.ENGNO, I.PRICE, I.UCDEF, I.UNMSR, I.ITTYP, I.ITCLS, I.VALUC, I.PACKC, AML.MFGNMA, AML.MFGA, AML.CMTA, AML.ULNUMA, AML.CSANMA, AML.MFGNMB, AML.MFGB, AML.CMTB, AML.ULNUMB, AML.CSANMB, AML.MFGNMC, AML.MFGC, AML.CMTC, AML.MFGNMD, AML.MFGD, AML.CMTD, AML.MFGNME, AML.MFGE, AML.CMTE, AML.MFGNMF, AML.MFGF, AML.CMTF, AML.MFGNMG, AML.MFGG, AML.CMTG, AML.MFGNMH, AML.MFGH, AML.CMTH, AML.MFGNMI, AML.MFGI, AML.CMTI, AML.MFGNMJ, AML.MFGJ, AML.CMTJ, AML.PKG, AML.LEADSP, AML.ORNTN, AML.PREFER, AML.COMENT,

(SELECT MOHTQ
FROM AMFLIBA.ITEMBL b WHERE b.ITNBR = a.ITNBR AND b.HOUSE = '2') AS WHS2,
(SELECT MOHTQ
FROM AMFLIBA.ITEMBL b WHERE b.ITNBR = a.ITNBR AND b.HOUSE = '3') AS WHS3,
(SELECT MOHTQ
FROM AMFLIBA.ITEMBL b WHERE b.ITNBR = a.ITNBR AND b.HOUSE = 'M') AS WHSM,
(SELECT MOHTQ
FROM AMFLIBA.ITEMBL b WHERE b.ITNBR = a.ITNBR AND b.HOUSE = 'R') AS WHSR,
(SELECT MOHTQ
FROM AMFLIBA.ITEMBL b WHERE b.ITNBR = a.ITNBR AND b.HOUSE = 'T') AS WHST

FROM AMFLIBA.ITEMASA I LEFT OUTER JOIN AMFLIBA.ITEMASA a ON I.ITNBR = a.ITNBR LEFT OUTER JOIN DLEDATA.AMLDATA AML ON I.ITNBR = AML.ITEM LEFT OUTER JOIN AMFLIBA.ITEMASC II ON I.ITNBR = II.ITNBR

ORDER BY I.ITNBR

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-07 : 17:02:14

IsNumeric will work.

SELECT IsNumeric(ITNBR), ITDSC
FROM Table1

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-07 : 17:03:35

to get just numbers
where patindex('%[^0-9]%', ITNBR) = 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-07 : 17:04:25

Ugh, I'm so stupid (today at least). IsNumeric will work but it would require more code.

Tara

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2003-08-07 : 18:36:00

SELECT TOP 100 PERCENT I.ITNBR, I.ITDSC, II.PITD1, II.PITD2, I.ENGNO, I.PRICE, I.UCDEF, I.UNMSR, I.ITTYP, I.ITCLS, I.VALUC, I.PACKC, AML.MFGNMA, AML.MFGA, AML.CMTA, AML.ULNUMA, AML.CSANMA, AML.MFGNMB, AML.MFGB, AML.CMTB, AML.ULNUMB, AML.CSANMB, AML.MFGNMC, AML.MFGC, AML.CMTC, AML.MFGNMD, AML.MFGD, AML.CMTD, AML.MFGNME, AML.MFGE, AML.CMTE, AML.MFGNMF, AML.MFGF, AML.CMTF, AML.MFGNMG, AML.MFGG, AML.CMTG, AML.MFGNMH, AML.MFGH, AML.CMTH, AML.MFGNMI, AML.MFGI, AML.CMTI, AML.MFGNMJ, AML.MFGJ, AML.CMTJ, AML.PKG, AML.LEADSP, AML.ORNTN, AML.PREFER, AML.COMENT,

(SELECT MOHTQ
FROM AMFLIBA.ITEMBL b WHERE b.ITNBR = a.ITNBR AND b.HOUSE = '2') AS WHS2,
(SELECT MOHTQ
FROM AMFLIBA.ITEMBL b WHERE b.ITNBR = a.ITNBR AND b.HOUSE = '3') AS WHS3,
(SELECT MOHTQ
FROM AMFLIBA.ITEMBL b WHERE b.ITNBR = a.ITNBR AND b.HOUSE = 'M') AS WHSM,
(SELECT MOHTQ
FROM AMFLIBA.ITEMBL b WHERE b.ITNBR = a.ITNBR AND b.HOUSE = 'R') AS WHSR,
(SELECT MOHTQ
FROM AMFLIBA.ITEMBL b WHERE b.ITNBR = a.ITNBR AND b.HOUSE = 'T') AS WHST

FROM AMFLIBA.ITEMASA I LEFT OUTER JOIN AMFLIBA.ITEMASA a ON I.ITNBR = a.ITNBR LEFT OUTER JOIN DLEDATA.AMLDATA AML ON I.ITNBR = AML.ITEM LEFT OUTER JOIN AMFLIBA.ITEMASC II ON I.ITNBR = II.ITNBR

WHERE patindex('%[^0-9]%', I.ITNBR) = 0

ORDER BY I.ITNBR

***********************

I tried this and got this error:

Error Source: Microsoft OLE DB Provider for ODBC Drivers
Error Description: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - PATINDEX in *LIBL type *N not found.
Context: Error calling Prepare on the command text.

Any other suggestions? I've downloaded the latest iSeries ODBC drivers already and still didn't work.

JLM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-07 : 18:39:29

Well you could see if IsNumeric is supported by the provider. Just do a simple query to check. It'll return a 0 or a 1 (0 if not numeric, 1 if numeric).

SELECT IsNumeric(ITNBR)
FROM MOHTQ

You could use the REPLACE function to remove the dashes:

SELECT REPLACE(ITNBR, '-', '')
FROM MOHTQ

Tara

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2003-08-07 : 18:57:35

Thanks for the suggestion Tara but it still didn't work.

I get the same type of error.

So were on the same page, what I need done is to filter out the whole item number if the item contains a '-' in it's number or if it's a char. I don't want to import all the item numbers that have '-' in them and then have the query go through and remove the '-'. These items don't mean anything to me and I don't need them imported.

JLM

I'd hate to import everything to some temp table then have another query take that data and filter it out to another table from within SQL so that these commands will work.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-07 : 18:59:50

It doesn't seem that your provider is going to allow this, so you are going to have to import into a temp table like you mentioned. If you really don't like this, then you will need to go through the provider's documentation as this is AS400 related and not SQL Server related. The AS400 driver is the one preventing these functions.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources