Tag: Query

UDF in MS SQL Server returning a query starting with “with” cause

This is a perfectly valid SQL Query and works nicely (for e.g.) in Microsoft SQL Server 2014:

with t as (select 'a' as attr) select * from t

it returns a, surely one can simplify it and drop the with part, but that’s not the point, the point is to find out a smallest example that illustrates the need/problem. I can pack part of it as a UDF:

CREATE PROCEDURE x(@__employee_id uniqueidentifier) AS RETURN (select 'a' as attr);

How can I pack a query that has with block? My aim is to achieve something like:

CREATE PROCEDURE x (@__employee_id uniqueidentifier) AS RETURN (     with t as (select 'a' as attr) select * from t );

however sql server does not like it, firing an error:

Msg 156, Level 15, State 1, Procedure x, Line 4 [Batch Start Line 0] Incorrect syntax near the keyword 'with'.

NOT EXISTS with two subquery fields that match 2 fields in main query

Background: Two different document types in a document management system. Both Doc Type A and Doc Type B have a Ticket #, and a Ticket Date. What we’re looking for: Doc Type A docs that don’t have a matching Doc Type B doc (NOT EXISTS) with the same Ticket # and Ticket Date. There like are Doc Type B docs that have the same Ticket # but NOT the same Ticket Date. We want to ignore those. Seems simple…. but I am stuck. So far what I have is something like this:

SELECT distinct ki110.keyvaluechar AS "Ticket #", ki101.keyvaluedate AS "Ticket Date"  FROM itemdata  left outer join hsi.keyitem110 ki110 on ( itemdata.itemnum = ki110.itemnum ) left outer join hsi.keyitem101 ki101 on ( itemdata.itemnum = ki101.itemnum ) WHERE   ki101.keyvaluedate BETWEEN '01-01-2021' AND '01-31-2021' AND ( itemdata.itemtypenum  = 178  ) -- this is Doc Type A  AND NOT EXISTS (select ki110.keyvaluechar, ki101.keyvaluedate from itemdata, keyitem110 ki110 , keyitem101 ki101 where --(itemdata.itemnum = ki110.itemnum) --Ticket #

— ** the problem is here for Date: I need to say Date in Doc Type B doc is not the same as Date in Doc Type A doc using ki101.keyvaluedate)

AND itemdata.itemtypenum = 183) -- this  is DOC Type B

Does mysql “order by” guarantees to give the same order for the same query every time if the sort key is equal?

I’m planning to write a query that sorts the result by a value s and then paginate the result. Let’s say I have ten items that match the query and all the items have the same s. In the first query, I sort the value by s and get the first five items. Then in the next query, I sort the value by s and get the sixth to tenth items. Is it possible that the items that appear on my first query will appear again in my second query, and that some items will not show up in either query?

Query to find the second highest row in a subquery

The goal is to send notifications about the customer updates but only for the first one if there are consecutive updates from the customer in a ticketing system.

This is the simplified query that I’m using to get the data that I need. There are a few more columns in the original query and this subquery for threads is kind of required so I can also identify if this is a new ticket or if existing one was updated (in case of update, the role for the latest threads will be a customer):

SELECT t.ref, m.role    FROM tickets t    LEFT JOIN threads th ON (t.id = th.ticket_id)    LEFT JOIN members m ON (th.member_id = m.id)   WHERE th.id IN ( SELECT MAX(id)                      FROM threads                     WHERE ticket_id = t.id                 )

It will return a list of tickets so the app can send notifications based on that:

+------------+----------+ | ref        | role     | +------------+----------+ | 210117-001 | customer | | 210117-002 | staff    | +------------+----------+

Now, I want to send only a single notification if there a multiply consecutive updates from the customer.

Question:

How I can pull last and also one before last row to identify if this is consecutive reply from the customer?

I was thinking about GROUP_CONCAT and then parse the output in the app but tickets can have many threads so that’s not optimal and there are also a few more fields in the query so it will violate the ONLY_FULL_GROUP_BY SQL mode.

db<>fiddle here

Executing a query on the production to fix erroring data

During the creation of some data on the production server by our backend, an unexpected error occurred and some data became inaccessible. Currently, a fix is being found for the error, but the flawed data is still on the server, causing some trouble. This can easily be fixed by setting some data to NULL. The question is, how should we handle this? Our backend uses the Spring framework and Liquibase to update the schema when necessary. Our production server is managed by a different company and is unaccessible for us (the developers). The other company wants us to write an entry for the Liquibase XML file to fix this. We are questioning this decision, but running a query on the production database seems tricky too.

Note: we are running more than just the production environment, so by adding the query to the Liquibase XML file, it will run on all environments.

Case statement query

How can this be re-written. somehow not able to make it right. In case statement Then and ELSE are same would like to change it to single statement.

SELECT t.status_id status_id, ( CASE WHEN t .status_id NOT IN (SELECT status_id FROM firm_return WHERE enabled = ‘D’ and firm_id=t.firm_id)

THEN (cl.category_code + ‘-‘ + s.status_name) ELSE cl.category_code + ‘-‘ + s.status_name END) AS status_type FROM bla bla

How to check if a column in a query can produce NULL?

I am scheming how to implement a type generator for arbitrary SQL queries.

I found that for any query, the simplest way to get the types of every column is to create a temporary table and then inspect that table. However, this tells nothing about which columns can be NULL.

I am interested to find a solution to identifying which columns can be NULL for any arbitrary query with the goal of generating types for that query.

What’s the way to go about it?

Change query based on post type while staying in loop

I have a loop that displays three custom post types.

Thing is, all of these post types use a different logic. ‘elemzesek’ and ‘gyorshirek’ use ACF fields which you can see in the meta_query.

However, the ‘cikkek’ post type, to display the correct articles, would need a

‘tag’ => get_the_title();

The problem is, if this is added, and it runs for ‘elemzesek’ and ‘gyorshirek’ as well, the loops finds no results.

Can someone help me in achieving this?

<?php      $  ceg = get_the_title();     $  ceg_megadasa = get_field('ceg_megadasa');     $  paged = ( get_query_var( 'paged' ) ) ? get_query_var( 'paged' ) : 1;     $  query = new WP_Query( array(         'post_type' => array('cikkek', 'elemzesek', 'gyorshirek'),         'posts_per_page' => 10,         'paged' => $  paged,         'post_status' => 'publish',         // 'tag' => $  ceg,         'orderby' => 'date',         'order' => 'DESC',         'meta_query' => array(             'relation' => 'OR',             array(                 'key' => 'ceg_megadasa',                 'value' => get_the_ID()             ),             array(                     'key' => 'ceg_megadasa_gyorshirek',                     'value' => get_the_ID()                 ),                      )     ) ); ?>

Combining multiple numeric meta_key’s in an order_by query for a custom woocommerce sorting option [closed]

I would like to create a custom woocommerce product orderby sort option that combines the values of one custom numeric field

  • ‘sort_price’

with another numeric field (woocommerce regular price)

  • ‘_price’

And order the combined values in ASC order

edit: I see this forum is wordpress only, I will take this to the woocommerce plugin forum, apologies, I can’t seem to delete this.

My code so far achieves ordering by sort_price alone but I am unsure how to combine the two.

add_filter( 'woocommerce_get_catalog_ordering_args', 'custom_woocommerce_get_catalog_ordering_args' );  function custom_woocommerce_get_catalog_ordering_args( $  args ) {   $  orderby_value = isset( $  _GET['orderby'] ) ? wc_clean( $  _GET['orderby'] ) : apply_filters( 'woocommerce_default_catalog_orderby', get_option( 'woocommerce_default_catalog_orderby' ) );     if ( 'random_list' == $  orderby_value ) {         $  args['orderby'] = 'meta_value_num';         $  args['order'] = 'ASC';         $  args['meta_key'] = 'sort_price';     }     return $  args; } add_filter( 'woocommerce_default_catalog_orderby_options', 'custom_woocommerce_catalog_orderby' ); add_filter( 'woocommerce_catalog_orderby', 'custom_woocommerce_catalog_orderby' ); function custom_woocommerce_catalog_orderby( $  sortby ) {     $  sortby['random_list'] = 'Price Low to High Custom';     return $  sortby; }

there is an example in the wp codex which demonstrates using multiple meta keys but I am a little unsure of what the compare and value parts in the array are doing.

    'meta_query' => array(         'relation' => 'AND',         'state_clause' => array(             'key' => 'state',             'value' => 'Wisconsin',         ),         'city_clause' => array(             'key' => 'city',             'compare' => 'EXISTS',         ),      ),     'orderby' => array(          'city_clause' => 'ASC',         'state_clause' => 'DESC',     ),

How can I use COUNT in the same query with SUM()?

Ok, I have three tables: theater, spectacles and tickets. A theater has one or more spectacles and for a spectacle were sold one or more tickets* So, id_theater is foreign key in TABLE SPECTACLES and id_spectacle is foreign key in TABLE TICKETS. In table TICKETS, I have ticket_price and id_ticket and with count(id_ticket) I calculate the number of tickets sold for spectacles. And each theater has one or more spectacles. The value for spectacles is calculated as: count(tickets.id_ticket) * tickets.ticket_price. After I calculate the value for spectacles, I need to gather the value of spectacles to find the each value of theater and for this I use SUM().

I need to write a query where I need to calculate the total value obtained by each theater and to be sort by value. For this I need to use the SUM() function. I tried so many different options, but I have different errors. When I tried to run this, this error appeared :

SQL Error: ORA-00937: not a single-group group function 
SELECT       theater.id_theater,       SUM(count(tickets.id_ticket) * tickets.id_ticket) total_theater FROM spectacles, theater GROUP BY theater.id_theater ORDER BY total_theater DESC;

For this problem I must use SUM, but I really don’t know what do to at this point and would appreciate any help.