OpsCenter Custom SQL Query (Help Needed)
I need help writing a query that I assume needs a join or inner join that I cannot figure out, and my SQL knowledge is to be desired. So here is what I am trying to do...First the tables and columns.
Tables and Columns that have the data I need.
---------------------------------------------
domain_client
Column "Name"
domain_Policy
Column "name"
domain_JobArchive
Column "startTime" & "endTime" & "throughput" & "State" "statusCode"
nb_VirtualMachines
Column "operatingSystem" & "name"
What I am trying to do
---------------------------------------------
I need a SQL query that takes the "name" column value from the table "domain_client" and use it to select values in the "name" column in the table "nb_VirtualMachines" that contains the Operating System Value in column "operatingSystem" and combine its out put from "name" & "operatingSystem" with the select values from Column "name" in table "domain_Policy" and Column "startTime" & "endTime" & "throughput" & "State" from table "domain_JobArchive"
I then need to apply
adjust_timestamp(domain_JobArchive.startTime,-14400000 ) as "Job Start Time",
adjust_timestamp(domain_JobArchive.endTime,-14400000 ) as "Job End Time",
for the two columns "startTime" & "endTime" from table domain_JobArchive.
And - and here is where I dont know if this is possible. I need to have the column "state" from Table "domain_JobArchive" if it has a value of 3 and a value of 0 or 1 in column "statusCode" in the same table it show a value of 0 or 1. 0 being = to Successful, and 1 being = to Partial.
My failed attempt at this was
(case when domain_JobArchive.state =3 and domain_JobArchive.statusCode in (0,1) then 0 else 2 end) as "Job Status (Complete/Partial/Failed)
The output would in the end would be something like
Policy Name | VM Name | OS Type | Job Start Time | Durration (Min) | Job End Eimte | Throughput | Size (GB)| Job Status
Some_Policy | Somehos | RHEL6 | Oct 11 2142AM | 30 | Oct 11 2142PM | 192MB/s | 17 |Successful
Some_Policy | Somehos | Win7 | Oct 11 2112AM | 7 | Oct 11 2113PM | 102MB/s | 7 |Failed
Any help would be welcome. Attached is one of my failed SQL Query attempts
You're getting nonsense results because you didnt completely join all the tables, letting sybase figure it out.
All of the tables mentioned in your from clause should appear in your where clause (since you're doing your joins that way)
So for example you should probably add -
... from domain_client, nb_VirtualMachines, domain_JobArchive , domain_Policy , domain_MasterServer , domain_Client where nb_VirtualMachines.name=domain_client.name AND domain_JobArchive.clientName = domain_Client.name AND domain_JobArchive.policyName = domain_Policy.name AND domain_jobArchive.masterServerID = domain_MasterServer.id