Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

code change to allow P135578 but reject all other p%

Posted on 2009-02-23
5
Medium Priority
?
207 Views
Last Modified: 2012-05-06
hi i want to be able to allow specified P1375004 numbers however reject all others from the search

im sure its simple enough but not sure how.

Thanks
SELECT T0.DocNum, T0.DocType,T1.ItemCode,  T0.CardCode, T0.DocDate, T0.NumAtCard, T0.U_IIS_INADR, T0.U_IIS_JTYPE FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry WHERE t0.docdate  >=[%0] and T0.NumAtCard NOT Like 'P%%_%%'

Open in new window

0
Comment
Question by:KeirMcCann
  • 3
5 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 23710498
Hi KeirMcCann,

Like 'P137*' without using "not like'

Good Luck!

Gary
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 23710603
Try the code in snippet.  If you need to exclude more than 1 number, use IN('Pxxx', 'Pyyy')

SELECT T0.DocNum, T0.DocType,T1.ItemCode,  T0.CardCode, T0.DocDate, T0.NumAtCard, T0.U_IIS_INADR, T0.U_IIS_JTYPE 
FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry 
WHERE t0.docdate  >=[%0] and (T0.NumAtCard NOT Like 'P%%_%%' OR T0.NumAtCard = 'P135578')

Open in new window

0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 23710625
forgot to mention: there's no need to duplicate the % wildcard, it stands for "zero or more characters"
0
 

Author Comment

by:KeirMcCann
ID: 23710668
yea its for sap business one it adds that itself...

this is what i have now am i right in thinking this will include all jobs that start with P1375853 but exclude all others that start with P%?
SELECT T0.DocNum, T0.DocType,T1.ItemCode,  T0.CardCode, T0.DocDate, t1.u_iis_ddate, T0.NumAtCard, T0.U_IIS_INADR, T0.U_IIS_JTYPE, T3.SlpName FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode WHERE t1.U_iis_ddate >=[%0] and T0.NumAtCard NOT Like 'P%%_%%'
and t2.itmsgrpcod = '106' and (T0.NumAtCard NOT Like 'P%%_%%' OR T0.NumAtCard = 'P1375853%')

Open in new window

0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 23711322
You need to remove the first LIKE, it is replaced by the one in brackets.  Also, use LIKE instead of = to check if the number starts with a certain string, see modifications in snippet

SELECT T0.DocNum, T0.DocType,T1.ItemCode,  T0.CardCode, T0.DocDate, t1.u_iis_ddate, T0.NumAtCard, T0.U_IIS_INADR, T0.U_IIS_JTYPE, T3.SlpName 
FROM ODLN T0  INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode 
WHERE t1.U_iis_ddate >=[%0] and t2.itmsgrpcod = '106' 
and (T0.NumAtCard NOT Like 'P%%_%%' OR T0.NumAtCard LIKE 'P1375853%')

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question