Stephenmanuelmelissa's Profile

55
Points

Questions
9

Answers
16

  • Asked on September 1, 2020 in Sql.

    It is best to avoid case expressions in the where clause. One reason is that they are easy to avoid by using simpler boolean constructs:

    Where (@IsSold = 'False' and P.PlotID NOT IN (Select PlotID from SaleContract)) or       (@IsSold = 'True' and P.PlotID IN (Select PlotID from SaleContract)) 

    Note that SQL Server does not support a boolean type, so a boolean expression cannot be returned from a case.

    • 10 views
    • 1 answers
    • 0 votes
  • You are only inserting two columns in the @T:

    insert into @T (ID, rownum)     select ID, ROW_NUMBER() OVER (order by id)      from testtabel4; 

    You are not inserting name so it is NULL on all rows. Hence, the then part of the case expression will always be NULL.

    • 16 views
    • 2 answers
    • 0 votes
  • Asked on September 1, 2020 in Sql.

    You need to include the column in the subquery:

    SELECT o.sale_nag, i.purchi,        COALESCE(i.sale_nag,0) + i.sale_nag   result FROM stockin_details i LEFT JOIN       (SELECT purchi, SUM(sale_nag) AS snag       FROM stockout_details       WHERE purchi = 'a12'       GROUP BY marka, chalan, room, rack, purchi      ) o      ON i.purchi = o.purchi WHERE i.purchi = 'a12' GROUP BY i.marka, i.chalan, i.room, i.rack; 

    Note that I replaced the table aliases with more manageable aliases and qualified all column names.

    That said, I’d be surprised if the query actually did anything useful — the result is only using values from one table.

    Also, I would expect the join conditions to use all the columns in the aggregation. If you want further help with the query, ask a new question. Provide sample data, desired results, an explanation of what the code should do, and an appropriate database tag.

    • 19 views
    • 2 answers
    • 0 votes
  • Asked on September 1, 2020 in Mysql.

    left join with table contact works better here . just try this if you want to select all data from contacts.

    SELECT contact.* FROM contact LEFT JOIN enterprise ON contact.enterprise_id = enterprise.id WHERE contact.created_on > 01012010 00:00:00  AND enterprise.created_on > 01012010 00:00:00 
    • 8 views
    • 3 answers
    • 0 votes
  • Asked on September 1, 2020 in Mysql.

    First of all, your function:

    function convert($num_likes) {     $num_likes = $number / 1000;     return $num_likes . 'k'; } 

    will not work as expected, because it converts to the opposite way 🙂 Here is updated version:

    function convert($num_likes) {     $number = $num_likes / 1000;     return $number . 'k'; } 

    Second point. You should use the function somewhere… for example your line (actually only a part of it):

    <p> Total Likes: " . $num_likes ."</p> 

    must be:

    <p> Total Likes: " . convert($num_likes) ."</p> 

    And finally, using this answer we can modify convert function to this:

    function convert($n) {     if ($n < 1000) {         $n_format = number_format($n);     } else if ($n < 1000000) {         // Anything less than a million         $n_format = number_format($n / 1000, 3) . 'k';     } else if ($n < 1000000000) {         // Anything less than a billion         $n_format = number_format($n / 1000000, 3) . 'M';     } else {         // At least a billion         $n_format = number_format($n / 1000000000, 3) . 'B';     }     return $n_format; } 

    Now we can convert all numbers up to billions.

    Playground: click.

    • 14 views
    • 3 answers
    • 0 votes
  • Asked on September 1, 2020 in Mysql.

    In C++, a derived class object can be assigned to a base class object, but the other way is not possible.

    class Base { int x, y; };  class Derived : public Base { int z, w; };  int main()  {     Derived d;     Base b = d; // Object Slicing,  z and w of d are sliced off } 

    Object slicing happens when a derived class object is assigned to a base class object, additional attributes of a derived class object are sliced off to form the base class object.

    • 19 views
    • 17 answers
    • 0 votes
  • If I follow you correctly, you can use conditional aggregation:

    select      min(measureID),      terraID,      pinID,      time,      max(case when temperature = 1 then value end) value,     max(case when temperature = 0 then value end) humidity from mytable group by terraID, pinID, time order by min(measureID) 

    If needed, you can easily turn this to an insert query (using insert ... select ...) or even to a create table statement (create table newtable as ...).

    Demo on DB Fiddle:

     measureID | terraID | pinID | time                |  value | humidity --------: | ------: | ----: | :------------------ | -----: | -------:         2 |       1 |     9 | 2020-04-10 13:00:01 | 26.500 |   34.300         4 |       2 |    10 | 2020-04-10 13:00:01 | 32.900 |   35.100         6 |       1 |     9 | 2020-04-10 13:05:01 | 26.600 |   34.400         8 |       2 |    10 | 2020-04-10 13:05:01 | 33.000 |   35.000     38088 |       2 |    10 | 2020-08-31 12:50:02 | 35.000 |   35.900     38089 |       1 |    11 | 2020-08-31 12:50:02 | 25.187 |     null     38090 |       2 |    12 | 2020-08-31 12:50:02 | 28.312 |     null     38092 |       2 |    10 | 2020-08-31 12:55:01 | 35.000 |   35.800     38093 |       1 |    11 | 2020-08-31 12:55:01 | 25.250 |     null     38094 |       2 |    12 | 2020-08-31 12:55:01 | 28.375 |     null 
    • 11 views
    • 2 answers
    • 0 votes
  • Asked on September 1, 2020 in Mysql.

    You can’t have these SET statemments inside the CREATE VIEW statement. Also, since the order of evaluation of expressions in the select clause is undefined, your query might not even do what you want consistently.

    If you are running MySQL 8.0, I would recommend window functions instead of user-defined variables. I think the logic you want is:

    create view dashboard as select     t.*,     po - lag(po, 1, 0) over(order by bulan) as selisihpo,     forecast - lag(forecast, 1, 0) over(order by bulan) as selisihfc from view_t_dasboard t where tahun= '2020'  
    • 7 views
    • 1 answers
    • 0 votes
  • Asked on September 1, 2020 in Mysql.

    This worked for me. Change database, username and password accordingly.

    mysql -Nse 'show tables' -D DATABASE -uUSER -pPWD | while read table; do echo "SET FOREIGN_KEY_CHECKS = 0;drop table \`$table\`;SET FOREIGN_KEY_CHECKS = 1;"; done | mysql DATABASE -uUSER -pPWD 
    • 20 views
    • 26 answers
    • 0 votes
  • Asked on September 1, 2020 in iOS.

    After further testing…

    1. cellForItemAt indexPath will be called only for cells which were not on screen but are now on screen as a result of the invalidateLayout().
    2. This appears to be true
    3. See answer to question 1. This could cause an issue if you have moved cells around and the data in each cell is not in the same order as the data source.
    4. I am now using reloadData() a lot less

    In general, it looks like reloadData() also calls invalidateLayout() (or at least has a similar effect) and will trigger cellForItemAt indexPath for every cell. invalidateLayout(), however, will only call cellForItemAt indexdPath if the cell is now within the screen bounds and needs to be reloaded as a result of the layout change.

    • 8 views
    • 1 answers
    • 0 votes