asked on

SELECT statement not returning data

Ok, it has been a while since I worked on an actual server database, I have been mostly wokring in ACCESS. So, I have the below query that simply refuses to work. There is data in the table, but I do not know what is wrong with my select statement. I want all the loans except those that have 10 digits in the number and no text characters. what is going on is the nubmer i want are in the form like 11111.10 or 11111-10, but NOT accounts 000000002 or 11111-10C.

SELECT balview.LOAN, balview.PRIN_BAL_P, balview.PRIN_BAL_P
	FROM balview
	WHERE balview.LOAN Like '?????.*' Or balview.LOAN Like '?????-*' 
		   And balview.LOAN Not Like '*[A-Z]*') 
		AND balview.ACCOUNTING_DATE = '2007-11-30'

Open in new window

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

let's see:
* you placed a ) but I don't see the ( for that one
* you have a OR, but to get this working properly, you need a () around the 2 OR parts.
* I replaced the NOT LIKE by a LIKE with a changed pattern

SELECT balview.LOAN, balview.PRIN_BAL_P, balview.PRIN_BAL_P
      FROM balview
      WHERE ( balview.LOAN Like '?????.*' Or balview.LOAN Like '?????-*'  )
               And balview.LOAN Like '*[^A-Z]*'
            AND balview.ACCOUNTING_DATE = '2007-11-30'

Open in new window

Guy Hengel [angelIII / a3]

and yes, with sql server it is _ and not ?

SELECT balview.LOAN, balview.PRIN_BAL_P, balview.PRIN_BAL_P
      FROM balview
      WHERE ( balview.LOAN Like '?????.*' Or balview.LOAN Like '?????-*'  )
               And balview.LOAN Like '*[^A-Z]*'
            AND balview.ACCOUNTING_DATE = '2007-11-30'

Open in new window

Sandra Smith

ASKER

Actually, in the code I did correct that, but still did not work. aneeshattingal, will try your version.

Sandra Smith

ASKER

Aneeshattingal, thankyou, got it to work, but I don't understand the difference between the ? character and the _? Are not both placeholders for one character?

Aneesh

yes, _ stands for a single character in sql

Sandra Smith

ASKER

Thank you both. Switching is going to be a struggle for the next few weeks, so expect more questions.