Declare table, insert into table, select distinct

I have a table-valued function where there are multiple tables being declared and finally joined together to create the final table with data from each table. I had a problem with duplicates so I created a new table @ORDERHEADER so select the distinct values and then insert it into the the table and eventually the final table. When I run the select distinct by itself, it populates correctly but when I run it with the insert nothing populates.

Below is the code I am currently working with (I kept the commented out lines for you to see what I have tried — above the first “–LEFT OUTER JOIN” is my working code).

DECLARE @ORDERHEADER TABLE(     [DIV_CD] [varchar](8) NULL,     [SALES_ORD_NR] [varchar](8) NOT NULL,     [CUST_EDP_ID] [decimal](28,0) NOT NULL,     [OFFER_CD] [varchar] (8) NULL)      INSERT INTO @ORDERHEADER     SELECT DISTINCT(SV_OR_1000_ORDER_HEADER.SALES_ORD_NR),  SV_OR_1000_ORDER_HEADER.DIV_CD, SV_OR_1000_ORDER_HEADER.CUST_EDP_ID,  SV_OR_1000_ORDER_HEADER.OFFER_CD     from SV_OR_1000_ORDER_HEADER     --join @RETURNS r on SV_OR_1000_ORDER_HEADER.SALES_ORD_NR =  r.SALES_ORD_NR                 --LEFT OUTER JOIN  OPENQUERY(CH1KNOWAPP,'SELECT DISTINCT SALES_ORD_NR FROM  EmailVPN..EB_RETURN_CONFIRMATION_NEW WITH(NOLOCK) WHERE  ORD_ITM_STA_LGCY_CD IN (''R'',''E'',''4'')') as RET_EMAILS on  RET_EMAILS.SALES_ORD_NR COLLATE SQL_Latin1_General_CP1_CI_AS =  SV_OR_1000_ORDER_HEADER.SALES_ORD_NR     --join @RETURNS r on SV_OR_1000_ORDER_HEADER.SALES_ORD_NR =  r.SALES_ORD_NR      --join SV_MACORD_RETURNS on  SV_OR_1000_ORDER_HEADER.SALES_ORD_NR = SV_MACORD_RETURNS.SALES_ORD_NR     -- where exists (SELECT DISTINCT * from  SV_OR_1000_ORDER_HEADER)-- exists (select * from SV_OR_1000_ORDER_HEADER  join SV_MACORD_RETURNS on SV_OR_1000_ORDER_HEADER.SALES_ORD_NR =  SV_MACORD_RETURNS.SALES_ORD_NR)      --where SV_OR_1000_ORDER_HEADER.SALES_ORD_NR is not null     --where  SV_OR_1000_ORDER_HEADER.OFFER_CD not in ('36')      where SV_OR_1000_ORDER_HEADER.SALES_ORD_NR = 'W3151536'     --group by SV_MACORD_RETURNS.SALES_ORD_NR,  SV_MACORD_RETURNS.DIVISION, SV_OR_1000_ORDER_HEADER.CUST_EDP_ID, SV_MACORD_RETURNS.OFFER_CD     --having COUNT(SV_OR_1000_ORDER_HEADER.SRC_SYS_TRANS_DT) < 2 

Efficiently finding the unique non-zero columns after a variable column in a data table

Suppose I have the following data:

        tempmat=matrix(c(1,1,0,4,1,0,0,4,0,1,0,4, 0,1,1,4, 0,1,0,5),5,4,byrow=T)         tempmat=rbind(rep(0,4),tempmat)         tempmat=data.table(tempmat)         names(tempmat)=paste0('prod1vint',1:4)         tempmat[,firstnonzero:=c(NA,1,1,2,2,2)] 

So the data table looks like this:

> tempmat    prod1vint1 prod1vint2 prod1vint3 prod1vint4 firstnonzero 1:          0          0          0          0           NA 2:          1          1          0          4            1 3:          1          0          0          4            1 4:          0          1          0          4            2 5:          0          1          1          4            2 6:          0          1          0          5            2 

I want to find the number of nonzero elements to the RIGHT of the column indicated by “firstnonzero”.

To make this clear, the desired output then is:

> tempmat    prod1vint1 prod1vint2 prod1vint3 prod1vint4 firstnonzero numbernonzero 1:          0          0          0          0           NA            NA 2:          1          1          0          4            1             2 3:          1          0          0          4            1             1 4:          0          1          0          4            2             1 5:          0          1          1          4            2             2 6:          0          1          0          5            2             1 

This is because, for example, on row 2, there is a nonzero element in prod1vint2 and prod1vint4, so the number of nonzero elements to the right of the first nonzero element is 2, and so forth.

This needs to be efficient and scale well, so it cannot be an apply or looping style solution.

Magento 2 create admin config table with custom rows and columns

I want to create admin config with a table to take the data and save in config for that I followed this link.

But I also want to create custom rows in that table programmatically and also without the last action column and add button. Please refer the image Admin Config

I was unable to find any solution on the web regarding the same nor the file

\Magento\Config\Block\System\Config\Form\Field\FieldArray\AbstractFieldArray

that we extend gave any clues.
Could have easily done this via jquery but want to implement the standard solution.

Code:

<?php namespace Abc\Paymentmethod\Block\Adminhtml\System\Config\Form\Field;  class Feetable extends \Magento\Config\Block\System\Config\Form\Field\FieldArray\AbstractFieldArray {     /**      * @var \Magento\Framework\Data\Form\Element\Factory      */     protected $  _elementFactory;      /**      * @param \Magento\Backend\Block\Template\Context $  context      * @param \Magento\Framework\Data\Form\Element\Factory $  elementFactory      * @param array $  data      */     public function __construct(         \Magento\Backend\Block\Template\Context $  context,         \Magento\Framework\Data\Form\Element\Factory $  elementFactory,         array $  data = []     )     {         $  this->_elementFactory  = $  elementFactory;         parent::__construct($  context,$  data);     }     protected function _construct(){         $  this->addColumn('noi', ['label' => __('NOI'),'readonly'=>'readonly']);         $  this->addColumn('fixed', ['label' => __('Fixed')]);         $  this->addColumn('percent', ['label' => __('Percent')]);         $  this->_addAfter = false;         $  this->_addButtonLabel = __('Add More');         parent::_construct();     }      protected function _prepareArrayRow(\Magento\Framework\DataObject $  row) {         $  options = [1,2,3];         $  row->setData('option_extra_attrs', $  options);     }  } 

system.xml

<field id="abc_fee_table" translate="label comment tooltip" sortOrder="17.4" showInDefault="1" showInWebsite="1" showInStore="0">                     <label>Abc Fee by Number of Installments(NOI)</label>                     <frontend_model>Abc\Paymentmethod\Block\Adminhtml\System\Config\Form\Field\Feetable</frontend_model>                     <backend_model>Magento\Config\Model\Config\Backend\Serialized\ArraySerialized</backend_model>                                     </field> 

The expectation of the total number of pairs of keys in a hash table that collide using universal hashing

I am reading CLRS relating to perfect hashing. When computing the $ $ \mathbb{E}[\sum_{j=0}^{m-1}{n_j\choose{2}}] $ $

where $ m$ is the number of slots in the hash table, and $ n_j$ is the number of keys in position $ j$ . I don’t understand why we can directly conclude that

$ $ \mathbb{E}[\sum_{j=0}^{m-1}{n_j\choose{2}}]\leq{n\choose{2}}\frac{1}{m} $ $

I understand that since $ h$ is randomly chosen from a universal hash function family, $ \Pr{(h(x_i)=h(x_j))}\leq{\frac{1}{m}},\forall{i\neq{j}}$ . I don’t understand why we can use the total number of pairs (the combination part) directly because if $ h(x_i)=h(x_j)$ and $ h(x_j)=h(x_k)$ , then we have $ h(x_i)=h(x_k)$ immediately instead of a probability of $ \frac{1}{m}$ .

Someone can help me out? Thanks!

How do you check if table is empty before resetting the auto increment?

I have a database table that a user can add to and remove data from within my drupal module. What I would like to do is automatically check to see if the table is completely empty after every delete, in which case I would like to run an “ALTER TABLE” to reset the auto increment to 1. However I am having trouble building a function that does this for me.

How can I implement a function to automatically detect if all fields are empty, and automatically reset the increment without the need to go into the database and apply ALTER SQL manually?

Here is some of the code…

function achievementlist_entry_delete($  form, &$  form_state) { foreach(($  form_state['values']['table']) as $  rows) {     $  num_deleted = db_delete('achievements')     ->condition('achvmntID', $  rows)     ->execute(); }     //Here I would like to check the "achievements" database, as well as ALTER TABLE     //it is empty } 

WordPress mariadb – should I use Aria table type?

I’ve been reading about the benefits of Aria table type in MariaDB (we use mysql Ver 15.1 Distrib 5.5.60-MariaDB), which seem to be simple and fast, yet crash-proof, take on MyISAM. Which makes me wonder if I could change the table type on WordPress tables into Aria. Not much to find on Google or StackExchange about this idea. Has anyone done it?

Mysql query to get all columns and its sub contents from other table by its id

I have two tables named “tasks” and “comments”.

I want to get all tasks from “tasks” table.

“comments” table has 2 columns: description and task_id.

task_id is foreign key from task table.

$  tasks = DB::table('tasks')        ->join('comments','tasks.id', '=','comments.task_id')        ->select('tasks.*','comments.* as comments')        ->get(); 

This code returns error.

How can I do this?

Getting the column names of a table by brute force blind SQL injection

Is there a way of getting the column names of a table by brute force blind SQL injection in MySQL. so the query would be:

select column_name from information_schema.columns WHERE table_name = ‘tablename’ and *column names first letter = "a"*  

so if the first column name of that table does not begin with ‘a’, it would return an error if it did begin with ‘a’ it wouldn’t. and do the same for the second, third letters and so on working through the alphabet. Is this possible? or is there a more efficient way maybe? Thanks