Hello,
I have been using the following expression in Access as part of a statement to query an Oracle database: - (Sum(CASE WHEN STATS_DAILY_SA.LOGIN_TIME > (STATS_DAILY_SA.SCHEDULED_TIME - DECODE(STATS_DAILY_SA.EXCEPTION_TIME, Null, 0,
-
STATS_DAILY_SA.EXCEPTION_TIME)) THEN 0 ELSE ((STATS_DAILY_SA.SCHEDULED_TIME - DECODE(STATS_DAILY_SA.EXCEPTION_TIME, Null, 0,
-
STATS_DAILY_SA.EXCEPTION_TIME)) - STATS_DAILY_SA.LOGIN_TIME) END)/Sum(STATS_DAILY_SA.SCHEDULED_TIME)*100)
It works fine.
However, I now need to use the same statement (well, get the same result at least) within Access - the Query I will be querying, "Query2" has the same fields, but I am trying to use this expression as part of a crosstab query: - TRANSFORM (Sum(CASE WHEN Query2.LOGIN_TIME > (Query2.SCHEDULED_TIME - DECODE(Query2.EXCEPTION_TIME, Null, 0,
-
Query2.EXCEPTION_TIME)) THEN 0 ELSE ((Query2.SCHEDULED_TIME - DECODE(Query2.EXCEPTION_TIME, Null, 0,
-
Query2.EXCEPTION_TIME)) - Query2.LOGIN_TIME) END)/Sum(Query2.SCHEDULED_TIME)*100) AS SumOfSTAT
This doesn't work, giving me a 3075 Error - syntax error (missing operator) in query expression...
I first assumed that this was due to using the Decode function (which I don't believe works in Access?), so I tried using IIFs and even Switch statements, but I'm getting similar errors.
Can anyone help with this, I'm getting a serious headache here!!
Many thanks,
Olly
1  5478 
Solved this by converting my Nulls to zero on the way into query2 using NVL, then used a simpler IIF statement for the crosstab: - (Sum(IIf(Query2.LOGIN_TIME>(Query2.SCHEDULED_TIME-Query2.EXCEPTION_TIME),0,(Query2.SCHEDULED_TIME-Query2.EXCEPTION_TIME-Query2.LOGIN_TIME)))/Sum(Query2.SCHEDULED_TIME)*100)
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: etravels |
last post by:
Hiyas
I am currently working on Oracle Report which ties in with my a report
in the ebusiness suite of Oracle telesales module.
Basically, the address on statement reports have blank fields (null)
where the customer does not have and address2, address 3,
At present its set up to read customers
ADDRESS1
|
by: Jevon |
last post by:
I am trying to conert a SQL Server (2000) database to Oracle to see if
it is a supportable back end for my product. I am running into a
stone wall with Stored Procedures though. I was wondering if someone
could explain a simple way to turn this T-SQL statement into a Oracle
PL/SQL statement:
CREATE PROCEDURE
@BSID Int
AS
SELECT...
|
by: richasaraf |
last post by:
Hello everyone,
I'm facing problem in converting CASE statements into DECODE. As i have PL/SQL 8i, so it does not handle CASE statements. Please send me the solutions . Then basic problem is the CASE has NOT condition i.e. <> ...... and this particularly i'm not able to convert in DECODE.
Please send me the solution as early as possible.....
|
by: Amin Schoeib |
last post by:
Hi,
Like I see there is no equivalent to the Oracle decode Function
In Postgres.Is there maybe somebody who wrote decode as a
Function?
Schoeib
4Tek Gesellschaft für angewandte Informationstechnologien mbH
Schoeib Amin
Tel. +49 (0) 69 697688-132
|
by: Michael John |
last post by:
Dear Oracle Developers,
my task is to make up a Oracle View from a Pivot table in MS Access.
Given are two tables to join:
T_FIRM:
FIRM_ABBR VARCHAR2(3 BYTE),
FIRM_LONG VARCHAR2(70 BYTE),
CONSTRAINT PK_FIRM PRIMARY KEY (FIRM_ABBR)
| | |
by: Alfonso Esteban Gonzalez Sencion |
last post by:
I am trying to use Access as a front end for extracting information from
an Oracle database. I started using linked tables but I am getting a
very curious behaviour.
When I consult the linked table in access, the total number of records
is OK but some records appear several times and some records do not
appear at all. It seems as if access...
|
by: Medhatithi |
last post by:
Hi,
I am facing a strange problem with decode function in oracle. My table name is status_hist. Below is the query I am hitting on this table:
select max(decode(voided_flag,'Y',null,decode(status_hist.status,'WD',status_date)))
from status_hist
where sk_seq=6574
The result returned is '29-SEP-05'
However, I checked out the table...
|
by: Vinod Sadanandan |
last post by:
All,
Below listed are the new features in Oracle 11g ,please join me in this discussion to generate a testcase and analyze each of the listed features .
Precompilers: Additional Array INSERT and SELECT Syntax Support by
Pro*C/C++ and Pro*COBOL
Precompilers: Dynamic SQL Statement Caching in Pro*C/C++ and Pro*COBOL
...
|
by: Michael Goerz |
last post by:
Hi,
I am writing unicode stings into a special text file that requires to
have non-ascii characters as as octal-escaped UTF-8 codes.
For example, the letter "ƨ" (latin capital I with acute, code point 205)
would come out as "\303\215".
I will also have to read back from the file later on and convert the
escaped characters back into a...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
