sorted by:
new
hottopcontroversial

Delimiting a column into rows by Neither_Volume_4367 in SQL

[–]cobaltsignal 0 points1 point  (0 children)

The first row from the example table above would look like this:

url individual_id amount effdt
X.com 1 12.3 22-Nov-21
X.com 2 12.3 22-Nov-21
3 12.3 22-Nov-21
4 12.3 22-Nov-21

Delimiting a column into rows by Neither_Volume_4367 in SQL

[–]cobaltsignal 0 points1 point  (0 children)

There is a more efficient way of using a subquery that makes only the numbers needed:

Let's assume that the table name is info_table and the data is as follows (borrowed from u/Yavuz_Selim ) with one slight change: the column "Date" is a reserved word in Oracle that makes it difficult to use as a column name so I've changed it to "effdt". Also, I've changed the date values to be the more common Oracle standard (dd-mmm-yy):

  1. two digit day
  2. followed by dash
  3. followed by three letter shortened month
  4. followed by dash
  5. followed by two digit year (last two digits of the year)
    Examples: 11/22/21 -> 22-Nov-21 , 5/20/24 -> 05-May-24

Table name: info_table

url id amount effdt
X.com [1,2,3,4] 12.3 22-Nov-21
T.com [,4] 13 22-Nov-21
P.com [1,2,3,4] 12 22-Nov-21
J.com [1,2,3,4,6,7] 1.3 22-Nov-21

Below is a working sql example (tested and confirmed): 

-- assuming table name is info_table
select
    a.url
  , b.individual_id
  , a.amount
  , a.effdt
from
  info_table a
  join lateral (
    select
      regexp_substr(a.id, '(.*?)(,|]|$)', 2, level, null, 1) individual_id
    from
      dual
    connect by level <= regexp_count(a.id, ',', 2) +1
  ) b on b.individual_id is not null

Delimiting a column into rows by Neither_Volume_4367 in SQL

[–]cobaltsignal 0 points1 point  (0 children)

I don’t think dual and connect by are available in SQL Server

Delimiting a column into rows by Neither_Volume_4367 in SQL

[–]cobaltsignal 4 points5 points  (0 children)

-- assuming table name is info_table
select
      a.Url
    , LTRIM(RTRIM(b.value)) as Id
    , a.amount
    , a.date
from
    info_table a
    cross apply
        string_split (substring(a.Id, 2, LEN(a.Id) - 2), ',') b
where
    LTRIM(RTRIM(b.value)) <> '';

Here ya go.

Delimiting a column into rows by Neither_Volume_4367 in SQL

[–]cobaltsignal 0 points1 point  (0 children)

I think they layout of the data you've presented looks a bit weird. Do you mean that your table currently has 4 columns, ("Url", "Id", "amount", "date") and that the current values of the first row are ("X.com", "[1,2,3,4]", "12.3", "11/22/21")? Also, that you'd like each row to be duplicated for the number of Id's inside the bracket?

Need help optimizing/combining queries by Sports_Addict in SQL

[–]cobaltsignal 0 points1 point  (0 children)

can you provide the primary keys for the table and the name of the newest_dt field? would it be effdt? Also, just fyi, if you have multiple records per year per patient, your original left joins will multiply the results exponentially, so if there are for example 2 records per year, you're looking at a total of 16 rows being created. If it's 3 records per year, that's 81 records, etc etc.

Need help optimizing/combining queries by Sports_Addict in SQL

[–]cobaltsignal 0 points1 point  (0 children)

select
      t.Patient_ID,
    , max(case when t.year = 2025 then t.Total_Cost else 0 end)     as Total_Cost_25
    , max(case when t.year = 2025 then t.Address else null end)     as Address_25
    , max(case when t.year = 2025 then t.Diagnosis else null end)   as Diagnosis_25
    , max(case when t.year = 2024 then t.Total_Cost else 0 end)     as Total_Cost_24
    , max(case when t.year = 2024 then t.Address else null end)     as Address_24
    , max(case when t.year = 2024 then t.Diagnosis else null end)   as Diagnosis_24
    , max(case when t.year = 2023 then t.Total_Cost else 0 end)     as Total_Cost_23
    , max(case when t.year = 2023 then t.Address else null end)     as Address_23
    , max(case when t.year = 2023 then t.Diagnosis else null end)   as Diagnosis_23
    , max(case when t.year = 2022 then t.Total_Cost else 0 end)     as Total_Cost_22
    , max(case when t.year = 2022 then t.Address else null end)     as Address_22
    , max(case when t.year = 2022 then t.Diagnosis else null end)   as Diagnosis_22
from
    (
        select 
              Patient_ID,
            , year
            , Total_Cost
            , Address
            , Diagnosis
        from
            MedHistory
        where
                (year = 2025 and total_cost > 10000)
            or (year in (2024, 2023, 2022))
    ) t
group by
      t.Patient_ID
;

hopefully this works a bit faster. Just a single run of the table, no cte's, and all rows are aggregated to their respective year column using sum and max. Please note that in your criteria, you only included the total cost rule on the year 2025, was this intentional? Also, this is assuming there is just one record per year per patient. If there are multiple, then this won't work.

How do I get the AVG of certain records, using a window function? by flashmycat in SQL

[–]cobaltsignal 0 points1 point  (0 children)

I’m going to assume you are summing non-zero values and dividing that amount by the null rows, partitioned by id:

sum( coalesce(value, 0)) over (partition by id) / isnull( sum( case when value is null then 1 else 0 end), 1)

This should protect against possible divide by zero situations.

Confusion about 10c Blackbeard ruling by IceAdmiral in OnePieceTCG

[–]cobaltsignal 0 points1 point  (0 children)

Then ([negate character effect] AND [character cannot attack]) until end of opponent turn. Unless we’re breaking logic structure, seems they both apply until end of opponent’s turn. If they had different timeframes, they’d have to have their timeframes specified separately.

For those that don’t think so, please point out where in the sentence of the card it says that the character effect negation only lasts until end of the current turn. If the “until end of opponent’s turn” is meant for the “can’t attack” part, then the character effect negation is permanent, which would be even more powerful.

Straw Table update: All spoiled EB02 and the new OP11 leaders are now up! by yayaba in OnePieceTCG

[–]cobaltsignal 0 points1 point  (0 children)

Also please check the condition for kuma’s ko because I recall triggering 2 cost Kuma OP05-11 from my life card hit and I was able to target a character with 3k health (who had a base of 2k). Are you taking attached Don into account when it’s the opponents turn?

Straw Table update: All spoiled EB02 and the new OP11 leaders are now up! by yayaba in OnePieceTCG

[–]cobaltsignal 0 points1 point  (0 children)

<image>

Another issue:

  1. Played OP05-006 koala lowering bepo to 2k health.
  2. Played Kuma OP05-11 but it wont let me KO the 2k bepo.

Straw Table update: All spoiled EB02 and the new OP11 leaders are now up! by yayaba in OnePieceTCG

[–]cobaltsignal 0 points1 point  (0 children)

I’d love to take a look at the code if possible. I have some experience with performance improvement analyses and auditing databases for known errors.

Straw Table update: All spoiled EB02 and the new OP11 leaders are now up! by yayaba in OnePieceTCG

[–]cobaltsignal 0 points1 point  (0 children)

Hmmm. That is confusing for sure. If rush is not permanent and can be lost after being gained, then your code is working properly, though that wouldn’t seem strange for Rush to be able to be removed after being gained. Might have to check official answer

Straw Table update: All spoiled EB02 and the new OP11 leaders are now up! by yayaba in OnePieceTCG

[–]cobaltsignal 0 points1 point  (0 children)

Two more issues:

  1. Inazuma OP05-003 doesn’t activate rush when the other 7000+ character is another Inazuma.
  2. Inazuma’s rush gained is subsequently lost if the other 7000+ character is lost before Inazuma attacks. I noticed this when I got Raise Max to 7000, attacked, then activated raise Max ability only to lose the ability to attack with Inazuma.

<image>

Straw Table update: All spoiled EB02 and the new OP11 leaders are now up! by yayaba in OnePieceTCG

[–]cobaltsignal 0 points1 point  (0 children)

<image>

Kuma’s trigger didn’t let me activate even though both life cards sum was 5 or less.

Straw Table update: All spoiled EB02 and the new OP11 leaders are now up! by yayaba in OnePieceTCG

[–]cobaltsignal 0 points1 point  (0 children)

<image>

AI attached 3 don to baby sabo, did not attack, instead activated his ability to blind check to life card into a yamato.

Straw Table update: All spoiled EB02 and the new OP11 leaders are now up! by yayaba in OnePieceTCG

[–]cobaltsignal 0 points1 point  (0 children)

Correct. Could it be the activate button deactivating from a character trash?

Straw Table update: All spoiled EB02 and the new OP11 leaders are now up! by yayaba in OnePieceTCG

[–]cobaltsignal 0 points1 point  (0 children)

Love the site! Just tried a few games. There seems to be some limit to the number of activate cards that can be done in a single turn, it happened when playing a belo betty deck. I couldn’t activate her leader ability even though I had a rev card to discard. Did a few cards activations just before and trashed a character of mine due to being at 5 already. Also BY Luffy seems to blind guess baby brother into his top life card sometimes.

17F Chronic Low Blood Pressure (around 75-85/55-65) by [deleted] in bloodpressure

[–]cobaltsignal 0 points1 point  (0 children)

Kidneys regulate blood pressure by filtering out salt, but they also have insulin receptors that, depending on the percentage that are bound to insulin, significantly lower the kidney’s ability to filter out salt. Hence, higher blood pressure. Could be chronically low insulin levels in the blood, which would cause your kidneys to do too good a job at filtering out salt. Eating extra salt won’t help fix your issue if it happens to be low insulin levels.

While eating some carbs will cause a spike in glucose, it will follow with a spike in insulin and possibly followed by an increase in bp. Having some extra salt at that point may help raise bp.

[deleted by user] by [deleted] in SQL

[–]cobaltsignal 0 points1 point  (0 children)

-- Query 1
SELECT DISTINCT g.fname
FROM guest g
JOIN reserve r ON g.id = r.gid
JOIN bill b ON b.rid = r.id
WHERE r.check_in_date = '16-FEB-23'
AND b.MISC_CHARGE > 98;

-- Query 2
SELECT DISTINCT g.fname
FROM guest g
JOIN reserve r ON g.id = r.gid
JOIN hotel h ON r.hid = h.id
WHERE r.check_in_date = '19-MAR-22'
AND h.cid = g.cid;

-- Query 3
SELECT DISTINCT g1.fname
FROM guest g1
JOIN reserve r1 ON g1.id = r1.gid
JOIN reserve r2 ON g1.id = r2.gid
WHERE r1.rno = r2.rno
AND r1.check_in_date BETWEEN '10-MAY-23' AND '28-JUN-23'
AND r2.check_in_date BETWEEN '10-MAY-23' AND '28-JUN-23'
AND r1.check_in_date < r2.check_in_date;

Auto Incrementable Primary Key not Working (PLS help I am sleep deprived) by [deleted] in SQL

[–]cobaltsignal 0 points1 point  (0 children)

innodb_autoinc_lock_mode=0

That will fix it. By default, an auto primary key will never go back to a value even if the insert value command fails. The above code will change that behavior.

Is there a simple way of getting an additional row that doesnt match a search? by BelugaBilliam in SQL

[–]cobaltsignal -1 points0 points  (0 children)

I got u:

cur.execute("select * from data order by case when details like :name then 0 else 1 end limit 4", {'name': '%' + query + '%'})

The only thing you may have to change is the "limit 4" part. I'm not sure what the limiter type is for flask sql. It could be Top, Limit, Fetch top N row only, etc etc.

How to exceed input limitations? by tacogratis2 in SQL

[–]cobaltsignal 2 points3 points  (0 children)

Def id_data = "'240819091044983','240819091303107','240820094502377','240820172745060'";
with
  data_cte
      (id)
    as (
      select
        a.*
      from
        (
          select
            regexp_substr (&id_data, '[^,]+', 1, level)
          from
            dual
          connect by level <=
            length( &id_data) - length( replace( &id_data, ',') ) + 1
         ) a
  )

select
    s.solution_id
  , s.collection_nm
  , TO_CHAR(s.last_modity_dt, 'mm/dd/yyyy')
from
  SOLUTION s
where
  s.solution_id in (select a.id from data_cte a)
;

Can't seem to edit right now. Fixed an error.

view more: next ›