Solved

Or operator does not bring back data as expected in SQL Server 2000.

Posted on 2007-03-28
18
187 Views
Last Modified: 2008-02-01
The query I have is supposed to return data greater than 30 characters in all fields queried against.
The first field displays the data greater than 30 characters, but the other fields display data greater than 30 or less than 30.  The query does not work.
Here is the query:

select regline1,regline2,regline3,regline4,regline5,regline6
from tblAmp
where (Len(regline1)> 30 or (Len(regline2)> 30) and (Len(regline3)> 30)) or (Len(regline4)> 30) or (Len(regline5)>30) or (Len(regline6)>30)))


Please help.
0
Comment
Question by:donnie919101
  • 7
  • 5
  • 2
  • +3
18 Comments
 
LVL 35

Expert Comment

by:Raynard7
Comment Utility
hi,

your brackets to not look like they are correctly organised.

the middle and statement does not look consistent, you may want to check what exactly you want to compare, I'm not sure exactly what you want from the description given.
0
 

Author Comment

by:donnie919101
Comment Utility
For regline1 I want to display the string data in the field greater than 30 characters.
Then I want to do the same for the rest of the other fields.
If the data in regline1 is greater than 30 characters then I need to display the data in the field regline1.
If the data in regline2 is greater than 30 characters then I need to display the data in the field regline2.
If the data in regline3 is greater than 30 characters then I need to display the data in the field regline3.
If the data in regline4 is greater than 30 characters then I need to display the data in the field regline4.
If the data in regline5 is greater than 30 characters then I need to display the data in the field regline5.
If the data in regline6 is greater than 30 characters then I need to display the data in the field regline6.

Each column (field) should have it's own set of data.



where (Len(regline1)> 30 or (Len(regline2)> 30) and (Len(regline3)> 30)) or (Len(regline4)> 30) or (Len(regline5)>30) or (Len(regline6)>30)))
0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
I count 12 opening brackets and 14 closing brackets in that WHERE clause so I don't know how it could actually be running.

If the data in regline1 is greater than 30 characters in length but the rest aren't, do you just want to see a single record with regline1 populated but with NULLS in the other columns?

If every regline is < 30 characters, do you want to see no record or do you want to see an empty record?

The where clause effects whether a record is returned. It resolves to True or False - the record is shown or it isn't. That record will have all fields in it.
0
 
LVL 6

Assisted Solution

by:psv1973
psv1973 earned 25 total points
Comment Utility
You description of what you want to show is different form what you're query says it should do Try formatting you're query like this and then you should be able to figure it out.

select regline1,regline2,regline3,regline4,regline5,regline6
from tblAmp
where  ( Len(regline1)> 30)  or (Len(regline2)> 30) )
             and (        (Len(regline3)> 30)
                         or (Len(regline4)> 30)
                         or (Len(regline5)>30)
                         or (Len(regline6)>30)
                     )
             )

If I look at your description, then I can not fgure out what you really wan(data greater than 30 or less than 30, what do you mean?):

The query I have is supposed to return data greater than 30 characters in all fields queried against.
The first field displays the data greater than 30 characters, but the other fields display data greater than 30 or less than 30.  The query does not work.
Here is the query:

0
 

Author Comment

by:donnie919101
Comment Utility
     Regline1                         Regline2                           Regline3                         Regline4                   
characters >30            characters >30                      characters >30            characters >30
If not then blank          If not then blank                       If not then blank        If not then blank
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 75 total points
Comment Utility
Then you dont actually want a where clause. You want a case statement.


select
CASE WHEN LEN(regline1) > 30 THEN regline1 ELSE '' END As regline1
CASE WHEN LEN(regline2 > 30 THEN regline2 ELSE '' END As regline2
CASE WHEN LEN(regline3) > 30 THEN regline3 ELSE '' END As regline3
...

..


from tblAmp
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 25 total points
Comment Utility
I think mmcdermaid has the correct solution.
I would add the where clause to remove rows where all the 6 fields are shorter than 30:


select
CASE WHEN LEN(regline1) > 30 THEN regline1 ELSE '' END As regline1
CASE WHEN LEN(regline2 > 30 THEN regline2 ELSE '' END As regline2
CASE WHEN LEN(regline3) > 30 THEN regline3 ELSE '' END As regline3
CASE WHEN LEN(regline4) > 30 THEN regline4 ELSE '' END As regline4
CASE WHEN LEN(regline5) > 30 THEN regline5 ELSE '' END As regline5
CASE WHEN LEN(regline6) > 30 THEN regline6 ELSE '' END As regline6
from tblAmp
where (
     Len(regline1) > 30
or  Len(regline2)> 30
or  Len(regline3)> 30
or  Len(regline4)> 30
or  Len(regline5)> 30
or  Len(regline6)> 30
)
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<I think mmcdermaid has the correct solution.
I would add the where clause to remove rows where all the 6 fields are shorter than 30:>>
I believe an important information is missing to correctly answer the question: What should be returned in case the length of the columns are below 30...

Just my two cents...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Racimo: if you look at the last comment from the author above, you should find that information...
just my 2c
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:donnie919101
Comment Utility
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.

select regline1,regline2,regline3,regline4,regline5,regline6
CASE WHEN LEN(regline1) > 30 THEN regline1 ELSE '' END As regline1
CASE WHEN LEN(regline2 > 30 THEN regline2 ELSE '' END As regline2
CASE WHEN LEN(regline3) > 30 THEN regline3 ELSE '' END As regline3
CASE WHEN LEN(regline4) > 30 THEN regline4 ELSE '' END As regline4
CASE WHEN LEN(regline5) > 30 THEN regline5 ELSE '' END As regline5
CASE WHEN LEN(regline6) > 30 THEN regline6 ELSE '' END As regline6
from tblAmp
where (
     Len(regline1) > 30
or  Len(regline2)> 30
or  Len(regline3)> 30
or  Len(regline4)> 30
or  Len(regline5)> 30
or  Len(regline6)> 30
)

0
 

Author Comment

by:donnie919101
Comment Utility
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.

select regline1,regline2,regline3,regline4,regline5,regline6
CASE WHEN LEN(regline1) > 30 THEN regline1 ELSE '' END As regline1
CASE WHEN LEN(regline2 > 30 THEN regline2 ELSE '' END As regline2
CASE WHEN LEN(regline3) > 30 THEN regline3 ELSE '' END As regline3
CASE WHEN LEN(regline4) > 30 THEN regline4 ELSE '' END As regline4
CASE WHEN LEN(regline5) > 30 THEN regline5 ELSE '' END As regline5
CASE WHEN LEN(regline6) > 30 THEN regline6 ELSE '' END As regline6
from tblAmp
where (
     Len(regline1) > 30
or  Len(regline2)> 30
or  Len(regline3)> 30
or  Len(regline4)> 30
or  Len(regline5)> 30
or  Len(regline6)> 30
)

Help please.
0
 

Author Comment

by:donnie919101
Comment Utility
select
CASE
WHEN LEN(regline1) > 30
      THEN regline1
   ELSE ''
END As regline1,
CASE
WHEN LEN(regline2) > 30
      THEN regline2
   ELSE ''
END As regline2,
CASE
WHEN LEN(regline3) > 30
      THEN regline3
   ELSE ''
END As regline3,
CASE
WHEN LEN(regline4) > 30
      THEN regline4
   ELSE ''
END As regline4,
CASE
WHEN LEN(regline5) > 30
      THEN regline5
   ELSE ''
END As regline5,
CASE
WHEN LEN(regline6) > 30
      THEN regline6
   ELSE ''
END As regline6
from tblAmp
where (
     Len(regline1) > 30
or  Len(regline2)> 30
or  Len(regline3)> 30
or  Len(regline4)> 30
or  Len(regline5)> 30
or  Len(regline6)> 30)

This works.  The only problem is that I there are blanks where the record is not greater than 30 characters.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
angelIII
<<Racimo: if you look at the last comment from the author above, you should find that information...
just my 2c>>
Apologies I have overlooked that part...;)

<< The only problem is that I there are blanks where the record is not greater than 30 characters.>>
Was'nt that what you demanded?


0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
> The only problem is that I there are blanks where the record is not greater than 30 characters.
what do you want instead ? null?
then replace '' by NULL in the code.
0
 

Author Comment

by:donnie919101
Comment Utility
I would like all the columns to shift up where there is a blank.
 Is this possible?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>I would like all the columns to shift up where there is a blank.
are you aware that that would mean a variable number of columns returned (which is not possible by database means).
you should do that in the front-end code.
in sql, this would be much too complicated code, although possible (assuming that you put nulls in the trailing columns instead)
0
 

Author Comment

by:donnie919101
Comment Utility
I was't sure about lthe variable number part.  Thanks for clearing that up for me.
Thanks for all your help.

So who gets the points.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>So who gets the points.
those that helped to solve your problem.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now