help with SQL
I have a query that I am using a similar syntax for a couple of different
databases, but I am unable to find the equivalent in informix. Any help
would be greatly appreciated. Here is the syntax from SQL Server;
SELECT COUNT(NULLIF(Vendor_Records.OutDuration, 0)) AS Calls,
COUNT(NULLIF(Vendor_Records.SeizedDuration, 0)) As Attempts
FROM Vendor_Records
WHERE (Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')
The nullif allows me to set the value to null if certain conditions exist.
In this case if the value is zero then it sets the value to a null so that
it will not be counted and I can accurately detect the Completed Calls from
the Attempted Calls.
Any help on a command and a syntax for Informix would be EXTREMELY helpful.
Thanks in advance for your assistance.
Jeff
Network Engineer
Why not just specify:
AND Vendor_Records.OutDuration != 0
AND Vendor_Records.SeizedDuration != 0
in the WHERE clause.
Now I know these are not mutual conditions so this becomes:
SELECT
(SELECT COUNT(Vendor_Records.OutDuration)
FROM Vendor_Records
WHERE Vendor_Records.OutDuration != 0
AND Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')
AS Calls,
(SELECT COUNT(Vendor_Records.SeizedDuration)
FROM Vendor_Records
WHERE Vendor_Records.SeizedDuration != 0
AND Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')
AS Attempts
FROM systables
WHERE tabid = 1;
Must be a fancy way but this works and is more efficient and portable.
Art S. Kagel
Jeff Bluemel wrote:
>
> I have a query that I am using a similar syntax for a couple of different
> databases, but I am unable to find the equivalent in informix. Any help
> would be greatly appreciated. Here is the syntax from SQL Server;
>
> SELECT COUNT(NULLIF(Vendor_Records.OutDuration, 0)) AS Calls,
> COUNT(NULLIF(Vendor_Records.SeizedDuration, 0)) As Attempts
> FROM Vendor_Records
> WHERE (Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')>
> The nullif allows me to set the value to null if certain conditions exist.
> In this case if the value is zero then it sets the value to a null so that
> it will not be counted and I can accurately detect the Completed Calls from
> the Attempted Calls.
>
> Any help on a command and a syntax for Informix would be EXTREMELY helpful.
>
> Thanks in advance for your assistance.
>
> Jeff
> Network Engineer
OK, I modified the query to the following which reflects my Informix
database;
SELECT dest_country AS Country,
(SELECT COUNT(outbound_duration)
FROM master_call
WHERE outbound_duration != 0
AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-2623:59:59'
AS Attempts,
(SELECT COUNT(answered_duration)
FROM master_call
WHERE answered_duration != 0
AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26
23:59:59'
AS Calls
FROM master_call
WHERE out_trunk_group=18
AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26
23:59:59'
GROUP BY dest_country;
This is the output that I received;
country attempts callspts
46 31806 14664
62 31806 14664
81 31806 14664
44 31806 14664
47 31806 14664
212 31806 14664
86 31806 14664
39 31806 14664
671 31806 14664
How can I modify this so the it calculates the calls and attempts by my
other group by and selected information? There will be several other things
that I add to this.
Thanks,
Jeff
"Art S. Kagel" <kagel@bloomberg.net> wrote in message
news:397F6287.40082BB0@bloomberg.net...
> Why not just specify:
>
> AND Vendor_Records.OutDuration != 0
> AND Vendor_Records.SeizedDuration != 0
>
> in the WHERE clause.
>
> Now I know these are not mutual conditions so this becomes:
>
> SELECT
> (SELECT COUNT(Vendor_Records.OutDuration)
> FROM Vendor_Records
> WHERE Vendor_Records.OutDuration != 0
> AND Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')
> AS Calls,
> (SELECT COUNT(Vendor_Records.SeizedDuration)
> FROM Vendor_Records
> WHERE Vendor_Records.SeizedDuration != 0
> AND Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')
> AS Attempts
> FROM systables
> WHERE tabid = 1;
>
> Must be a fancy way but this works and is more efficient and portable.
>
> Art S. Kagel
>
>
> Jeff Bluemel wrote:
> >
> > I have a query that I am using a similar syntax for a couple of
different
> > databases, but I am unable to find the equivalent in informix. Any help
> > would be greatly appreciated. Here is the syntax from SQL Server;
> >
> > SELECT COUNT(NULLIF(Vendor_Records.OutDuration, 0)) AS Calls,
> > COUNT(NULLIF(Vendor_Records.SeizedDuration, 0)) As Attempts
> > FROM Vendor_Records
> > WHERE (Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')> >
> > The nullif allows me to set the value to null if certain conditions
exist.
> > In this case if the value is zero then it sets the value to a null so
that
> > it will not be counted and I can accurately detect the Completed Calls
from
> > the Attempted Calls.
> >
> > Any help on a command and a syntax for Informix would be EXTREMELY
helpful.
> >
> > Thanks in advance for your assistance.
> >
> > Jeff
> > Network Engineer
Try this :
SELECT dest_country AS Country,
SUM(CASE
WHEN outbound_duration > 0 THEN 1
ELSE 0
END) AS Attempts,
SUM(CASE
WHEN answered_duration > 0 THEN 1
ELSE 0
END) AS Calls
FROM master_call
WHERE out_trunk_group=18
AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26 23:59:59'
GROUP BY dest_country;
Rudy
Jeff Bluemel wrote:
> SELECT dest_country AS Country,
> (SELECT COUNT(outbound_duration)
> FROM master_call
> WHERE outbound_duration != 0
> AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26> 23:59:59'
> AS Attempts,
> ...
> FROM master_call
> WHERE out_trunk_group=18
> AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26
> 23:59:59'
> GROUP BY dest_country;
>
> This is the output that I received;
>
> country attempts callspts
>
> 46 31806 14664
> 62 31806 14664
> ...
> 671 31806 14664
>
> How can I modify this so the it calculates the calls and attempts by my
> other group by and selected information? There will be several other things
> that I add to this.
> Jeff
I like Rudy's query if you have 7.3x or later. Anyway here is the
fix for you, which is to corellate the two sub-queries:
Jeff Bluemel wrote:
>
> OK, I modified the query to the following which reflects my Informix
> database;
>
SELECT A.dest_country AS Country,
(SELECT COUNT(B.outbound_duration)
FROM master_call AS B
WHERE B.outbound_duration != 0
AND B.call_date_time_k BETWEEN '2000-07-25 00:00:00'
AND '2000-07-26 23:59:59'
AND B.dest_country = A.dest_country {This may want A.Country}
AS Attempts,
(SELECT COUNT(C.answered_duration)
FROM master_call AS C
WHERE C.answered_duration != 0
AND C.call_date_time_k BETWEEN '2000-07-25 00:00:00'
AND '2000-07-26 23:59:59'
AND C.dest_country = A.dest_country {This may want A.Country}
AS Calls
FROM master_call AS A
WHERE A.out_trunk_group=18
AND A.call_date_time_k BETWEEN '2000-07-25 00:00:00'
AND '2000-07-26 23:59:59'
GROUP BY dest_country;>
> This is the output that I received;
>
> country attempts callspts
>
> 46 31806 14664
> 62 31806 14664
> 81 31806 14664
> 44 31806 14664
> 47 31806 14664
> 212 31806 14664
> 86 31806 14664
> 39 31806 14664
> 671 31806 14664
>
> How can I modify this so the it calculates the calls and attempts by my
> other group by and selected information? There will be several other things
> that I add to this.
>
> Thanks,
>
> Jeff
>
> "Art S. Kagel" <kagel@bloomberg.net> wrote in message
> news:397F6287.40082BB0@bloomberg.net...
> > Why not just specify:
> >
> > AND Vendor_Records.OutDuration != 0
> > AND Vendor_Records.SeizedDuration != 0
> >
> > in the WHERE clause.
> >
> > Now I know these are not mutual conditions so this becomes:
> >
> > SELECT
> > (SELECT COUNT(Vendor_Records.OutDuration)
> > FROM Vendor_Records
> > WHERE Vendor_Records.OutDuration != 0
> > AND Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')
> > AS Calls,
> > (SELECT COUNT(Vendor_Records.SeizedDuration)
> > FROM Vendor_Records
> > WHERE Vendor_Records.SeizedDuration != 0
> > AND Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')
> > AS Attempts
> > FROM systables
> > WHERE tabid = 1;
> >
> > Must be a fancy way but this works and is more efficient and portable.
> >
> > Art S. Kagel
> >
> >
> > Jeff Bluemel wrote:
> > >
> > > I have a query that I am using a similar syntax for a couple of
> different
> > > databases, but I am unable to find the equivalent in informix. Any help
> > > would be greatly appreciated. Here is the syntax from SQL Server;
> > >
> > > SELECT COUNT(NULLIF(Vendor_Records.OutDuration, 0)) AS Calls,
> > > COUNT(NULLIF(Vendor_Records.SeizedDuration, 0)) As Attempts
> > > FROM Vendor_Records
> > > WHERE (Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')> > >
> > > The nullif allows me to set the value to null if certain conditions
> exist.
> > > In this case if the value is zero then it sets the value to a null so
> that
> > > it will not be counted and I can accurately detect the Completed Calls
> from
> > > the Attempted Calls.
> > >
> > > Any help on a command and a syntax for Informix would be EXTREMELY
> helpful.
> > >
> > > Thanks in advance for your assistance.
> > >
> > > Jeff
> > > Network Engineer