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

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.
donnie919101Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raynard7Commented:
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
donnie919101Author Commented:
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
nmcdermaidCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

psv1973Commented:
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
donnie919101Author Commented:
     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
nmcdermaidCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Racimo: if you look at the last comment from the author above, you should find that information...
just my 2c
0
donnie919101Author Commented:
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
donnie919101Author Commented:
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
donnie919101Author Commented:
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
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> 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
donnie919101Author Commented:
I would like all the columns to shift up where there is a blank.
 Is this possible?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
donnie919101Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>So who gets the points.
those that helped to solve your problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.