4 Replies Latest reply on Aug 21, 2006 1:34 PM by elDonrico

    Select Statement  Help

    elDonrico Level 1
      I havent run this yet as there is still much work to be done. but, this is the beginnings. i am uploading a csv file and selecting data from tables and bulk inserting the rows into a table.

      My question is: so far, will this work?? the left join that has to have two matching variables... is that how i would do it?
      and am i putting the select in the right place in the cfloop?

      thanks for any input on this one.


      <cffile action="UPLOAD" filefield="clockfile" destination="#ExpandPath('uploadFolder')#" nameconflict="overwrite" >

      <cfset CRLF = Chr(13) & Chr(10)>
      <cffile action="READ" file="#ExpandPath('uploadFolder')#/clocktest.csv" variable="csvData">
      <!---
      FILE LAYOUT:
      #csvArray[1]#,#csvArray[2]#,#csvArray[3]#,#csvArray[4]#,#csvArray[5]#
      Number, export code, regular, overtime, dbl time
      empl_code, assign_no, regular, overtime, dbl time
      --->
      <cfloop index="csvRecord" list="#csvData#" delimiters="#CRLF#">
      <cfset csvArray = ArrayNew(1)>
      <cfset csvCellCount = 1>
      <cfloop index="csvCell" list="#csvRecord#" delimiters=",">
      <cfset csvArray[variables.csvCellCount] = csvCell>
      <cfset csvCellCount = csvCellCount + 1>

      </cfloop>
      <cfquery name="rsMatch" datasource="standard">
      SELECT a.branch_code, a.bus_segment, a.cust_code, a.cust_dept, a.order_num, a.reference, a.reference2, wk.wc_class, c.wc_state_no,
      wr.wc_rate, o.wc_code, a.reg_prate, a.reg_brate, a.ovt_prate, a.ovt_brate, a.dbl_prate, a.dbl_brate, a.bill_to_code, a.owner_num,
      a.taker_num, cb.slsp_code, cb.sls_are_code, a.type_service
      FROM (((((informix.styasgne a
      LEFT JOIN informix.styordre o ON a.order_num = o.order_num)
      LEFT JOIN informix.strcustr c ON a.cust_code = c.cust_code)
      LEFT JOIN informix.strcbsgr cb ON a.cust_code = cb.cust_code AND (a.bus_segment = cb.bus_segment))
      LEFT JOIN informix.stywcskr wk ON o.wc_code = wk.wc_code)
      LEFT JOIN informix.stywcrtr wr ON wk.wc_class = wr.wc_class)
      WHERE a.assign_no = '#csvArray[2]#'
      </cfquery>

      <cfquery datasource="standard">
      INSERT INTO paywetmp
      (
      batch_no, batch_rec, batch_pflag, batch_user_num, batch_date, merge_no, empl_code, week_end, branch_code, bus_segment,
      cust_code, cust_dept, order_num, assign_no, reference, reference2, wc_class, wc_state_no, wc_rate, wc_base_amt, wc_amt,
      wc_code, reg_phrs, reg_prate, reg_pamt, reg_bhrs, reg_brate, reg_bamt, ovt_phrs, ovt_prate, ovt_pamt, ovt_bhrs, ovt_brate,
      ovt_bamt, dbl_phrs, dbl_prate, dbl_pamt, dbl_bhrs, dbl_brate, dbl_bamt, tot_phrs, tot_pamt, tot_bhrs, tot_bamt, tot_mtax_pamt,
      tot_mtax_bamt, tot_mded_pamt, tot_mded_bamt, reg_pa, reg_at, ovt_pa, ovt_at, dbl_pa, dbl_at, tsht_type, bill_to_code, owner_num,
      taker_num, slsp_code, sls_area_code, type_service, cl_week_end, billed, tot_mntx_pamt, tot_mntx_bamt, pay_type, pay_year,
      pay_month, pay_qtr, pay_period, gl_period, gl_year, div_code
      )
      VALUES
      (

      **blah blah**

      )
      </cfquery>
      </cfloop>
        • 1. Re: Select Statement  Help
          Dan Bracuk Level 5
          I took a cursory look. Unless the blah blah blah part includes something from your select query, why bother running it?
          • 2. Re: Select Statement  Help
            elDonrico Level 1
            yes dan, it is, but that isnt where my problem is, i just shortened the code. is the rest of it ok?
            • 3. Re: Select Statement  Help
              draves Level 1
              The insert will only use the first row from the prior query. If you might have multiple rows you could put a cfloop around the insert but it would be faster to just combine the insert and select queries.
              • 4. Re: Select Statement  Help
                elDonrico Level 1
                no, that part works fine. in fact, i have gotten the whole page to act right thnx, here is the whole code if it can help someone else out with it.

                cfmx 6.1
                informix 7.3.1
                bulk loader
                csv loader

                <cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
                <cfif IsDefined("FORM.InsertRecord") AND FORM.InsertRecord EQ "loadreceiver">

                <cffile action="UPLOAD" filefield="clockfile" destination="#ExpandPath('uploadFolder')#" nameconflict="overwrite" >

                <cfset CRLF = Chr(13) & Chr(10)>
                <cffile action="READ" file="#ExpandPath('uploadFolder')#/clocktest.csv" variable="csvData">
                <!---
                FILE LAYOUT:
                #csvArray[1]#,#csvArray[2]#,#csvArray[3]#,#csvArray[4]#,#csvArray[5]#
                empl_code, assign_no, regular, overtime, dbl time
                --->
                <cfquery name="rsPeriod" datasource="standard">
                SELECT curr_year, curr_month, curr_qtr, curr_period
                FROM stycurpd
                </cfquery>
                <cfset current_batch_number = form.batch_no>
                <cfset current_record_number = 0>
                <cfloop index="csvRecord" list="#csvData#" delimiters="#CRLF#">
                <cfset current_record_number = current_record_number + 1>
                <cfset csvArray = ArrayNew(1)>
                <cfset csvCellCount = 1>
                <cfif current_record_number EQ 100>
                <cfset current_batch_number = current_batch_number + 1>
                <cfset current_record_number = 1>
                </cfif>
                <cfloop index="csvCell" list="#csvRecord#" delimiters=",">
                <cfset csvArray[variables.csvCellCount] = csvCell>
                <cfset csvCellCount = csvCellCount + 1>
                </cfloop>

                <cfquery name="rsMatch" datasource="standard">
                SELECT
                a.branch_code, a.bus_segment, a.cust_code, a.assign_dept, a.order_num, a.reference1, a.reference2, wk.wc_class, c.wc_state_no,
                wr.wc_calc_rate, o.wc_code, a.reg_prate, a.reg_brate, a.ovt_prate, a.ovt_brate, a.dbl_prate, a.dbl_brate, a.bill_to_code, a.owner_num,
                a.taker_num, cb.slsp_code, cb.sls_area_code, a.type_service
                FROM standard:informix.styasgne a, standard:informix.styordre o, standard:informix.strcustr c, standard:informix.strcbsgr cb, standard:informix.stywcskr wk, standard:informix.stywcrtr wr
                WHERE (a.order_num=o.order_num) AND (a.cust_code=c.cust_code) AND (c.cust_code=cb.cust_code) AND (o.wc_code=wk.wc_code) AND (wk.wc_class=wr.wc_class) AND a.assign_no='#csvArray[2]#'
                </cfquery>

                <cfquery datasource="standard">
                INSERT INTO paywetmp
                (
                batch_no, batch_rec, batch_pflag, batch_user_num, batch_date, merge_no, empl_code, week_end, branch_code, bus_segment,
                cust_code, cust_dept, order_num, assign_no, reference, reference2, wc_class, wc_state_no, wc_rate, wc_base_amt, wc_amt,
                wc_code, reg_phrs, reg_prate, reg_pamt, reg_bhrs, reg_brate, reg_bamt, ovt_phrs, ovt_prate, ovt_pamt, ovt_bhrs, ovt_brate,
                ovt_bamt, dbl_phrs, dbl_prate, dbl_pamt, dbl_bhrs, dbl_brate, dbl_bamt, tot_phrs, tot_pamt, tot_bhrs, tot_bamt, tot_mtax_pamt,
                tot_mtax_bamt, tot_mded_pamt, tot_mded_bamt, reg_pa, reg_at, ovt_pa, ovt_at, dbl_pa, dbl_at, tsht_type, bill_to_code, owner_num,
                taker_num, slsp_code, sls_area_code, type_service, cl_week_end, billed, tot_mntx_pamt, tot_mntx_bamt, pay_type, pay_year,
                pay_month, pay_qtr, pay_period, gl_period, gl_year, div_code
                )
                VALUES
                (
                #current_batch_number#
                ,
                #current_record_number#
                ,
                <cfif IsDefined("FORM.batch_pflag") AND #FORM.batch_pflag# NEQ "">
                '#FORM.batch_pflag#'
                <cfelse>
                NULL
                </cfif>
                ,
                <cfif IsDefined("FORM.batch_user_num") AND #FORM.batch_user_num# NEQ "">
                '#FORM.batch_user_num#'
                <cfelse>
                NULL
                </cfif>
                ,
                <cfif IsDefined("FORM.batch_date") AND #FORM.batch_date# NEQ "">
                '#FORM.batch_date#'
                <cfelse>
                NULL
                </cfif>
                ,
                0
                ,
                #csvArray[1]#
                ,
                <cfif IsDefined("FORM.week_end") AND #FORM.week_end# NEQ "">
                '#FORM.week_end#'
                <cfelse>
                NULL
                </cfif>
                ,
                '#rsMatch.branch_code#'
                ,
                '#rsMatch.bus_segment#'
                ,
                #rsMatch.cust_code#
                ,
                '#rsMatch.assign_dept#'
                ,
                #rsMatch.order_num#
                ,
                #csvArray[2]#
                ,
                '#rsMatch.reference1#'
                ,
                '#rsMatch.reference2#'
                ,
                '#rsMatch.wc_class#'
                ,
                '#rsMatch.wc_state_no#'
                ,
                #rsMatch.wc_calc_rate#
                ,
                #ABS(ABS(csvArray[3] + csvArray[4] + csvArray[5]) * rsMatch.reg_prate)#
                ,
                #ABS(ABS(ABS(csvArray[3] + csvArray[4] + csvArray[5]) * rsMatch.reg_prate) * rsMatch.wc_calc_rate)#
                ,
                '#rsMatch.wc_code#'
                ,
                #csvArray[3]#
                ,
                #rsMatch.reg_prate#
                ,
                #ABS(csvArray[3] * rsMatch.reg_prate)#
                ,
                #csvArray[3]#
                ,
                #rsMatch.reg_brate#
                ,
                #ABS(csvArray[3] * rsMatch.reg_brate)#
                ,
                #csvArray[4]#
                ,
                #rsMatch.ovt_prate#
                ,
                #ABS(csvArray[4] * rsMatch.ovt_prate)#
                ,
                #csvArray[4]#
                ,
                #rsMatch.ovt_brate#
                ,
                #ABS(csvArray[4] * rsMatch.ovt_brate)#
                ,
                #csvArray[5]#
                ,
                #rsMatch.dbl_prate#
                ,
                #ABS(csvArray[5] * rsMatch.dbl_brate)#
                ,
                #csvArray[5]#
                ,
                #rsMatch.dbl_brate#
                ,
                #ABS(csvArray[5] * rsMatch.dbl_brate)#
                ,
                #ABS(csvArray[3] + csvArray[4] + csvArray[5])#
                ,
                #ABS(ABS(csvArray[3] * rsMatch.reg_prate))+ (ABS(csvArray[4] * rsMatch.ovt_prate)) + (ABS(csvArray[5] * rsMatch.dbl_prate))#
                ,
                #ABS(csvArray[3] + csvArray[4] + csvArray[5])#
                ,
                #ABS(ABS(csvArray[3] * rsMatch.reg_brate))+ (ABS(csvArray[4] * rsMatch.ovt_brate)) + (ABS(csvArray[5] * rsMatch.dbl_brate))#
                ,
                0
                ,
                0
                ,
                0
                ,
                0
                ,
                0
                ,
                0
                ,
                0
                ,
                0
                ,
                0
                ,
                0
                ,
                'R'
                ,
                '#rsMatch.bill_to_code#'
                ,
                #rsMatch.owner_num#
                ,
                #rsMatch.taker_num#
                ,
                '#rsMatch.slsp_code#'
                ,
                '#rsMatch.sls_area_code#'
                ,
                '#rsMatch.type_service#'
                ,
                <cfif IsDefined("FORM.week_end") AND #FORM.week_end# NEQ "">
                '#FORM.week_end#'
                <cfelse>
                NULL
                </cfif>
                ,
                'N'
                ,
                0
                ,
                0
                ,
                'W'
                ,
                #rsPeriod.curr_year#
                ,
                #rsPeriod.curr_month#
                ,
                #rsPeriod.curr_qtr#
                ,
                #rsPeriod.curr_period#
                ,
                #rsPeriod.curr_period#
                ,
                #rsPeriod.curr_year#
                ,
                'MZZ'
                )
                </cfquery>
                </cfloop>

                <cflocation url="index.cfm">
                </cfif>

                <html>
                <head>
                <title>Load Clock File</title>
                <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
                </head>

                <body>

                <form name="loadreceiver" action="<cfoutput>#CurrentPage#</cfoutput>" method="post" enctype="multipart/form-data">
                <div align="center">
                <input type="file" name="clockfile">
                <br />
                </div>
                <table width="80%" border="1" align="center" cellpadding="3" cellspacing="1">
                <tr valign="baseline">
                <td nowrap align="right">Batch Begin:</td>
                <td><input type="text" name="batch_no" value="" size="32"></td>
                </tr>
                <tr valign="baseline">
                <td nowrap align="right">User Number:</td>
                <td><input type="text" name="batch_user_num" value="<cfoutput>#cgi.AUTH_TYPE#</cfoutput>" size="32"></td>
                </tr>
                <tr valign="baseline">
                <td nowrap align="right">Client End Date:</td>
                <td><input type="text" name="week_end" value="<cfoutput>#today#</cfoutput>" size="32"></td>
                </tr>
                <tr valign="baseline">
                <td nowrap align="right">Pull Flags: </td>
                <td><input name="batch_pflag" type="text" id="batch_pflag" size="3"></td>
                </tr>
                <tr valign="baseline">
                <td colspan="2" align="right" nowrap><div align="center">
                <input type="submit" value="Load Clock File">
                </div></td>
                </tr>
                </table>
                <input type="hidden" name="InsertRecord" value="loadreceiver">
                <input type="hidden" name="batch_date" value="<cfoutput>#dateformat(NOW(), 'yyyy-m-d')#</cfoutput>">
                </form>




                </body>
                </html>