SQL Help

[Oggy997]

Hey benders,

I'm trying to port a Business Objects query to an SQLPlus script (Oracle) -
I have built a table that pulls all the required data from the data warehouse (eqso01.svc_lvl) and it's aliased as tmp.

I am now creating some tables from this, which incorporates some of the calculations (variables) from business objects, and I've encountered what feels like Inception.... Hoping someone can make the SQL a little tidier.

I have 3 BO variables, two are done (the 3rd might be, but BO is down at the minute so I cant check my work)

BO Variable Direct P CodeS

round((sum(tmp.Status_9_P_Code) - sum(tmp.dc_status_9_p_Codes)) / count(distinct tmp.site_no),0)

BO Variable Direct OOS

round(((sum(tmp.Status_9_P_Code) - sum(tmp.p_code_in_stock)) - (sum(tmp.dc_status_9_p_Codes) - sum(tmp.dc_P_Code_In_Stock)))/count(distinct tmp.site_no),2)

^ These are correct

The third BO query is Direct Service Level
which in BO is:

=If([DIRECT P CODES] <> 0;([DIRECT P CODES]-[DIRECT OOS])/[DIRECT P CODES])

Which I need to port to SQL, and I think is something along the lines of

round((sum(tmp.Status_9_P_Code) - SUM(tmp.dc_status_9_p_Codes) - (sum(tmp.p_code_in_stock) - sum(tmp.dc_P_Code_In_Stock))) / nullif(count(distinct tmp.site_no), 0) /
nullif((sum(tmp.Status_9_P_Code) - sum(tmp.dc_status_9_p_Codes)) / count(distinct tmp.site_no), 0), 2)

I suspect this is wrong, but I cannot check at the minute, and it looks seriously dirty..... Can anyone with a clue shed some light.

<Awaits really helpful answers>

 

[Ray Gin]

Not sure on this mate. @Ol’ Tarby can you assist?

 

[Oggy997]

^ by deleting this post, preferably.

 

[Ray Gin]

^ by deleting this post, preferably.

It's Friday night mate get a few pints down you instead.

 

[boultonn]

I’d just ask chatGPT tbh
Or try dropping some tables

 

[Oggy997]

I've tried gee pee tee, but if its shite in its shite out, innit

 

[Strell]

I've tried gee pee tee, but if its shite in its shite out, innit

Can’t you put something like….

If direct p = this
Direct o = that
And direct service = them two

Remove the need to create them separately. It should be able to do that?

On my phone and defo not doing it for you

 

[Oggy997]

Not in sql?
I can use a case statement obviously but I can't reference columns in my current select? Which they would be?

Id have to do:
case when
<mess> then do x
Else <bigger mess > end

No?

 

[Ol’ Tarby]

Not sure on this mate. @Ol’ Tarby can you assist?

No idea bud