Error in Query

Question

Post reply

Error in Query

Sandeep Mahajan-393729

SSC Eights!

Points: 858
More actions
April 10, 2007 at 12:30 am

#91092

SELECT tblContact.ContactID, tblContact.Title + tblContact.FName + tblContact.MName + tblContact.LName AS Name,
  tblDesignation.DesignationName, tblContact.DesignationID, tblCompany.CompanyName
  FROM tblContact, tblDesignation, tblOperator, tblCompany, tblCountry   Where tblDesignation.DesignationID = 3 AND tblCountry.CountryID = 3 AND tblOperator.OperatorID= 6 AND   (tblContact.FName like '%san%' OR tblContact.MName like '%san%' OR tblContact.LName like '%san%' OR tblCompany.CompanyName like '%san%') AND [DateEntered] BETWEEN (( YEAR([DateEntered]) = 2006 AND MONTH([DateEntered]) =12 AND DAY([DateEntered]) = 12)
     AND ( YEAR([DateEntered]) = 2007 AND MONTH([DateEntered]) = 12 AND DAY([DateEntered]) = 12)) AND (1=1)
  AND tblContact.DesignationID = tblDesignation.DesignationID
  AND tblContact.OperatorID = tblOperator.OperatorID
  AND tblContact.CompanyID = tblCompany.CompanyID

What is the error in this Query? plz
it's Urgent.....

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

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

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 1

Hi,

[DateEntered] BETWEEN (( YEAR([DateEntered]) = 2006 AND MONTH([DateEntered]) =12 AND DAY([DateEntered]) = 12)

AND ( YEAR([DateEntered]) = 2007 AND MONTH([DateEntered]) = 12 AND DAY([DateEntered]) = 12))

should say

[DateEntered] BETWEEN '20061212' AND '20071212'

K. Matsumura

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

...and I guess the words "formatted code" just aren't in your dictionary, huh?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

shashi kant SSCommitted Points: 1659 More actions · Answer 3

AND [dateentered] BETWEEN ((YEAR([dateentered]) = 2006

AND MONTH([dateentered]) = 12

AND DAY([dateentered]) = 12)

AND (YEAR([dateentered]) = 2007

AND MONTH([dateentered]) = 12

AND DAY([dateentered]) = 12))

AND (1 = 1)

I think the between clause is wrong

coze

Syntax is

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

regards shashi kant

SQLBill SSC Guru Points: 51440 More actions · Answer 4

I don't know....what is the error? If it didn't work you should have gotten an error message - what was it? If it didn't return what you expected - provide us the schema, sample data, what was returned, and what you expected to get returned.

-SQLBill