Urgent, help needed from experts of Coldfusion! How do u merge the 2 tables together with the zeros included.

lunas_sie
lunas_sie used Ask the Experts™
on
The site is
http://www.geocities.com/toafriend2002/project.html

Image that the database is a totally flat DB, without any parent and child table.

how to merge the 2 tables into this

http://www.geocities.com/toafriend2002/tns.pdf

Thanks a lot
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Please help.

Commented:
2nd page does not work making it hard to know what you want.  Please try to explain what it is you want to do exactly.

Commented:
Well, without knowing which DB you are using, I'll propose a general SQL solution.

===========================
INSERT INTO Table1
   (field1, field2)
   SELECT * FROM Table2
==========================

This will physically insert the entire contents of Table2 into Table1.

If you wish to combine the tables into a temporary recordset, then you can use:

=============================
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
=============================

This makes the following assumptions:
  1.  Table1 and Table2 have identical fields in identical order
  2.  Your DB is SQL92 compliant.

Of course this SQL statement will need to be put into an ADO statement of some sort.

If the fields are different between the two tables, then you can explicitly ennumerate the field list in the SELECT statement, and make any conversions necessary.

If this does not answer your question, please provide the following info:

1. What DB are you using.
2. Name and structure of Table1
3. Name and structure of Table2
4. what is your concern about the zeros?
 
Best Regards,
Jim
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Author

Commented:
1. DB is MS access.
2. Contract_no, reported_date. So i use the count function.
3. U see when one of the contracts have past months contracts and some do not have. So the count function will not display a zero, it display a null. so how do i set, such that even there is no contracts in the table, it will still display the contract and with a zero instead of null.

actually the site on the 1st site, the data is draw from the same database under a flat table. Just that i do not know how to merge them into one.

Thanks a Lot Jim.

*Note: This is a totally flat table.

Author

Commented:
Ok, the second site is working now.

Commented:
lunas_sie,

>>>so how do i set, such that even there is no contracts in the table, it will still display the contract and with a zero instead of null.<<<

You can use the IsNull() function.

--- In your SQL Query ---

SELECT IIF(IsNull(Count(*), 0, Count(*)) AS NumContracts
  FROM Table1


--- In you ASP Code ---
lnCount = oRst("NumContracts")  'assuming you set an alias of "NumContracts"
IF IsNull(lnCount) THEN lnCount = 0

Does this answer your question?

Best Regards,
Jim

Author

Commented:
Bingo, that is what i'm asking

can it be use together with group by function??

This is my original codes

<CFQUERY NAME="get_contractCC" DATASOURCE="cicf">
  SELECT contract_no, Count(Sheet1.Contract_No) AS CountOfContract_No
  FROM Sheet1
  WHERE Hierarchy_4 ='#url.grp#'
  AND (((DatePart('m',[Reported]))=DatePart('m',Date())))
  Group By Contract_no
  </cfquery>

Commented:
Yep, just change one line:

SELECT contract_no,
   IIF( IsNull(Count(Sheet1.Contract_No)),
      0,
      Count(Sheet1.Contract_No) )
   AS CountOfContract_No

That should do it.

Commented:
Please note that the IIF function is specific to MS Access.  IF you were to upgrade this to SQL Server then you would need to use the TSQL function IsNull, which is different than the Access/VB IsNull:

=========================
SELECT contract_no,
   IsNull(Count(Sheet1.Contract_No), 0)
   AS CountOfContract_No
=========================

Here's the description of TSQL IsNull:

Syntax
ISNULL ( check_expression , replacement_value )

Arguments
check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.


Author

Commented:
do i use union too?

Author

Commented:
i need it such that all the contracts name under the same name, which is of different table can merge together as 1 and if they have it for the current month and do not have it for the past months, the past months will display a zero together with the contract name will also be displayed.

Thanks for yr time., Jim

Commented:
You need to use UNION only if you want to combine the rows from two different tables, as shown in my previous post.

Do you have two tables?  What is the name of the 2nd table?

Author

Commented:
they is only 1 table.

the code u have suggested did not display the zero. it still remains the same. please refer back to the 1st site.

Commented:
lunas,

Please double-check your code.  If my sample code was implemented correctly, I'm very sure it will return a zero.

Please copy-and-paste the exact code you are running.

Best Regards,
Jim

Author

Commented:
they is only 1 table.

the code u have suggested did not display the zero. it still remains the same. please refer back to the 1st site.

Commented:
lunas,

You seem to have a duplicate post. did read get my last post?

Author

Commented:
the database looks like this.

Reported_date                      Contract_no
-------------                      -----------
7/6/2001 6:21:00 PM                P014-4 STB
8/6/2001 6:21:00 PM                P014-4 STB
9/6/2001 6:21:00 PM                P014-5 STB

something like that, totally flat.

i did copy yr codes directly but it still didn't shows the zero.

Author

Commented:
the database looks like this.

Reported_date                      Contract_no
-------------                      -----------
7/6/2001 6:21:00 PM                P014-4 STB
8/6/2001 6:21:00 PM                P014-4 STB
9/6/2001 6:21:00 PM                P014-5 STB

something like that, totally flat.

i did copy yr codes directly but it still didn't shows the zero.

Author

Commented:
Yes, i've read.
it's ok then, i even ask ny lectuers about it, they ask me to use the array to buffer, then display but it's too much work.

u did help me i'll give u the points, it really didn't display the zero.

Commented:
Thanks.

I can't understand why it wouldn't display the zero.  If you will post the actual code you ran, I will try to help you.

Best Regards,
Jim

Author

Commented:
u tried? it can??

Commented:
Sorry, I can't understand what you're saying.  If you would like to expand on what you're saying, I will try to help.

Best Regards,
Jim

Author

Commented:
This is for the past months.

SELECT contract_no, IIF( IsNull(Count(Sheet1.Contract_No)),  0,  Count(Sheet1.Contract_No) )  AS CountOfContract_No
FROM Sheet1
WHERE Hierarchy_4 ='#url.grp#' AND (((DatePart('m',[Reported])) < DatePart('m',Date())))
GROUP BY Sheet1.Contract_No;

This is for the current month:

SELECT contract_no, IIF( IsNull(Count(Sheet1.Contract_No)),0, Count(Sheet1.Contract_No) ) AS CountOfContract_No
 FROM Sheet1
  WHERE Hierarchy_4 ='#url.grp#' AND (((DatePart('m',[Reported]))=DatePart('m',Date())))
  GROUP BY Sheet1.Contract_No;

Commented:
lunas,

I have tested this using MS Access, and the Count() function always returns some value.  Note that it is counting the number of records found for each distinct contract_no.

Can you test your query string using the Access Query builder?  Of course you will have to substitute some value for #url.grp#

Can you show me the code where you display the results of the ASP query?

Author

Commented:
yup, i did sub a valid value into #url.grp#.

and it did return the count values, but did not display the zeros.

Commented:
Can you post the ASP code that displays the results?

Author

Commented:
Jim, thanks...

<html>
<head>
<title>Daily Report</title>
</head>

<CFQUERY NAME="getGroups" DATASOURCE="cicf">
SELECT     Distinct Hierarchy_4
FROM         Sheet1
WHERE  Hierarchy_4='#url.grp#'
</CFQUERY>


<body bgcolor="#FFFFFF" text="#000000">

<CFQUERY NAME="pastdate" DATASOURCE="cicf">
SELECT      Call_ID
FROM         Sheet1
WHERE (((DatePart('m',[Reported])) < DatePart('m',Date())))
AND Hierarchy_4='#url.grp#'
</CFQUERY>
 
 <CFQUERY NAME="curdate" DATASOURCE="cicf">
SELECT      Call_ID
FROM         Sheet1
WHERE (((DatePart('m',[Reported]))=DatePart('m',Date())))
AND Hierarchy_4='#url.grp#'
</CFQUERY>

 <CFQUERY NAME="get_contractCC" DATASOURCE="cicf">
  SELECT contract_no, IIF( IsNull(Count(Sheet1.Contract_No)),0, Count(Sheet1.Contract_No) ) AS CountOfContract_No
 FROM Sheet1
  WHERE Hierarchy_4 ='#url.grp#' AND (((DatePart('m',[Reported]))=DatePart('m',Date())))
  GROUP BY Sheet1.Contract_No;
  </cfquery>
<cfset valid = True>

<table width="94%" border="0">
  <tr>
    <td width="45%" height="20"><b><u>Open Tickets Report For Department </u></b></td>
   
      
      <td width="20%" height="20">       
    <h3><font color="#0033CC"><cfoutput query="getGroups"><b>#getGroups.Hierarchy_4#</cfoutput></b></font></h3>
    </td>
      
      
      
      
    <td width="35%" height="20">
      <div align="right">
        <h3><font color="#0033CC"><cfoutput><b>#DateFormat(Now())#</b></cfoutput></font></h3>
      </div>
    </td>
  </tr>

<cfoutput>
<cfset curdate1=#curdate.recordcount#>
<cfset pastdate1=#pastdate.recordcount#>
</cfoutput>

<cfset total=curdate1 + pastdate1>

  <tr>
    <td width="45%">Open Tickets from the <font color="#FF0000"><strong>Past Months</strong></font>
      =</td>
    <td width="20%"><b><font color="#FF0000"><cfoutput>#pastdate.recordcount#</cfoutput></font></b></td>
    <td width="35%">&nbsp;</td>
  </tr>
  <tr>
    <td width="45%">Open Ticket from in <strong><font color="blue">Current Month</font></strong>
      = </td>
    <td width="20%"><b><u><font color="blue"><cfoutput>#curdate.recordcount#</cfoutput></font></u></b></td>
    <td width="35%">&nbsp;</td>
  </tr>
  <tr>
    <td width="45%" height="40">
      <div align="left"><b>Total SBSQ Tickets Open =</b></div>
     
    </td>
    <td width="20%" bgcolor="#FFFFFF"><font color="black"><b><cfoutput>#total#</cfoutput></b></font></td>
    <td width="35%">&nbsp;</td>
  </tr>
</table>

<p><b>NOTE: </b>This report will include Tickets currently escalated to workgroups
  in other Siemens Units.</p>
<p>&nbsp;</p>
<p><strong><u>Open Tickets Report breakdown by Contracts</u></strong></p>
 
<CFQUERY NAME="get_contract" DATASOURCE="cicf">
SELECT     distinct contract_no
FROM         Sheet1
WHERE Hierarchy_4 ='#url.grp#'
ORDER BY contract_no desc
</CFQUERY>
<table width="70%" border="0">
  <tr>
    <td width="41%" height="146">
      <table width="98%" height="100%" border="1">
        <tr>
          <td width="47%" height="52"><strong><font color="Red">Past Months TT</font></strong></td>
          <td> <div align="center"><strong>Total</strong></div></td>
        </tr>
        <CFQUERY NAME="get_contractCP" DATASOURCE="cicf">
        SELECT contract_no, IIF( IsNull(Count(Sheet1.Contract_No)),  0,  Count(Sheet1.Contract_No) )  AS CountOfContract_No
 FROM Sheet1
 WHERE Hierarchy_4 ='#url.grp#' AND (((DatePart('m',[Reported]))
        < DatePart('m',Date()))) GROUP BY Sheet1.Contract_No;
        </cfquery>
        <cfoutput query="get_contractCP">
          <!-- CFIF is HERE -->
          <cfif get_contractCP.contract_no  is NOT " ">
            <cfset contract_name = "#get_contractCP.contract_no#">
            <cfelse>
            <cfset contract_name = "No Contract no.">
          </cfif>
          <tr>
            <td height="47">
              <div align="center">
                <div align="center"><strong><a href="siemens/contract_detail.cfm?con=#get_contractCP.contract_no#&grp=#url.grp#">
                  #contract_name# </a> </strong></div>
              </div></td>
            <td><b>
              <div align="center">
                <p><font color="Red">#get_contractCP.CountOfContract_No#</font></p>
              </div>
              </b></td>
          </tr>
        </cfoutput>
        <tr>
          <td> <div align="center"><strong><font color="Red">Sub Total Past Months</font></strong></div>
            <div align="center"></div></td>
          <td> <div align="center"><b><font color="Red"><cfoutput>#pastdate.recordcount#</cfoutput></font></b></div>
            <div align="center"></div></td>
        </tr>
      </table></td>
    <td width="59%"><table width="50%" height="100%" border="1">
        <tr>
          <td height="51"><strong><font color="Blue">Current Month TT</font></strong></td>
      <td><div align="center"><strong>Total</strong></div></td>
    </tr>
      <cfoutput query="get_contractCC">
    <cfif get_contractCC.contract_no  is NOT " ">
      <cfset contract_nameC = "#get_contractCC.contract_no#">
      <cfelse>
      <cfset contract_nameC = "No Contract no.">
    </cfif>
    <tr>
            <td height="46">
              <div align="center"> <a href="siemens/contract_detail.cfm?con=#get_contractCC.contract_no#&grp=#url.grp#"><strong>
          #contract_nameC# </strong></a></div></td>
      <td><div align="center"><font color="blue"><strong>#get_contractCC.CountOfContract_No#</strong></font></div></td>
    </tr>
      </cfoutput>
    <tr>
          <td height="57">
            <div align="center"><strong><font color="Blue">Sub Total
          Current Months</font></strong></div></td>
      <td><div align="center"><strong><font color="Blue"><cfoutput>#curdate.recordcount#</cfoutput></font></strong></div></td>
    </tr>
  </table></td>
  </tr>
</table>
<cfoutput> </cfoutput><br>
  <b><cfoutput><a href="siemens/Allcontract_detail.cfm?grp=#url.grp#">CLick
    Here</a></cfoutput>&nbsp;to display all records of group: <cfoutput><b><font color="##FF0000">#url.grp#</font></b></cfoutput>
  </b>


</body>
</html>
Commented:
Lunas,

I can't see anything wrong with your code, but then I'm NOT familiar with Cold Fusion.  So I don't know why the Count() function is not displaying zero as "0".

I hope I've answered your original question about how to merge the two tables.  If you need any further clarification concerning this I'll be glad to help.

I can make two suggestions concerning the zero display problem:

1.  Extract a small code segment into a new file just to test the SELECT and display of zero.  It will be easier to debug than the large file  you have.

2.  Post a question in the CF forum about the zero problem.

I hope you'll agree that your original question has been  answered.

Best Regards,
Jim

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial