Tag: statement

Case statement with null values for MySQL

In MYSQL, I have a sales table with sales.id and industry table with primary_industry and secondary_industries in the same column which differentiate by industry_id. The logic is (CASE WHEN cl.industry_id IS NULL THEN cl.industry END ). For each sales_id there might be multiple primary industries and the requirement is to get all the primary industries per sales_id. for example If I have one sales_id with two primary industries I need to get two rows for that.

I am using a case statement but instead of two rows I am getting three rows one with a null row

SELECT sales.id as sales_id, a.id as app_id, 
( CASE WHEN cl.industry_id IS NULL THEN cl.industry END ) AS primary_industry
FROM sales 
LEFT JOIN classification_jobs cj on cj.jobref = sales.id 
LEFT JOIN sales_industry cl on cl.id = cj.label_id 
WHERE sales.id in (31271403) 
GROUP BY sales_id, app_id, primary_industry

when trying above code I am getting three rows with two rows with two primary industries and the third one with a null primary industry value. but I want exclude the null value row. I can’t use having priamry_industry is not null option as we have data with sales.id with out industry values at all and if I use having priamry_industry is not null then it will exclude the ids with out industries.

I tried creating subquery as a column but I am getting the following error reference primary_industry not supported (forward reference in items list) Please help me out in solving the issue

Thanks

sharepoint online – Calculated Column with multiple If statement

I would like to have a column in which the date is calculated from the values in three other columns.
I have one column named “Last Test Date”, a second column called “Type”, a third column called “Insulation” and the forth column called “Next Test Date.

I need the “Next Last Date” column to be calculated so that it updates automatically when the “Last Test Date” date is changed, and basis the output value on “Type” + “Insulation” + “Last Test Date” values.

e.g.

  • if “Last Test Date” equals 1/1/2021 and “Type” equals Winch, then “Next Date Test” equals 1/4/2021.(3 monthly)
  • if “Last Test Date” equals 1/1/2021 and “Type” equals Cage, then “Next Date Test” equals 1/6/2021. (6 monthly)
  • if “Last Test Date” equals 1/1/2021, “Type” equals Winch, and Insulation<=500 then “Next Date Test” equals 31/1/2021. (monthly)
  • if “Last Test Date” equals 1/1/2021, “Type” equals Cage, and Insulation <=500 then “Next Date Test” equals 1/4/2021. (3 monthly)

I tried with below but the last two conditions it seems not reflected

=IF(Type="Winch",(Last Test Date)+90,IF(Type="Cage",(Last Test Date)+180,IF(AND(Type="Winch",(Insulation (MΩ))<=500),(Last Test Date)+30,IF(AND(Type="Cage",(Insulation (MΩ))<=500),(Last Test Date)+90,))))

Any assistance is greatly appreciated.

Evidence of Bank statement

My aunt opened a bank account on my name in England since May 2019 with 3500pounds. Can I use it as my bank statement. Please I need answers.

turing machines – Explain the simulation of an if else statement in LOOP

I’m learning about LOOP program and in my book I have the following command which is simulated by a LOOP program:

$$text{if} x_i = 0 text{then} text{P1} text{else} text{P2} text{end}$$

This is simulated in LOOP as:
begin{align}
&x_n := 1; \
& x_{n+1} := 1; \
&text{loop} x_i text{do} x_n := x_n -1 text{end}; \
&text{loop} x_n text{do} x_{n+1} := x_{n+1} -1 text{end}; \
&text{loop} x_n text{do} text{P1} text{end}; \
&text{loop} x_{n+1} text{do} text{P2} text{end}; \
&x_n := 0; \
& x_{n+1} := 0; \
end{align}

There is no commentary for the code and I’m not sure how this even simulates the original if statement.

postgresql – Undo INSERT in WITH statement ON CONFLICT in Postgres

In my Postgres 13 database I have a master table suggestions and multiple child tables with different sets of columns, two of them are movie_edit_suggestions and series_edit_suggestions.

The id of the child tables are foreign keys to the id of the master table and ids are unique across all child tables, i.e. id “1” can’t exist in both movie_edit_suggestions and release_edit_suggestions.

In these child tables there are a pair of columns that must be unique within each table:

author integer NOT NULL,
field text NOT NULL

These tables also has the value text NOT NULL column. If the same author tries to do two inserts with the same field then the latest value should update the existing one.

CREATE TYPE suggestion_type AS ENUM (
    'change_movie',
    'change_series'
);

CREATE TABLE suggestions (
    id SERIAL PRIMARY KEY,
    suggestion_type suggestion_type NOT NULL,
    author integer NOT NULL
);


ALTER TABLE ONLY suggestions
    ADD CONSTRAINT suggestions_id_suggestion_type_author_key UNIQUE (id, suggestion_type, author);


CREATE TABLE movie_edit_suggestions (
    id SERIAL PRIMARY KEY,
    author integer NOT NULL,
    field text NOT NULL,
    value text NOT NULL,
    suggestion_type suggestion_type DEFAULT 'change_movie'::suggestion_type,
    CONSTRAINT movie_edit_suggestions_suggestion_type_check CHECK ((suggestion_type = 'change_movie'::suggestion_type))
);

CREATE UNIQUE INDEX unique_movie_edit_suggestions ON movie_edit_suggestions USING btree (field, author);

ALTER TABLE ONLY movie_edit_suggestions
    ADD CONSTRAINT movie_edit_suggestions_id_suggestion_type_author_fkey FOREIGN KEY (id, suggestion_type, author) REFERENCES suggestions(id, suggestion_type, author) ON DELETE CASCADE;


CREATE TABLE series_edit_suggestions (
    id SERIAL PRIMARY KEY,
    author integer NOT NULL,
    field text NOT NULL,
    value text NOT NULL,
    suggestion_type suggestion_type DEFAULT 'change_series'::suggestion_type,
    CONSTRAINT series_edit_suggestions_suggestion_type_check CHECK ((suggestion_type = 'change_series'::suggestion_type))
);

CREATE UNIQUE INDEX unique_series_edit_suggestions ON series_edit_suggestions USING btree (field, author);

ALTER TABLE ONLY series_edit_suggestions
    ADD CONSTRAINT series_edit_suggestions_id_suggestion_type_author_fkey FOREIGN KEY (id, suggestion_type, author) REFERENCES suggestions(id, suggestion_type, author) ON DELETE CASCADE;

I’m trying to do this with CTE’s but by first attempt results in duplicate suggestions entries:

WITH suggestion AS (
    INSERT INTO suggestions (suggestion_type, author) VALUES ('change_movie', 1)
    RETURNING id
)
INSERT INTO movie_edit_suggestions
(id, suggestion_type, field, value, author)
VALUES
((SELECT id FROM suggestion), 'change_movie', 'title', 'Star Wars', 1)
ON CONFLICT (field, author) DO UPDATE SET value = 'Star Wars';

-- Another one

WITH suggestion AS (
    INSERT INTO suggestions (suggestion_type, author) VALUES ('change_movie', 1)
    RETURNING id
)
INSERT INTO movie_edit_suggestions
(id, suggestion_type, field, value, author)
VALUES
((SELECT id FROM suggestion), 'change_movie', 'title', 'Star Wars: New Hope', 1)
ON CONFLICT (field, author) DO UPDATE SET value = 'Star Wars: New Hope'

SELECT * FROM movie_edit_suggestions
id author field value suggestion_type
1 1 title Star Wars: New Hope change_movie 
SELECT * FROM suggestions
id suggestion_type author
1 change_movie 1
2 change_movie 1

Swapping the inserts results in an error when inserting to both child tables:

WITH movie_edit_suggestion AS (
    INSERT INTO movie_edit_suggestions
    (suggestion_type, field, value, author)
    VALUES
    ('change_movie', 'title', 'Star Wars', 1)
    ON CONFLICT (field, author) DO UPDATE SET value = 'Star Wars'
    RETURNING
    id, field, value
)

INSERT INTO suggestions (id, suggestion_type, author)
VALUES ((SELECT id FROM movie_edit_suggestion), 'change_movie', 1)

-- Another one

WITH series_edit_suggestion AS (
    INSERT INTO series_edit_suggestions
    (suggestion_type, field, value, author)
    VALUES
    ('change_series', 'title', 'Breaking Bad', 1)
    ON CONFLICT (field, author) DO UPDATE SET value = 'Breaking Bad'
    RETURNING
    id, field, value
)

INSERT INTO suggestions (id, suggestion_type, author)
VALUES ((SELECT id FROM series_edit_suggestion), 'change_series', 1)

ERROR: duplicate key value violates unique constraint “suggestions_pkey”
DETAIL: Key (id)=(1) already exists.

Full dbfiddle showcasing both attempts

f# – How to apply multiple filters in one statement

Nope, that’s pretty much the way.

If you really, really want to make this shorter and less cluttered, and if you face similar situations multiple times in your codebase, you could make yourself a special operator for combining predicates:

let (.&&.) f g c = f c && g c

Then you can use this operator in your filter:

|> Seq.filter (needSpecialDocument .&&. wantToMoveTo)

But base on my own experience I wouldn’t advise this. You make the program slicker and cooler looking, but you’re losing some readability. Now whoever reads your program will have to look up the meaning of .&&.. Programs are read more often than they are written.

triggers – DML statement to add multiple accounts in single step

I want to add multiple accounts to the salesforce object from the anonymous window. I know how to do that using the below code

 Account acc = new Account(Name='account1');
 List<Account> accToAdd = new List<Account>();
 accToAdd.add(acc);
 insert accToAdd;

but when I am trying to insert multiple accounts(see code below), it is giving me error as “Line: 1, Column: 5
Unexpected token ‘<‘.”

List<Account> accToAdd = new List<Account>(
 { new Account(Name='triggertest4'),
   new Account(Name='triggertest5'),
   new Account(Name='triggertest3')
 });

insert accToAdd;

can anyone help???

IF STATEMENT REVIEW

  1. What will be printed by the following code segment?
    x=15
    if x==25:
    print (‘Pizza is yummy’)
    else:
    print (‘My teacher is awesome’)
    If x=15 , then double of 15 is x=25

  2. What will be printed by the following code segment?
    x=35

y=52

if x!=25 and y==52:

print (‘Pizza is yummy’)

else:

(‘My teacher is awesome’)

  1. Correct the errors in the following code to find the least number between x, y and z.
    if x > y or y <=z
    print (y)
    elif: y>z
    if x>z:
    print(z)
    else print (“x”)

  2. Consider the following code:
    if a > b:
    if b > c:
    print("Spam Please")
    else:
    print("It’s a late parrot!")
    elif b > c:
    print("Cheese Shoppe")
    if a >= c:
    print("Cheddar")
    elif a < b:
    print("Gouda")
    elif c == b:
    print("Swiss")
    else:
    print("Trees")
    if a == b:
    print("Chestnut")
    else:
    print("Larch")
    print("Done")
    What would the output be for each of the following inputs? (assume they are in order a, b, c)
    a) 3, 4, 5
    b) 3, 3, 3
    c) 5, 4, 3
    d) 3, 5, 2
    e) 5, 4, 7
    f) 3, 3, 2

Programming Exercises
5. Given numbers (a, b, c) determine which number is the smallest, assign it to min, find the largest, assign it to max, and find the middle number and assign it to mid.

Paste code here.

  1.  A certain teacher gives 100 point exams that are graded on a scale from 0-100. Each grade can be given as a letter grade (90-100 = A, 80-89 = B,70-79 = C, 60-69 = D, <60 = F) . Write a function that will accept a value of a number grade, and will print the letter grade that corresponds to the number range.

sharepoint rest api – Query string prohibited depending on the statement

The problem

I have a List with loads of rows and I’m doing a workaround in Python to fetch them without exceding the list view threshold. So far I managed to do this by limiting the ID range of the results, like so:

<...>/_api/Web/lists/GetByTitle('<ListName>')/Items?$select=ID&$top=5000&$orderby=ID&$filter=ID ge 1 and ID le 5000

And then I change the numbers of ‘ID’ column from 1~5000 to 5001~10000, and so on…

Some Queries

So this query here works fine:

<...>/_api/Web/lists/GetByTitle('<ListName>')/Items?$select=*&$top=5000&$orderby=ID&$filter=ID ge 20001 and ID le 25000 and TextField eq 'Some text'

And I manage to get 5000 rows from each request made like this (even though there are more than 5000 rows with ‘Some text’ inside the ‘TextField’ column).

But if I try this one here:

<...>/_api/Web/lists/GetByTitle('<ListName>')/Items?$select=*&$top=5000&$orderby=ID&$filter=ID ge 20001 and ID le 25000 and DateField gt '2020-09-12T00:00:00'

It raises me the limit exception. And if I change the date to a day that I know it’ll rerturn less then 5000 rows, it runs just fine.

In Sum

I’m limiting the range with the ID column of SharePoint list so it doesn’t exceed the view threshold, but it doesn’t seem consistent.

This is the template i’m using, adding things at the end, and only changing the range until the list is over:

<...>$filter=ID ge 1 and ID le 5000 and <other query options...>

Any ideas on why SharePoint thinks I’m trying to fetch more than 5000 rows at once? Thanks in advance!

Convert an IF statement in Mixed Integer Programming

I want to convert an IF statement for my optimization problem. I want to minimize the total price. I want 800 tones of salt and 3 suppliers offer me their prices.

Supplier $1$ offers me $100$ tones at $\$150$ a tone.

Supplier $2$ offers me $400$ tones at $\$170$ a tone, if I take $300$ tones (or more)the price drops to $\$150$.

Supplier $3$ offers me $1600$ tones at $\$170$ a tone.

Here is my model without the if statement :

$x_i$ is an integer variable representing the quantity bought to the supplier $i$,
$p_i$ is the price of the supplier $i$, $c_i$ is the maximal capacity offered by the supplier $i$.

$Min( p_1times x_1 + p_2times x_2 + p_3times x_3)$

$s.t. x_1 + x_2 + x_3 = 800$

$ x_1 leq 100$

$ x_2 leq 400$

$ x_3 leq 1600$

Now I want to use the Big M method the convert my if statement but I don’t know how to do it.
I tried this method : Express a “complex” IF-Statement to Linear Programming to find the constraints but I don’t know how to modify my prices. I tried this :

If $x_2 geq 300$, Then $p_2 = 150$, Else $p_2 = 170$.

i.e.

If $x_2 geq 300$, Then $b = 1$, Else $b = 0$ and $p_2 = 150b + 170(1-b)$

In addition to this I code in python the the mip library, do you know a better library to do this?

Thanks a lot !