sql server 2016 – Multiple condition case when query

I am writing a report that will display what versions of a particular software is compatible with which versions of Windows 10. I have matrix that was give to me by the vendor. Below is part of my query.

In the first case when I am linking the Windows version with the build number to make the results easier to read.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblOperatingsystem.Version As Build,
Case
When tblOperatingsystem.Version Like ‘10.0.10240’ Then ‘1507’
When tblOperatingsystem.Version Like ‘10.0.10586’ Then ‘1511’
When tblOperatingsystem.Version Like ‘10.0.14393’ Then ‘1607’
When tblOperatingsystem.Version Like ‘10.0.15063’ Then ‘1703’
When tblOperatingsystem.Version Like ‘10.0.16299’ Then ‘1709’
When tblOperatingsystem.Version Like ‘10.0.17134’ Then ‘1803’
When tblOperatingsystem.Version Like ‘10.0.17763’ Then ‘1809’
When tblOperatingsystem.Version Like ‘10.0.18362’ Then ‘1903’
When tblOperatingsystem.Version Like ‘10.0.18363’ Then ‘1909’
When tblOperatingsystem.Version Like ‘10.0.19041’ Then ‘2004’
End version,
Case
When tblOperatingsystem.Version Like ‘1607’ And
tblSoftware.softwareVersion Like ‘5.4.%’ Or
tblSoftware.softwareVersion Like ‘5.3.%’ Or
tblSoftware.softwareVersion Like ‘5.2.%’ Or
tblSoftware.softwareVersion Like ‘5.1.%’ Or
tblSoftware.softwareVersion Like ‘5.0.%’ Or
tblSoftware.softwareVersion Like ‘4.1.%’ Or
tblSoftware.softwareVersion Like ‘4.9.%’ Or
tblSoftware.softwareVersion Like ‘4.8.%’ Or
tblSoftware.softwareVersion Like ‘4.7.%’ Or
tblSoftware.softwareVersion Like ‘4.6.%’ Then ‘Not Compatable’
End compat3,
Case
When tblOperatingsystem.Version Like ‘1809’ And
tblSoftware.softwareVersion Like ‘5.4.%’ Or
tblSoftware.softwareVersion Like ‘4.8.%’ Or
tblSoftware.softwareVersion Like ‘4.7.%’ Or
tblSoftware.softwareVersion Like ‘4.6.%’ Or
tblSoftware.softwareVersion Like ‘4.5.%’ Or
tblSoftware.softwareVersion Like ‘4.4.%’ Then ‘Not Compatable’
End compat2,
tblSoftware.softwareVersion,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblSoftwareUni.softwareName = ‘vmware horizon client’ And
tblOperatingsystem.Caption = ‘Microsoft Windows 10 Pro’ And
tblAssetCustom.State = 1

For some reason the second case statement is putting not compatible on the Windows 1809 as well as 1607. The first case should not even be concerned with 1809. Any ideas?

database design – The right way to model multiple FACTs schema

Background

I’m in a process of designing a database (using a STAR schema).

There are three tables to model: products, tests, states.

The database will be used to store results of tests conducted on products (in a great simplification). There can be many tests pointing out to a single product but each test is unique (they are not shared among products). Besides, I need to record the current state of the product, at the time when the test was conducted. Let’s assume that the state of a product describes its current location and owner, which are changing very often. That will most likely involve SCD lvl 2 – to track the history of state changes and to be able to locate a product with all its tests as well as the states it had during these tests.

Problem

I’m not entirely sure how to model this problem. It seems obvious to store every test in a FACT table. This table would then consist of thousands of transactions. On the other hand, there will also be hundreds (and later thousands) of products, so I should probably keep them in a second FACT table. Then, there will also be thousands of state changes, so in order to record their entire history, I would need to keep them in a … FACT table as well? I’ve been told that FACT tables are typically used to store multiple-rows data but on the other hand where are the DIMs in this model?

I also don’t know how to model the relationships between these tables. Productsstates is a 1:* relationship. Productstests is a 1:* as well. Finally, statestests is a 1:* too. I would then go with linking products to states and then states to tests (products 1<-* states 1<-* tests), what would allow me to find all states for a particular product and all tests (in all states or in a selected state). What do you think about that? The problem here is that, as I keep adding states, I have two options: either keep duplicating products in the products table (with added “recorded_timestamp” column) or use a SCD lvl 2 in states table, pointing out to the products table with a FK, but this would effectively make the product table a DIM!

Any help here would be very appreciated.

C# Condensing Multiple single if statements

I have this piece of code in my service layer and registers and student for a course.
As you can see if have alot of if statements and i was wondering if any could suggest a nicer way of writing this code?

    public async Task<bool> RegisterStudentForCourseAsync(RegisterStudentForCourseRequest registerStudent)
    {
        var student = await _studentRepository.GetStudentInformationAsync(registerStudent.StudentId);

        if (student == null)
        {
            throw new StudentCourseRegistrationException("Student Not Found");
        }
       
        var alreadyRegistered = student.Enrollments.Any(x => x.CourseId == registerStudent.CourseId);

        if (alreadyRegistered)
        {
            throw new StudentCourseRegistrationException("Student is Already Registered for Course");
        }
       
        //change this to a config setting
        if (student.SubjectCount >= 5)
        {
            throw new StudentCourseRegistrationException("Cannot Register Amount of Courses");
        }

        var course = _courseRepository.GetCourseInformation(registerStudent.CourseId);

        if(course == null)
        {
            throw new StudentCourseRegistrationException("Course Not Found");
        }
        
        if(course.Enrolled >= course.Capacity)
        {
            throw new StudentCourseRegistrationException("Course is fully Booked");
        }

        return await _courseRepository.SaveRegisterationOfStudentForCourse(course, registerStudent, student);
    }
}

stored procedures – Update multiple columns based on percentage wise calculation in Oracle

I have a stored procedure which gives me the total data. Now based on ration of 80% and 20% I have to update 2 columns. i.e. 80% to UG_LENGTH and 20% to AR_length of total NE_length

So here is what I tried but it is giving multiple errors

CREATE OR REPLACE PROCEDURE UPD_UG_AR_BY_CMM AS 
   BEGIN


    for cur_r in (
    select circle, 
                   regexp_substr(MP,'(^/)+',1,1)MAINTENANCE_ZONE_NAME,
                   regexp_substr(MP,'(^/)+',1,2)MAINTENANCE_ZONE_CODE,
                   SPAN_TYPE,
                   SPAN_LINK_ID,
                   NE_LENGTH,
                   ROUTE_APPROVED_BY_CMM
                   from TBL_FIBER_INV_CMP_REPORT_MV    
                  where CMM_APPROVED_DATE IS NOT NULL 
                  OR ROUTE_APPROVED_BY_CMM > 0 
                  OR JOB_PROGRESS_FLAG = 1
        )
        
  LOOP

        IF cur_r.ROUTE_APPROVED_BY_CMM <  SELECT ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH,
                                           ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS UG_LENGTH
                                           ,ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY LIKE '%AERIAL%' THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS AR_LENGTH
                                           FROM NE.MV_SPAN@DB_LINK_NE_VIEWER
        THEN
        
          BEGIN
              UPDATE TBL_FIBER_INV_SIGN_OFF_SHEET 
              SET  FSA_UG = UG_LENGTH,  --- divide 80% of NE_LENGTH
                   FSA_AERIAL = AR_LENGTH    --- divide 20% of NE_LENGTH
              WHERE CUR_R.SPAN_LINK_ID =  RJ_SPAN_ID
                
          END
        
        

    NULL;
  END UPD_UG_AR_BY_CMM;

In the above procedure I have used cursor to do my logic. But I am unable to divide it based on percentage wise ratio and there are some loop cursor error also.

Erros are

Error(21,47): PLS-00103: Encountered the symbol “SELECT” when expecting one of the following: ( – + all case mod new null continue any avg count current max min prior some sql stddev sum variance execute forall merge time timestamp interval date pipe <an altern

Error(25,13): PLS-00103: Encountered the symbol “THEN” when expecting one of the following: . ) , @ with group having intersect minus partition start subpartition union where connect sample The symbol “)” was substituted for “THEN” to continue.

I am using Oracle for my DB work

UPDATE

I ran the below query for getting NE_length, UG_length and AR_length. also attached is the sample data output of the query

IMG

So what my first requirement is, I want 80% to be of NE_length to be updated in UG_length and 20% to be updated in AR_length

Hope it clears

Multiple users on Samsung Galaxy A10

I want to be able to have two separate logins on my Galaxy A10 android phone, which require different passwords to access. Is this possible?

Sorting layer issue with multiple sprites and a Tilemap in Unity

I have a particle system that draws a blood splat. I have a Tilemap that represents terrain. However, the blood splats, which are on a sorting layer in front of the terrain, are somehow rendered both in front of and behind the terrain.

Example

I have encountered similar problems before where the particles had some different Z value than the terrain, but that is not the case this time (3D view):

enter image description here

Here are the Inspector panels for these objects.

First terrain:

enter image description here

The renderer for the upper (in Y) blood particle system:

enter image description here

And for the lower (in Y) system:

enter image description here

And finally my sorting layers, where Base is behind Foreground:

enter image description here

What might cause this behavior and how can I fix it?

Edit: Here is the entire BloodSplat prefab:

enter image description here

Can phone_home module be used multiple times in cloud init?

The cloud-init in ubuntu (and other distros) have:
(https://cloudinit.readthedocs.io/en/latest/topics/modules.html#phone-home)

phone_home:
url: http://example.com/$INSTANCE_ID/
post:
    - pub_key_dsa
    - instance_id
    - fqdn
tries: 10

Is it possible to call multiple hosts (urls with this)?

where to put multiple url spintax code ?

i want to use spintax code with multiple urls , the spintax code is not locked to a specific url but is to be used with all of the urls
the spintax code is partial match
as an example car insurance new york
pet insurance houston
with generic text added on
i tried putting the code into the “generic” text file using the “edit” button, but didnt realise it is “global” & so you have insurance terms for all your other projects
in the “url” box at top i tried using that but it wont take the code, the code im using at the moment in the url box is
url#{car insurance miami|another car insurance}
2ndurl#{2ndcar insurance miami|2ndanother car insurance}
(this is working ok)
i tried using the “anchor text” field & then saw it says “this is ignored if you use an anchor text in the url field”
which i am using (above)
i tried putting it in “partial match anchor text” & it cuts off the code after a short amount of words.
ive tried “secondary” “branding” & “lsi” boxes below that to put the text in & they all cut the text off & show the error message when you close the box because it has cut off the text & so the code has errors in it.
so the code is not binding anchors to url, if it was there would be even more code, it is generic with keywords (partial match) for all urls, but i cant see where to put it.

finder – How to reduce file size of multiple pictures in Photos app?

I have some pictures in Photos that I simply don’t need in the maximum quality my phone camera took them in. They’re usually about 2-4MB when ~200KB would suffice. I am looking for the best way to reduce the file size of these pictures when they are already in the Photos app.

I tried using the “Edit with Preview” options in Photos and then use “Reduce Size” in Preview, but apparently that is saved as a separate file and has no effect on the original image. And I haven’t found a way to ‘apply’ the edits to replace the original file.

Technically I could find the image in the Photos library in the Finder, but that is very time-consuming (and then I have to close Photos to edit the file). I’m not even aware of a way to quickly get from an image in the Photos overview to the file in the finder.

Any ideas?

Best way to use variables in multiple functions?

So I am running into issues in Query Monitor – duplicate queries. The reason being that I have functions that need to get say, a customer’s order status or the like.

What is the cleanest, correct way to ensure I only make that query once, but can use the results of that query in another function?

Sorry, I know its a very noob question, I just want to start cleaning up code and do things properly.