Tag: SQL

Duplicates in two columns in a table

thor-in-the-poppies-lisa-twede

That’s a painting of my cat Thor in the backyard.  Apparently he’s looking at the hose, despite the fact that to his right the yard is full of California Poppies.

Today’s blog has some code that is not my own.  Michael Powaga posted it over on Stack Overflow.  I just wanted to keep track of this little gem because I’ve needed it twice now and both times I ended up going with his solution.

I needed to know how many rows (and which ones) were duplicates but not just on one column, on two columns.  I had a situation where (I feel) there should have been a constraint to prevent duplicates in this table.  I wanted to neatly identify the offending records so I could deal with them.

This is his code.  It’s at the link above, but also copied here just in case.

select s.id, t.* 
from [stuff] s
join (
    select name, city, count(*) as qty
    from [stuff]
    group by name, city
    having count(*) > 1
) t on s.name = t.name and s.city = t.city
Advertisements
SQL – Find the largest number in a set of alpha-numeric values

SQL – Find the largest number in a set of alpha-numeric values

References:

The SQL:

declare @lastIntUsed int 

create table #temp (clean_number varchar(50), IntCol int) 
; with tally as (select top (100) N=ROW_NUMBER() over (order by @@spid) from sys.all_columns),
data as ( 
   select sometable.number, clean_number
   from sometable
   cross apply ( 
      select (select C + '' FROM (select N, SUBSTRING(sometable.number, N, 1) C  
          from tally
          where N<=DATALENGTH(sometable.number)) [1]
      where C between '0' and '9'
      order by N
      for xml path('')) 
   ) p (clean_number)
   where p.clean_number is not null 
) 
insert into #temp (clean_number, IntCol) 
select number, cast(clean_number as int) IntCol from data

set @lastIntUsed = (select MAX(IntCol) from #temp) 
print @lastIntUsed
drop table #temp

I couldn’t find any way to just get the max number from the CTE itself (doesn’t mean there isn’t a way), so I said screw it – I’ll just stick the results into a temp table and then I can get the max number or the min or the max number that is less than some value or whatever I want.

Hibernate ProjectionList and ResultTransformer to solve problem of massive queries with endless joins

Hibernate ProjectionList and ResultTransformer to solve problem of massive queries with endless joins

gardenoftwedenprofileHibernate can make query building and entity mapping easy, but if you let it take too much control you can also end up with huge queries that create a big performance drag.

If you know you only need data from a few specific columns of your table or tables, you can use a ProjectionList to target just those columns, and use a ResultTransformer to form the raw results into the sparsely populated entity.

To illustrate the point, let’s take an example of an invoice and its line items.  We have a one-to-many relationship between the invoice table and the line item table.  From the invoice table, we want the invoice_id, invoice_number, invoice_date columns.  The invoice table is linked to a vendor table, and from that table we want the vendor_name column.  From the invoice line items, we want the line_item_number, amount and description columns.

Criteria criteria = session.createCriteria(invoice.class);
criteria.createAlias("vendor", "v");
criteria.createAlias("lineItems", "li");
criteria.setProjection(Projections.projectionList()
    .add(Property.forName("id"))
    .add(Property.forName("invoiceNumber"))
    .add(Property.forName("invoiceDate"))
    .add(Property.forName("v.vendorName"))
    .add(Property.forName("li.lineItemNumber"))
    .add(Property.forName("li.amount"))
    .add(Property.forName("li.description)));

You need to specify aliases for “vendor” and “lineItems” in order to be able to specify the properties from those related entities.  If you had a situation where there wasn’t always a vendor, but you wanted information from invoices that didn’t have vendors, you would specify the alias like this:

criteria.createAlias("vendor", "v", Criteria.LEFT_JOIN);

Since you have a one-to-many relationship from invoice to lineItem, you will get a separate object from this query for every line item.  You will get the same invoice information repeated, with different line item information.  In other words, if you have an invoice that has two line items in it, the raw data returned from the query will look like this:

o = {java.lang.Object[7]}
[0] = 1234   -- the internal ID for the invoice
[1] = "LAP-12355" -- the invoice number
[2] = "12/31/2016" -- the invoice date
[3] = "George's Great Grill" -- the vendor name
[4] = "1" -- the invoice line item number
[5] = 400.23 -- the invoice line item amount
[6] = "ribs" -- the invoice line item description

And then you might have a second object returned with the same information exactly in elements 0 through 3, but with the following for elements 4, 5, and 6:

[4] = "2" -- the invoice line item number
[5] = 20.00 -- the invoice line item amount
[6] = "delivery charge" -- the invoice line item description

This is where the ResultTransformer comes in.  The ResultTransformer is a method that transforms the raw results returned from the SQL query into the entity you use in your Java code.  There are built-in ResultTransformers you can use.  For this example we will write our own, to illustrate how it works.

Specify the ResultTransformer on your criteria like this:

criteria.setResultTransformer(new ResultTransformer()
{
    @Override
    public Object transformTuple(Object[] o, String[] strings)
    {
        return transformObjectToInvoice(o);
    }

    @Override
    public List transformList(List list)
    {
        return consolidateInvoices(list);
    }
});

Then you write a private method transformObjectToInvoice that takes an Object[] and returns an Invoice.  Every invoice it will return will have one line item.  And you write a private method consolidateInvoices that takes a List and returns a List. But the incoming list will have invoices with only one line item, and the outgoing list will have fewer invoices, and the invoices will have 1 to n line items apiece.

So your  transformObjectToInvoice will look something like this:

private Invoice transformObjectToInvoice(Object[] o)
{
    Invoice invoice = new Invoice();
    invoice.setId((Integer) o[0]);
    invoice.setInvoiceNumber((String) o[1]);
    etc.
    return invoice;
}

And your consolidateInvoices will look something like this:

private List consolidateInvoices(List list)
{
    List consolidatedInvoices = new ArrayList<~>();
    Map<Integer, Invoice> invoices = new HashMap<~>();
    For (Invoice oneLineItemInvoice : list)
    {
        Invoice mapInv = invoices.get(oneLineItemInvoice.getId());
        if (mapInv != null)
        {
             mapInv.getLineItems().add(oneLineItemInvoice.getLineItems().iterator().next());
        }
        else
        {
            invoices.put(oneLineItemInvoice.getId(), oneLineItemInvoice);
        }
    }
    consolidatedInvoices.addAll(invoices.values());
    return consolidatedInvoices;
}