Null value handling in Informix database

I have a sql statement that I am runing against an Informix table. It is a simple query and my question is this.

If there are value to return, then it returns the count of them. However, if there are no values, then it is blank. Is there a way that the count return a Zero if the result is nothing?

Here is my statement:

SELECT informix.sfu_inventory_rec.brand,   Count(informix.sfu_inventory_rec.invid) As Count_invid
FROM informix.sfu_inventory_rec
WHERE informix.sfu_inventory_rec.brand = 84
Group By   informix.sfu_inventory_rec.brand
kbsad1Asked:
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.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
are you getting any records returned?
0
kbsad1Author Commented:
Just a blank row with nothing in it. Now if there are items then yes I see records. It is only ones that are null.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if there are no records returned, then there is nothing to GROUP BY, which in turn yields no record output.

if you're using classic asp (as suggested by your tags), you can check in the code if any records are returned, and handle the outcome in the code:

sql = "SELECT informix.sfu_inventory_rec.brand,   Count(informix.sfu_inventory_rec.invid) As Count_invid FROM informix.sfu_inventory_rec WHERE informix.sfu_inventory_rec.brand = 84 Group By   informix.sfu_inventory_rec.brand"
set rs = conn.Execute( sql )

if not rs.BOF and not rs.EOF then    '-- records exist, proceed
.....
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.

kbsad1Author Commented:
Well this is part of a nested repeat region. I used all combinations ins ASP page including if IsNull. and nothing works. All I need is to return "0" if the records returns a Null.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you post your code?
0
kbsad1Author Commented:
<%
While ((Repeat_master1inv_brand_table__numRows <> 0) AND (NOT master1inv_brand_table.EOF))
%>
                                  <tr>
                                    <th><b><%=(master1inv_brand_table.Fields.Item("brand_title").Value)%></b> -<%=(master1inv_brand_table.Fields.Item("brand_id").Value)%></th>
                                </tr>
                                  <%    
  nst_sw = false
  detail2sfu_inventory_rec_cmd.Parameters("param1").Value = CInt("0" + master1inv_brand_table.Fields.Item("brand_id").Value)
  Set detail2sfu_inventory_rec = detail2sfu_inventory_rec_cmd.Execute
  While (NOT detail2sfu_inventory_rec.EOF)
    'Nested repeat
%>
                                  <%
' Show IF Conditional region1
  If Not IsNull(detail2sfu_inventory_rec.Fields.Item("count_invid").Value)  Then
%>
                                    <tr>
                                      <td>Items in the Inventory assigned to this brand( <strong><%=(detail2sfu_inventory_rec.Fields.Item("count_invid").Value)%></strong>)</td>
                                    </tr>
                                    <%
  End If
' End Conditional region1
%>
                                  <%
' Show IF Conditional region2
  If IsEmpty(detail2sfu_inventory_rec.Fields.Item("count_invid").Value) Then
%>
                                    <tr>
                                      <td>Items in the Inventory assigned to this brand( <strong></strong> <strong>0</strong> )</td>
                                    </tr>
                                    <%
  End If
' End Conditional region2
%>
                                  <%
  ' Nested move next
  detail2sfu_inventory_rec.MoveNext()
Wend
%>
                                  <%
  Repeat_master1inv_brand_table__index=Repeat_master1inv_brand_table__index+1
  Repeat_master1inv_brand_table__numRows=Repeat_master1inv_brand_table__numRows-1
  master1inv_brand_table.MoveNext()
Wend
%>
0
kbsad1Author Commented:
master1inv_brand_table is my master table
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
for each region, change

 If Not IsNull(detail2sfu_inventory_rec.Fields.Item("count_invid").Value)  Then

to

if not detail2sfu_inventory_rec.BOF and not detail2sfu_inventory_rec.EOF then
0
kbsad1Author Commented:
Still not working. The repeater shows the ones have items assigned to them and those nothing assigned do not show a 0.

Here is what I used:

  <%
' Show IF Conditional region1
  If  not detail2sfu_inventory_rec.BOF and not detail2sfu_inventory_rec.EOF   Then
%>
                                    <tr>
                                      <td>Items in the Inventory assigned to this brand( <strong><%=(detail2sfu_inventory_rec.Fields.Item("count_invid").Value)%></strong>)</td>
                                    </tr>
                                    <%
  End If
' End Conditional region1
%>
                                  <%
' Show IF Conditional region2
  If detail2sfu_inventory_rec.BOF or detail2sfu_inventory_rec.EOF Then
%>
                                    <tr>
                                      <td>Items in the Inventory assigned to this brand( <strong></strong> <strong>0</strong> )</td>
                                    </tr>
                                    <%
  End If
' End Conditional region2
%>
0
kbsad1Author Commented:
I remember long ago saw a code that actually said something like if the item value = Null or something like that and it worked. I can not find that article.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
try:

<%
' Show IF Conditional region1 
dim invCount : invCount = 0
  If  not detail2sfu_inventory_rec.BOF and not detail2sfu_inventory_rec.EOF   Then
       invCount  = detail2sfu_inventory_rec.Fields.Item("count_invid").Value
  end if
%>
                                    <tr>
                                      <td>Items in the Inventory assigned to this brand( <strong><%=invCount %></strong>)</td>
                                    </tr>
                                    <%
' End Conditional region1
%>
                                  <%
' Show IF Conditional region2 
invCount = 0
  If detail2sfu_inventory_rec.BOF or detail2sfu_inventory_rec.EOF Then
       invCount  = detail2sfu_inventory_rec.Fields.Item("count_invid").Value
  end if%>
                                    <tr>
                                      <td>Items in the Inventory assigned to this brand( <strong></strong> <strong><%=invCount%></strong> )</td>
                                    </tr>
                                    <%
' End Conditional region2
%>

Open in new window

0
kbsad1Author Commented:
Something is not right with the code. it gives error. I think the order of if statements or something is not right.
0
kbsad1Author Commented:
Basically the concept is if there is a count show it if there is no count display 0
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
ok, get rid of the 2nd IF statement then:

<%
' Show IF Conditional region1 
dim invCount : invCount = 0
  If  not detail2sfu_inventory_rec.BOF and not detail2sfu_inventory_rec.EOF   Then
       invCount  = detail2sfu_inventory_rec.Fields.Item("count_invid").Value
  end if
%>
                                    <tr>
                                      <td>Items in the Inventory assigned to this brand( <strong><%=invCount %></strong>)</td>
                                    </tr>
                                    <%
' End Conditional region1
%>

Open in new window


also, make sure you DIM invCount outside of your loop
0
kbsad1Author Commented:
The ones with count show up but ones without a record show just the title of the brand. Let me ask you this All I need is a nested repeat region with these two data sources.

Query 1:(The master loop):

Query2 the table that has a forgeign key from query one.

I am using dreamweaver cs3.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
i dont use dreameweaver so i cant help you there.

what do you want to display when there is no items?
0
kbsad1Author Commented:
Here is a screen shot of my results:
Capture.JPG
0
kbsad1Author Commented:
As you see on the screen canon, TEST2, TEST, and optoma have nothing to show. So I want them to show 0. As you can see, Blue has 7.

This is with your new code.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
please post the entire code block used in your loop
0
kbsad1Author Commented:
<table class="tftable">
                                <%
While ((Repeat_master1inv_brand_table__numRows <> 0) AND (NOT master1inv_brand_table.EOF))
%>
                                  <tr>
                                    <th><b><%=(master1inv_brand_table.Fields.Item("brand_title").Value)%></b></th>
                                  </tr>
                                <%    
  nst_sw = false
  detail2sfu_inventory_rec_cmd.Parameters("param1").Value = ( master1inv_brand_table.Fields.Item("brand_id").Value)
  Set detail2sfu_inventory_rec = detail2sfu_inventory_rec_cmd.Execute
  While (NOT detail2sfu_inventory_rec.EOF)
    'Nested repeat
%>


                                 
                                <%
' Show IF Conditional region1
invCount = 0
  If  not detail2sfu_inventory_rec.BOF and not detail2sfu_inventory_rec.EOF   Then
       invCount  = detail2sfu_inventory_rec.Fields.Item("count_invid").Value
  end if
%>
                                    <tr>
                                      <td>Items in the Inventory assigned to this brand( <strong><%=invCount %></strong>)</td>
                                    </tr>
                                    <%
' End Conditional region1
%>                          
                                 
                                 
                                    <%
  ' Nested move next
  detail2sfu_inventory_rec.MoveNext()
Wend
%>
                                      <%
  Repeat_master1inv_brand_table__index=Repeat_master1inv_brand_table__index+1
  Repeat_master1inv_brand_table__numRows=Repeat_master1inv_brand_table__numRows-1
  master1inv_brand_table.MoveNext()
Wend
%>
                              </table>
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you only expect 1 record back in the secondary query right (the one with getting the count)? if so, use this:

<table class="tftable">
                                <% 
While ((Repeat_master1inv_brand_table__numRows <> 0) AND (NOT master1inv_brand_table.EOF)) 
%>
                                  <tr>
                                    <th><b><%=(master1inv_brand_table.Fields.Item("brand_title").Value)%></b></th>
                                  </tr>
                                <%    
  nst_sw = false
  detail2sfu_inventory_rec_cmd.Parameters("param1").Value = ( master1inv_brand_table.Fields.Item("brand_id").Value)
  Set detail2sfu_inventory_rec = detail2sfu_inventory_rec_cmd.Execute
  
' Show IF Conditional region1 
invCount = 0
  If  not detail2sfu_inventory_rec.BOF and not detail2sfu_inventory_rec.EOF   Then
       invCount  = detail2sfu_inventory_rec.Fields.Item("count_invid").Value
  end if
%>
                                    <tr>
                                      <td>Items in the Inventory assigned to this brand( <strong><%=invCount %></strong>)</td>
                                    </tr>
                                    <%
' End Conditional region1

  Repeat_master1inv_brand_table__index=Repeat_master1inv_brand_table__index+1
  Repeat_master1inv_brand_table__numRows=Repeat_master1inv_brand_table__numRows-1
  master1inv_brand_table.MoveNext()
Wend
%>
                              </table>

Open in new window

1

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
kbsad1Author Commented:
Exactly. Okay I pasted it and it worked. Thanks.
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
ASP

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.