January 17, 2011 at 12:29 am
I have a tracking DB filled with various data (id#, description, cost, Vdoc#, Idoc# and Cdoc#)
All document numbers should start with V845BC, I15GK8 or C6TE01 and each has a total of 14 characters. I need to look at all 3 columns of data and find where these documents numbers don't start with V845BC, I15GK8 or C6TE01.
Vdoc# Idoc# Cdoc# Warehouse ID#
V845BC12345678 798 CT0001
I15GK812345678 798 CT0002
C6TE0112345678 798 CT0003
B845BC23145678 798 CT0004
798 CT0005
The only rows od data I want to bring in is for ID# CT0004 and CT0005. One has incorrect start of document number and other has no document numbers at all.
Thanks for all your help in advance.
January 17, 2011 at 12:58 am
if I am understanding your requirement properly then :-
select SUBSTRING(vdoc,1,1), * from #track where SUBSTRING(vdoc,1,1) not in ('v','i','c')
**SUBSTRING(vdoc,1,1), this value gives the first character and I am using this to compare if first character is in the list of your desired list.
you can further modify it.
----------
Ashish
January 17, 2011 at 1:21 am
Here is my query and (in my WHERE statement) I don't want to see any document numbers that start with these first 6 characters. I have 2 columns that I need to not see any documents that start with these. Only one of the columns should be filled in with a document number. Hope you will be able to read this.
-----------------------------------------------------------------------------------------------
SELECT EQLIST_VIEW.PLAN_ID, EQLIST_VIEW.EL_ID, EQLIST_VIEW.LIN, FSC||NIIN AS 'NSN', EQLIST_VIEW.ITEM_NAME, EQLIST_VIEW.SERIAL_NO AS 'SERIAL NO', EQLIST_VIEW.SYSTEM_NO AS 'SYSTEM NUM', EQLIST_VIEW.CONDITION_CD AS 'CC', EQLIST_VIEW.QUANTITY AS 'QTY', EQLIST_VIEW.LOCATION, EQLIST_VIEW.OWNERSHIP_PURPOSE_CD AS 'OWNERSHIP PURPOSE CD', EQLIST_VIEW.PROJECT_CD AS 'PROJECT CD', EQLIST_VIEW.ISSUE_RECEIPT_CD AS 'ISSUE RECEIPT CD', EQLIST_VIEW.OWNER_UIC AS 'OWNER UIC', EQLIST_VIEW.PBUSE_UIC_DocNum AS 'PBUSE DOCNUM', EQLIST_VIEW.DOCNUM, EQLIST_VIEW.DOCUMENT_NO AS 'DOCUMENT NO', EQLIST_VIEW.RECDOC, EQLIST_VIEW.LCMC_DOC_NO AS 'LCMC DOC NO', EQLIST_VIEW.MRODOC, EQLIST_VIEW.Gaining_Document_No AS 'GAINING DOCUMENT NO', EQLIST_VIEW.TURNIN, EQLIST_VIEW.REC_DT AS 'REC DATE', EQLIST_VIEW.REQ_DT AS 'REQ DATE', EQLIST_VIEW.RECDDT AS 'RECD DATE', EQLIST_VIEW.TMRNUM, EQLIST_VIEW.TMRREQ, EQLIST_VIEW.TMRRCD, EQLIST_VIEW.SHIPDT AS 'SHIP DATE', EQLIST_VIEW.SHIP_TO_DODAAC AS ' SHIP TO DODAAC', EQLIST_VIEW.SHIPTO, EQLIST_VIEW.Sign, EQLIST_VIEW.SITE, EQLIST_VIEW.BDE, EQLIST_VIEW.RFID, EQLIST_VIEW.remark
FROM a2k.EQLIST_VIEW EQLIST_VIEW
WHERE EQLIST_VIEW.PLAN_ID=('798') AND EQLIST_VIEW.LCMC_DOC_NO Not Like ('W919AD%') And EQLIST_VIEW.LCMC_DOC_NO Not Like ('W15GK8%') And EQLIST_VIEW.LCMC_DOC_NO Not Like ('W52H09%') And EQLIST_VIEW.LCMC_DOC_NO Not Like ('W56HZV%') And EQLIST_VIEW.LCMC_DOC_NO Not Like ('W31G3H%') And EQLIST_VIEW.LCMC_DOC_NO Not Like ('W58HZ1%') And EQLIST_VIEW.LCMC_DOC_NO Not Like ('W6TE01%')
And EQLIST_VIEW.MRODOC Not Like ('W919AD%') And EQLIST_VIEW.MRODOC Not Like ('W15GK8%') And EQLIST_VIEW.MRODOC Not Like ('W52H09%') And EQLIST_VIEW.MRODOC Not Like ('W56HZV%') And EQLIST_VIEW.MRODOC Not Like ('W31G3H%') And EQLIST_VIEW.MRODOC Not Like ('W58HZ1%') And EQLIST_VIEW.MRODOC Not Like ('W6TE01%')
ORDER BY EQLIST_VIEW.LCMC_DOC_NO DESC
-----------------------------------------------------------------------------------------------
Thanks ROS110
January 17, 2011 at 1:30 am
First, please read the first article I reference in my signature block below. Then please provide the DDL for the table(s) (CREATE TABLE statement(s)), sample data for each table (as a series of INSERT INTO statements) providing just enough data that is representative of the problem you are trying to solve, ant (not covered in the article) the expected results based on the sample data you provide.
Doing this, you will get more help quickly plus you will get tested code in return. Help us help you.
January 17, 2011 at 1:33 am
Also, as I was looking through your code, I have to ask; is this MS SQL Server code or Oracle code? The reason I ask is that the following fragment makes me suspect it is Oracle:
FSC||NIIN AS 'NSN',
January 17, 2011 at 1:43 am
Is it possible for both of these columns to contain data or to be empty.
If both contain data, is it possible for the data to be different.
If both are empty, is it null or an empty string. My experience with Oracle, an empty string is treated as a null.
Columns:
EQLIST_VIEW.LCMC_DOC_NO
EQLIST_VIEW.MRODOC
January 17, 2011 at 1:44 am
Thank you for all your help .... ROS110
January 17, 2011 at 1:51 am
MS SQL
Both columns can be Blank/Null or one or the other will be populated
I want to only see where both COLUMNS are Blank/Null or the document number in each column does not start with "W919AD", "W15GK8", "W52H09","W56HZV",W31G3H","W58HZ1" AND "W6TE01" .
started looking over your links
Thanks you all for the help
January 17, 2011 at 1:56 am
aj_jm964 (1/17/2011)
MS SQLBoth columns can be Blank/Null or one or the other will be populated
I want to only see where both COLUMNS are Blank/Null or the document number in each column does not start with "W919AD", "W15GK8", "W52H09","W56HZV",W31G3H","W58HZ1" AND "W6TE01" .
started looking over your links
Thanks you all for the help
If the columns do not contain a document number, will they have an empty string or a null? In SQL Server they are not the same. You can test for equality/non-equality with an empty string where you can't if the value is null.
January 17, 2011 at 2:11 am
Lynn
Attached is an Excel of a quick screen look at waht I'm looking to get. Items in yellow/red are what I need to get from the query. All others is what I want to have filtered out by query.
January 17, 2011 at 3:01 am
aj_jm964 (1/17/2011)
LynnAttached is an Excel of a quick screen look at waht I'm looking to get. Items in yellow/red are what I need to get from the query. All others is what I want to have filtered out by query.
Doesn't answer my question in my last post. Please answer the question regarding blank or null values.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply