Post reply

Removing like results based on a character

  • October 21, 2003 at 8:39 am

    #83337

    Hello,

    Thanks for the help in advance.

    I am trying to elimanate any jobs which have the same first 5 characters if there is one with a P

    This is the error I get:

    Server: Msg 512, Level 16, State 1, Line 2

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , >= or when the subquery is used as an expression.

    I am trying to find out what jobs have been quoted but not produced.

    Here is an Example of some quoted Jobs and the production job, none of which I want to see because it was produced. 13702, 13702V1, 13702V1P

    Hope this makes sense..

    Here is a copy of the query I am trying to run.

    SELECT DISTINCT A.JobNumber

    FROM dbo.Job A INNER JOIN

    dbo.Job B ON LEFT(A.JobNumber, 5) <>

    (SELECT LEFT(B.JobNumber, 5)

    FROM Job B

    WHERE RIGHT(B.JobNumber, 1) = 'P')

    Leo Snetsinger

  • October 21, 2003 at 8:46 am

    #478960

    Change your "<>" to "NOT IN".

    --Jonathan



    --Jonathan

  • October 21, 2003 at 11:38 am

    #478986

    Jonathan,

    Thanks Very Much.

    That worked Perfectly....

    How would I get the opposite. I would like to find the main Job to a Job That has a P on the end.

    I am basically looking to remove the end character and do a join with a table and a view.

    The View shows only jobs which have a '%P'

    Here is the statement I have started but can figure out how to truncate the right character which is a P and then join the tables.

    SELECT dbo.Job.JobNumber

    FROM dbo.Job INNER JOIN

    dbo.v_TW_PJobs ON dbo.Job.JobNumber = Right(dbo.v_TW_PJobs.JobNumber, 1)

    Thanks,

    Appreciate your help.

    Leo Snetsinger

  • October 21, 2003 at 1:29 pm

    #478997

    quote:

    Jonathan,

    Thanks Very Much.

    You are welcome.

    quote:

    How would I get the opposite. I would like to find the main Job to a Job That has a P on the end.

    I am basically looking to remove the end character and do a join with a table and a view.

    The View shows only jobs which have a '%P'

    Here is the statement I have started but can figure out how to truncate the right character which is a P and then join the tables.

    SELECT dbo.Job.JobNumber

    FROM dbo.Job INNER JOIN

    dbo.v_TW_PJobs ON dbo.Job.JobNumber = Right(dbo.v_TW_PJobs.JobNumber, 1)

    I don't think I understand. If you're just looking to join on the first five characters, that's similar to what you did in the first query: just join on LEFT(JobNumber,5). If instead you wish to join on (from your example) '13702V1', i.e. all characters preceding the last, then you could try something like LEFT(JobNumber,LEN(JobNumber)-1) or maybe REPLACE(JobNumber,'P','').

    --Jonathan



    --Jonathan

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply