INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Select Query - Restrict records if all columns are NULL

Select Query - Restrict records if all columns are NULL

(OP)
Using the sql script below, I have over 50,000 records in the result set of which over 30,000 records only display "NULL" in seven columns; GT#_Identifier, Inspector_Name, Ancillary, Geography, Sequence, OverConsult, List.

My objective: Filter out all records that have a "NULL" in the above-mentioned seven columns.

What revisions to the sql script will allow the filtering out of all records that have a "NULL" in the seven fields?

Not yet able to resolve this and appreciate any insight.

I encounter the following errors;

Errors using Option 1:

CODE

Incorrect syntax near the keyword 'is' and the red underline underneath "m.mcase" with the message 'the multi-part identifier "m.mcase" could not be bound" 

Error using Option 2:

CODE

Option 2: Incorrect syntax near the key word 'is'. 

[Code]
Use test_db

SELECT DISTINCT
m.mcustomer,
clname1,
m.mcase,
m.mdesc1,
(select a.value from global a where a.globalindex = '101' and a.Gljoin = m.mcase and a.udtype = 'GT') Invoice_no,
(select a.value from global a where a.globalindex = '25' and a.Gljoin = m.mcase and a.udtype = 'GT') GT#_Identifier,
(select a.value from global a where a.globalindex = '42' and a.Gljoin = m.mcase and a.udtype = 'GT') Inspector_Name,
(select a.value from global a where a.globalindex = '125' and a.Gljoin = m.mcase and a.udtype = 'GT') Ancillary,
(select a.value from global a where a.globalindex = '130' and a.Gljoin = m.mcase and a.udtype = 'GT') Geography,
(select a.value from global a where a.globalindex = '160' and a.Gljoin = m.mcase and a.udtype = 'GT') Sequence,
(select a.value from global a where a.globalindex = '200' and a.Gljoin = m.mcase and a.udtype = 'GT') OverConsult,
(select a.value from global a where a.globalindex = '250' and a.Gljoin = m.mcase and a.udtype = 'GT') List,
(select a.value from global a where a.globalindex = '185' and a.Gljoin = m.mcase and a.udtype = 'GT') Append

FROM case m
inner join customer on m.mcustomer = clnum
inner join employee h on h.id = SupCon
inner join employee i on i.id = OrgCon
inner join employee b on b.id = BillCon
left outer join description mm on mm.mcase = m.mcase and mm.line=1
left outer join description mn on mn.mcase = m.mcase and mn.line=2
left outer join description mo on mo.mcase = m.mcase and mo.line=3
left outer join description mp on mp.mcase = m.mcase and mp.line=4


--Option 1
WHERE

mcustomer = '14258'
AND m.mclosedt is null
and (select a.value from global a where a.globalindex = '25' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --GT#_Identifier,
and (select a.value from global a where a.globalindex = '42' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --Inspector_Name,
and (select a.value from global a where a.globalindex = '125' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --Ancillary,
and (select a.value from global a where a.globalindex = '130' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --Geography,
and (select a.value from global a where a.globalindex = '160' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --Sequence,
and (select a.value from global a where a.globalindex = '200' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --OverConsult,
and (select a.value from global a where a.globalindex = '250' and a.Gljoin = m.mcase and a.udtype = 'GT') is not null, --List,


--Option 2
WHERE
mcustomer = '14258'
AND m.mclosedt is null
and (select a.value from global a where (a.globalindex in ('25', '42', '125', '130', '160', '200', '250')
and a.Gljoin = m.mcase and a.udtype = 'GT') is not null



RE: Select Query - Restrict records if all columns are NULL

option 1

CODE

select *
from (
SELECT DISTINCT 
m.mcustomer, 
clname1, 
m.mcase, 
m.mdesc1, 
(select a.value from global a where a.globalindex = '101' and a.Gljoin = m.mcase and a.udtype = 'GT') Invoice_no,
(select a.value from global a where a.globalindex = '25' and a.Gljoin = m.mcase and a.udtype = 'GT') GT#_Identifier,
(select a.value from global a where a.globalindex = '42' and a.Gljoin = m.mcase and a.udtype = 'GT') Inspector_Name,
(select a.value from global a where a.globalindex = '125' and a.Gljoin = m.mcase and a.udtype = 'GT') Ancillary,
(select a.value from global a where a.globalindex = '130' and a.Gljoin = m.mcase and a.udtype = 'GT') Geography,
(select a.value from global a where a.globalindex = '160' and a.Gljoin = m.mcase and a.udtype = 'GT') Sequence,
(select a.value from global a where a.globalindex = '200' and a.Gljoin = m.mcase and a.udtype = 'GT') OverConsult,
(select a.value from global a where a.globalindex = '250' and a.Gljoin = m.mcase and a.udtype = 'GT') List,
(select a.value from global a where a.globalindex = '185' and a.Gljoin = m.mcase and a.udtype = 'GT') Append

FROM case m
inner join customer on m.mcustomer = clnum 
inner join employee h on h.id = SupCon 
inner join employee i on i.id = OrgCon
inner join employee b on b.id = BillCon
left outer join description mm on mm.mcase = m.mcase and mm.line=1
left outer join description mn on mn.mcase = m.mcase and mn.line=2
left outer join description mo on mo.mcase = m.mcase and mo.line=3
left outer join description mp on mp.mcase = m.mcase and mp.line=4
WHERE mcustomer = '14258'
AND m.mclosedt is null
) t
where GT#_Identifier is not null, --GT#_Identifier,
and Inspector_Name is not null, --Inspector_Name,
and Ancillary is not null, --Ancillary,
and Geography is not null, --Geography,
and Sequence is not null, --Sequence,
and OverConsult is not null, --OverConsult,
and List is not null, --List, 

option 2 - just cleaning those horrible correlated queries

CODE

SELECT DISTINCT 
m.mcustomer, 
clname1, 
m.mcase, 
m.mdesc1, 
gl.Invoice_no,
gl.GT#_Identifier,
gl.Inspector_Name,
gl.Ancillary,
gl.Geography,
gl.Sequence,
gl.OverConsult,
gl.List,
gl.Append

FROM case m
inner join customer on m.mcustomer = clnum 
inner join employee h on h.id = SupCon 
inner join employee i on i.id = OrgCon
inner join employee b on b.id = BillCon
left outer join description mm on mm.mcase = m.mcase and mm.line=1
left outer join description mn on mn.mcase = m.mcase and mn.line=2
left outer join description mo on mo.mcase = m.mcase and mo.line=3
left outer join description mp on mp.mcase = m.mcase and mp.line=4
left outer join (select a.Gljoin
                      , max(case when a.globalindex = '101' then a.value else null end) as Invoice_no 
                      , max(case when a.globalindex = '25' then a.value else null end) as GT#_Identifier 
                      , max(case when a.globalindex = '42' then a.value else null end) as Inspector_Name 
                      , max(case when a.globalindex = '125' then a.value else null end) as Ancillary 
                      , max(case when a.globalindex = '130' then a.value else null end) as Geography 
                      , max(case when a.globalindex = '160' then a.value else null end) as Sequence 
                      , max(case when a.globalindex = '200' then a.value else null end) as OverConsult 
                      , max(case when a.globalindex = '250' then a.value else null end) as List 
                      , max(case when a.globalindex = '185' then a.value else null end) as Append 
                from global a
                where a.udtype = 'GT'
                  and a.globalindex in ('101', '25', '42', '125', '130', '160', '200', '250', '185')
                group by a.Gljoin
                ) gl 
on gl.Gljoin = m.mcase
WHERE mcustomer = '14258'
AND m.mclosedt is null
) t
where GT#_Identifier is not null, --GT#_Identifier,
and Inspector_Name is not null, --Inspector_Name,
and Ancillary is not null, --Ancillary,
and Geography is not null, --Geography,
and Sequence is not null, --Sequence,
and OverConsult is not null, --OverConsult,
and List is not null, --List, 


Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close