Immediate IFF() ?

Is there an Immediate IFF() function in SQL 7.0?
I'm trying to do this:
INSERTINTO Myfile(Name,IFF(TDate='',Ddate,Tdate)
SELECTd.Name, d.tdate
FROMMasterfile d, Anotherfile s
WHEREd.Name = s.Name
thx,
mac
>> Is there an Immediate IFF() function in SQL 7.0?
No, in most cases, CASE expressions would suffice. For details and other
alternatives, read through the topics CASE, COALESCE, ISNULL & NULLIF in SQL
Server Books Online.
Anith
sql

Immediate IFF() ?

Is there an Immediate IFF() function in SQL 7.0?
I'm trying to do this:
INSERT INTO Myfile(Name,IFF(TDate='',Ddate,Tdate)
SELECT d.Name, d.tdate
FROM Masterfile d, Anotherfile s
WHERE d.Name = s.Name
thx,
mac>> Is there an Immediate IFF() function in SQL 7.0?
No, in most cases, CASE expressions would suffice. For details and other
alternatives, read through the topics CASE, COALESCE, ISNULL & NULLIF in SQL
Server Books Online.
--
Anith

Immediate IFF() ?

Is there an Immediate IFF() function in SQL 7.0?
I'm trying to do this:
INSERT INTO Myfile(Name,IFF(TDate='',Ddate,Tdate)
SELECT d.Name, d.tdate
FROM Masterfile d, Anotherfile s
WHERE d.Name = s.Name
thx,
mac>> Is there an Immediate IFF() function in SQL 7.0?
No, in most cases, CASE expressions would suffice. For details and other
alternatives, read through the topics CASE, COALESCE, ISNULL & NULLIF in SQL
Server Books Online.
Anith

Imbedded subroutine prefix error

I am trying to run this routine which works fine until I put the outside
Select in. The function consists of 4 Select statements that I need to do
to get counts of various comparisons in my tables. The problem is that the
inside PositionID has to refer to the outside PositionID and that is where I
get the error ("WHERE p3.PositionID = p.PositionID ").
How do I get it to refer this table (p).
SELECT c. CompanyID,PositionID,JobTitleShort,Poste
dData =
replace(convert(varchar,p.DateCreated,6),' ',''),
Qualified = (
SELECT Count(*)
FROM
(
SELECT
Cat1,Value1,Cat2,Value2,Cat3,Value3,Cat4
,Value4,Cat5,Value5,Cat6,Value6,Cat7
,Value7,
Cat8,Value8,TotalCats=Cat1+Cat2+Cat3+Cat
4+Cat5+Cat6+Cat7+Cat8,
TotalValues =
Value1+Value2+Value3+Value4+Value5+Value
6+Value7+Value8,
CriteriaStatus = CASE WHEN
(Cat1+Cat2+Cat3+Cat4+Cat5+Cat6+Cat7+Cat8
) =
(Value1+Value2+Value3+Value4+Value5+Valu
e6+Value7+Value8) THEN 'All'
WHEN
(Value1+Value2+Value3+Value4+Value5+Valu
e6+Value7+Value8) >=
((Cat1+Cat2+Cat3+Cat4+Cat5+Cat6+Cat7+Cat
8)/2) THEN 'Most'
ELSE 'Fails' END
FROM
(
SELECT Cat1=Case when OvertimeRequired = 1 then 1 else 0 end,
Value1=Case when OvertimeRequired = 1 then (Case when
WorkOvertime = 1 then 1 else 0 end) else 0 end,
Cat2=Case when SponserNonUS = 0 then 1 else 0 end,
Value2=Case when SponserNonUS = 0 then (Case when m.USCitizen
= 0 then 0 else 1 end) else 0 end,
Cat3=Case when p3.JobDistance > 0 then 1 else 0 end,
Value3 = Case when p3.JobDistance > 0 then (Case when
dbo.GetDistance(ZipCode,m.Zip) <= p3.JobDistance then 1 else 0 end) else 0
end,
Cat4=Case when p3.EducationLevel is not null then 1 else 0
end,
Value4=Case when p3.EducationLevel is not null then (Case
when m.EducationLevel >= p3.EducationLevel then 1 else 0 end) else 0 end,
Cat5=Case when p3.CareerLevel is not null then 1 else 0 end,
Value5=Case when p3.CareerLevel is not null then (Case when
m.CareerLevel >= p3.CareerLevel then 1 else 0 end) else 0 end,
Cat6=Case when p3.ExperienceLevel is not null then 1 else 0
end,
Value6=Case when p3.ExperienceLevel is not null then (Case
when m.ExperienceLevel >= p3.ExperienceLevel then 1 else 0 end) else 0 end,
Cat7=Case when p3.ScreenTestRequired = 1 then 1 else 0 end,
Value7=Case when p3.ScreenTestRequired = 1 then
(Case when ScreenTestScore >= p3.NotifyScreenMinScore then
1 else 0 end) else 0 end,
Cat8=Case when p3.SkillsTestRequired = 1 and
SkillsTestOffered is not null then 1 else 0 end,
Value8=Case when p3.SkillsTestRequired = 1 and
SkillsTestOffered is not null then
(Case when SkillsTestScore >= p3.NotifySkillsMinScore then
1 else 0 end) else 0 end
FROM applicant a
JOIN logon l on (a.UserID = l.UserID)
JOIN Position p3 on (a.PositionID = p3.PositionID)
JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
LEFT JOIN ApplicantPosition ap on (ap.ApplicantID =
a.ApplicantID)
LEFT JOIN MyInfo m on (m.UserID = a.UserID)
WHERE p3.PositionID = p.PositionID
) as a1
) as a2
WHERE CriteriaStatus = 'Most')
FROM position p
JOIN Companies c on (c.CompanyID = p.CompanyID)
The error I get is:
The column prefix 'p' does not match with a table name or alias name
used in the query.
Thanks,
Tomtshad (tfs@.dslextreme.com) writes:
> I am trying to run this routine which works fine until I put the outside
> Select in. The function consists of 4 Select statements that I need to
> do to get counts of various comparisons in my tables. The problem is
> that the inside PositionID has to refer to the outside PositionID and
> that is where I get the error ("WHERE p3.PositionID = p.PositionID ").
> How do I get it to refer this table (p).
Looking at your query, I will have to say that there is a whole lot of
fuzz just to get a COUNT(*). You must be able to simplify this. And maybe
even to the point you don't need to nest any derived tables.
Also, my experience is that subqueries in the SELECT list are often
expensive. Try to move the derived table to the FROM clause so you get
something like:
SELECT c.CompanyID, ... Qualified = d.cnt
FROM Positions p
JOIN Companies c ON ...
JOIN (SELECT position, cnt = COUNT(*)
FROM FROM applicant a
JOIN logon l on (a.UserID = l.UserID)
JOIN Position p3 on (a.PositionID = p3.PositionID)
JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
LEFT JOIN ApplicantPosition ap
on (ap.ApplicantID = a.ApplicantID)
LEFT JOIN MyInfo m on (m.UserID = a.UserID)
GROUP BY position) AS d ON p ON d.position = p.position
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm not sure if yours would work or not. I am just starting to look at it,
but I wasn't just trying to get the count().
The inside query is putting together a list of criteria and whether they
were met or not.
I then need to total the number of criteria and whether the user has met
each criteria or not (1=yes and 0= no). I use this total to tell whether
the user has met the criteria, met most or fails to meet them.
This is used in various reports. I now have a report that just gives me a
total number of users that have passed the criteria for each position. This
is why I need the inside querie to refer to the outside PositionID, but I
get an error on it. I have to do the inside 2 queries first to find out
whether users have passed or not.
It may not be the best way, but this was what I came up with a couple of
months ago on this group to make this work.
Thanks,
Tom
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9743B83E6219Yazorman@.127.0.0.1...
> tshad (tfs@.dslextreme.com) writes:
> Looking at your query, I will have to say that there is a whole lot of
> fuzz just to get a COUNT(*). You must be able to simplify this. And maybe
> even to the point you don't need to nest any derived tables.
> Also, my experience is that subqueries in the SELECT list are often
> expensive. Try to move the derived table to the FROM clause so you get
> something like:
> SELECT c.CompanyID, ... Qualified = d.cnt
> FROM Positions p
> JOIN Companies c ON ...
> JOIN (SELECT position, cnt = COUNT(*)
> FROM FROM applicant a
> JOIN logon l on (a.UserID = l.UserID)
> JOIN Position p3 on (a.PositionID = p3.PositionID)
> JOIN ApplicantResume ar on (ar.ApplicantID = a.ApplicantID)
> LEFT JOIN ApplicantPosition ap
> on (ap.ApplicantID = a.ApplicantID)
> LEFT JOIN MyInfo m on (m.UserID = a.UserID)
> GROUP BY position) AS d ON p ON d.position = p.position
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||tshad (tscheiderich@.ftsolutions.com) writes:
> I'm not sure if yours would work or not. I am just starting to look at
> it, but I wasn't just trying to get the count().
Ah, I see now that the outermost table had a WHERE clause. Still, all
the columns that comes before the definition of Criteria_status, has
no actual use in the query. (But I can understand that they are good
for debug.)
I can't say for sure that the outline that I gave will work for you,
as I don't have tables, nor sample data to test with (hint, hint!),
but I would encourage you to study the possibility.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97444A54ACE5Yazorman@.127.0.0.1...
> tshad (tscheiderich@.ftsolutions.com) writes:
> Ah, I see now that the outermost table had a WHERE clause. Still, all
> the columns that comes before the definition of Criteria_status, has
> no actual use in the query. (But I can understand that they are good
> for debug.)
>
Actually, I would be getting multiple records with Criteria_status equal to
either "All", "Most" or "Fail". But I am only interested in the ones that
are equal to "Most". But I have to read all the records and calculate the
Criterias and Values before I know if they fail or not.
I then need to filter out the "Most" ones and then count them.

> I can't say for sure that the outline that I gave will work for you,
> as I don't have tables, nor sample data to test with (hint, hint!),
> but I would encourage you to study the possibility.
I will.
But why was I getting the error?
And how can I get it work?
I have done subqueries before where the inside query references the outside
query. But I can't figure out why this one won't work.
Thanks,
Tom
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi tshad
I think I see what you're trying to do: in your set A2 you want to get
a count of rows BY PositionID, like so:
Position ID CountRows
1 6
2 3
3 10
etc.
Is this right?
To do this, you need to return a rowcount AND the position ID from A2 -
you can then join A2 to your other tables (position and Companies) on
PositionID, so that A2 doesn't have to try to refer to a column outside
itself. Instead of
SELECT Count(*) FROM (lots of SQL) AS A2
use
SELECT PositionID,Count(*) FROM (lots of SQL) AS A2 GROUP BY Position
ID.
Make this statement with the GROUP BY into a separate subquery, and
join it to the two other tables which you've put at the end:
SELECT c.CompanyID,p.[nb: qualified because there's now 2 PositionID
columns in the outer set]PositionID,JobTitleShort,PostedData =
replace(convert(varchar,p.DateCreated,6),' ',''),
totals.RowCount AS Qualified FROM
(SELECT PositionID,Count(*) FROM (lots of SQL) AS A2 GROUP BY
Position ID) totals
INNER JOIN
position p
ON totals.PositionID=p.PositionID
JOIN
Companies c
on (c.CompanyID = p.CompanyID)
WHERE... etc
By the way, you can also get rid of all the complicated calculation
within A2 - it's wasted, as having done all that calculation, SQL then
only returns a rowcount from the resulting set!
A2 (the SQL i've referred to as (lots of SQL) can be slimmed down to
something like this:
SELECT PositionID [qualify this, specifying which table it should come
from] FROM
applicant a
JOIN
logon l
etc
hope this helps.
cheers
Seb|||tshad (tscheiderich@.ftsolutions.com) writes:
> But why was I getting the error?
If you insist to get answer to that question, you better post the
CREATE TABLE statements for the table, so it's possible to play with
query.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Fri, 6 Jan 2006 18:00:48 -0800, tshad wrote:
(snip)
>But why was I getting the error?
Hi Tom,
Normally, a derived table can't refer to columns from the outer query.
The table has to be materialized before the joins in the FROM clause can
be evaluated, so there's no way to know which row is referred to. (This
is the theoretic description - in reality, the optimizer will probably
choose a faster strategy).
If a derived table is used inside a subquery, it still can't refer to
other tables used in the subquery, but it can refer to the tables used
outside of the subquery. This is possible becuase the complete subquery
has to be re-evaluated for any row in the outer query anyway (again, in
theory).
It appears as if SQL Server is unable to recognise this situation if you
start nesting subqueries. I would consider this to be a bug. The very
simple script below will reproduce this behaviour on SQL Server 2000
SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
fixed in SQL Server 2005.
CREATE TABLE t1 (a int, b int)
CREATE TABLE t2 (a int, b int)
go
-- Single derived table - no problem
SELECT (SELECT a
FROM (SELECT *
FROM t2
WHERE t2.b = t1.b
) AS Derived
) AS Subquery
FROM t1
go
-- Nested derived table - error
SELECT (SELECT a
FROM (SELECT *
FROM (SELECT *
FROM t2
WHERE t2.b = t1.b
) AS Inner_Derived
) AS Outer_Derived
) AS Subquery
FROM t1
go
DROP TABLE t1
DROP TABLE t2
go

>And how can I get it work?
Now that's the harder question, I guess.
Start with www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
> It appears as if SQL Server is unable to recognise this situation if you
> start nesting subqueries. I would consider this to be a bug. The very
> simple script below will reproduce this behaviour on SQL Server 2000
> SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
> fixed in SQL Server 2005.
Both your queries work on SQL 2005, so it appears to have been fixed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:h9k0s1pqtgusku3pmh58bqk47d67ab75uj@.
4ax.com...
> On Fri, 6 Jan 2006 18:00:48 -0800, tshad wrote:
> (snip)
> Hi Tom,
> Normally, a derived table can't refer to columns from the outer query.
> The table has to be materialized before the joins in the FROM clause can
> be evaluated, so there's no way to know which row is referred to. (This
> is the theoretic description - in reality, the optimizer will probably
> choose a faster strategy).
> If a derived table is used inside a subquery, it still can't refer to
> other tables used in the subquery, but it can refer to the tables used
> outside of the subquery. This is possible becuase the complete subquery
> has to be re-evaluated for any row in the outer query anyway (again, in
> theory).
> It appears as if SQL Server is unable to recognise this situation if you
> start nesting subqueries. I would consider this to be a bug. The very
> simple script below will reproduce this behaviour on SQL Server 2000
> SP4. I don't have SQL Server 2005 installed, so I can't tell if this is
> fixed in SQL Server 2005.
> CREATE TABLE t1 (a int, b int)
> CREATE TABLE t2 (a int, b int)
> go
> -- Single derived table - no problem
> SELECT (SELECT a
> FROM (SELECT *
> FROM t2
> WHERE t2.b = t1.b
> ) AS Derived
> ) AS Subquery
> FROM t1
> go
> -- Nested derived table - error
> SELECT (SELECT a
> FROM (SELECT *
> FROM (SELECT *
> FROM t2
> WHERE t2.b = t1.b
> ) AS Inner_Derived
> ) AS Outer_Derived
> ) AS Subquery
> FROM t1
> go
> DROP TABLE t1
> DROP TABLE t2
> go
Hi Hugo,
Yes, that is exactly what is happening. There may be a different way to
restructure the statement (maybe by a join to get rid of the inner table),
but I'm not sure how it would be done in my situation.
Maybe the inner_derived table has to reference the Outer_Derived and than
the Outer_Derived reference t1 somehow.

>
> Now that's the harder question, I guess.
> Start with www.aspfaq.com/5006.
This doesn't appear to be the faq you meant (or maybe it was) as doesn't
seem to have anything to do with query/subquery question.
Thanks,
Tom
> --
> Hugo Kornelis, SQL Server MVP

Image in SQL2000

Hello!
What function in SQL SERVER 2000 to test an image field if empty or not? Tried NoT NULL but no success.
I want to return only items with pictures...
THanksDid you try DATALENGTH function?sql

Image Data and PATINDEX function

Hi,
I need to retrieve the offset of some text stored as image. PATINDEX does not work for image data type. What other choice do I have?
Thank you,
Amar DasAre you searching for the text or the binary pattern of the text?

Use the image datatype for storing binary data and (n)text datatype for storing text.|||Thanks Paul.

I am searching for the text. Unfortunately the table is created by my client and I can not alter it.|||Hum... Books Online seems to have some errors...

try this...
create table #Tmp(f1 text, f2 image)
insert into #Tmp values('Test number 1','Test number 1')
insert into #Tmp values('Test number 2','Test number 2')
insert into #Tmp values('Test number 3','Test number 3')
insert into #Tmp values('Test number 4','Test number 4')
select charindex(cast('r 2' as varbinary),f2),* from #Tmpsql

I''m not able to connect to my SQL server with SQLconnect

Hi,

I'm using the SQLconnect function in order to connect to my SQL server

I want to connect to a database called "CookieJar" without user name and password

this is the code:

#include "Container.h"

#include <windows.h>

#include <sqlext.h>

int main()

{

HENV hEnv = NULL; // Env Handle from SQLAllocEnv()

HDBC hDBC = NULL; // Connection handle

HSTMT hStmt = NULL;// Statement handle

UCHAR szDSN[1024] = "CookieJar";// Data Source Name buffer

UCHAR szUID[10] = "";// User ID buffer

UCHAR szPasswd[10] = "";// Password buffer

UCHAR szModel[128];// Model buffer

SDWORD cbModel;// Model buffer bytes recieved

char buff[9] = "Testing";

UCHAR szSqlStr[128]= "INSERT into (Tablename) (ColumnName) Values ('Testing')" ;

RETCODE retcode;

//sprintf((char*)szSqlStr,"INSERT into (Tablename)(Columname) Values ('%s')",buff);

// Allocate memory for ODBC Environment handle

SQLAllocEnv (&hEnv);

// Allocate memory for the connection handle

SQLAllocConnect (hEnv, &hDBC);

// Connect to the data source "test" using userid and password.

retcode = SQLConnect (hDBC, (SQLWCHAR*)szDSN, SQL_NTS,/* (SQLWCHAR*)szUID*/ NULL, SQL_NTS, /*(SQLWCHAR*)szPasswd*/ NULL, SQL_NTS);

if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)

{

// Allocate memory for the statement handle

retcode = SQLAllocStmt (hDBC, &hStmt);

// Prepare the SQL statement by assigning it to the statement handle

retcode = SQLPrepare (hStmt, (SQLWCHAR*)szSqlStr, sizeof (szSqlStr));

// Execute the SQL statement handle

retcode = SQLExecute (hStmt);

// Project only column 1 which is the models

SQLBindCol (hStmt, 1, SQL_C_CHAR, szModel, sizeof(szModel), &cbModel);

// Get row of data from the result set defined above in the statement

retcode = SQLFetch (hStmt);

// Free the allocated statement handle

SQLFreeStmt (hStmt, SQL_DROP);

// Disconnect from datasource

SQLDisconnect (hDBC);

}

// Free the allocated connection handle

SQLFreeConnect (hDBC);

// Free the allocated ODBC environment handle

SQLFreeEnv (hEnv);

return 0;

}

The program works but it doesn't get into the "if" section...

And what do I need to put in szDSN - just the database name or the whole connection string?

please help,

Thanks,

Eli

Hi Eli,

http://msdn2.microsoft.com/en-us/library/ms711810.aspx

note that the string you are using for database is actually the server name.

If you want to specify the database, call SQLSetConnectAttr() with SQL_ATTR_CURRENT_CATALOG.

Still not sure why you are sending NULL username and password. You may want to use 0 instead of SQL_NTS there, with NULL strings -- not sure if we ignore that or not.

Hope that helps,

John

I''m not able to connect to my SQL server with SQLconnect

Hi,

I'm using the SQLconnect function in order to connect to my SQL server

I want to connect to a database called "CookieJar" without user name and password

this is the code:

#include "Container.h"

#include <windows.h>

#include <sqlext.h>

int main()

{

HENV hEnv = NULL; // Env Handle from SQLAllocEnv()

HDBC hDBC = NULL; // Connection handle

HSTMT hStmt = NULL;// Statement handle

UCHAR szDSN[1024] = "CookieJar";// Data Source Name buffer

UCHAR szUID[10] = "";// User ID buffer

UCHAR szPasswd[10] = "";// Password buffer

UCHAR szModel[128];// Model buffer

SDWORD cbModel;// Model buffer bytes recieved

char buff[9] = "Testing";

UCHAR szSqlStr[128]= "INSERT into (Tablename) (ColumnName) Values ('Testing')" ;

RETCODE retcode;

//sprintf((char*)szSqlStr,"INSERT into (Tablename)(Columname) Values ('%s')",buff);

// Allocate memory for ODBC Environment handle

SQLAllocEnv (&hEnv);

// Allocate memory for the connection handle

SQLAllocConnect (hEnv, &hDBC);

// Connect to the data source "test" using userid and password.

retcode = SQLConnect (hDBC, (SQLWCHAR*)szDSN, SQL_NTS,/* (SQLWCHAR*)szUID*/ NULL, SQL_NTS, /*(SQLWCHAR*)szPasswd*/ NULL, SQL_NTS);

if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)

{

// Allocate memory for the statement handle

retcode = SQLAllocStmt (hDBC, &hStmt);

// Prepare the SQL statement by assigning it to the statement handle

retcode = SQLPrepare (hStmt, (SQLWCHAR*)szSqlStr, sizeof (szSqlStr));

// Execute the SQL statement handle

retcode = SQLExecute (hStmt);

// Project only column 1 which is the models

SQLBindCol (hStmt, 1, SQL_C_CHAR, szModel, sizeof(szModel), &cbModel);

// Get row of data from the result set defined above in the statement

retcode = SQLFetch (hStmt);

// Free the allocated statement handle

SQLFreeStmt (hStmt, SQL_DROP);

// Disconnect from datasource

SQLDisconnect (hDBC);

}

// Free the allocated connection handle

SQLFreeConnect (hDBC);

// Free the allocated ODBC environment handle

SQLFreeEnv (hEnv);

return 0;

}

The program works but it doesn't get into the "if" section...

And what do I need to put in szDSN - just the database name or the whole connection string?

please help,

Thanks,

Eli

Hi Eli,

http://msdn2.microsoft.com/en-us/library/ms711810.aspx

note that the string you are using for database is actually the server name.

If you want to specify the database, call SQLSetConnectAttr() with SQL_ATTR_CURRENT_CATALOG.

Still not sure why you are sending NULL username and password. You may want to use 0 instead of SQL_NTS there, with NULL strings -- not sure if we ignore that or not.

Hope that helps,

John

IIF in SQL server

Is there a way in SQL Server to run the simple query
having something like IIF function in Access.
Similar to this easy query.
SELECT Mobile,iif([PlanType]=1, "New", "Upgrade") As Type FROM Acts WHERE
(RepId = 2194)
Thanks,
Michael
On Wed, 27 Oct 2004 12:51:12 -0700, MichaelK wrote:

>Is there a way in SQL Server to run the simple query
>having something like IIF function in Access.
>Similar to this easy query.
>SELECT Mobile,iif([PlanType]=1, "New", "Upgrade") As Type FROM Acts WHERE
>(RepId = 2194)
>Thanks,
>Michael
>
Hi Michael,
SELECT Mobile,
CASE
WHEN PlanType = 1
THEN 'New'
ELSE 'Upgrade'
END AS Type
FROM Acts
WHERE RepId = 2194
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

IIf in SQL

I'm trying to use the function IIf in a view in SQL SERVER 2005, but I get a message "IIf is not recognized as a built-in function".

How can I make it work, or at least see a list of all the built-in functions?

Your DB --> Programmability --> Functions for all built-in functions

|||

You need to use CASE in SQL Server (which is not the same as IIF in Access). You can search (SQL CASE) to find how to use it.

IIF in SQL

hello!
In MS ACCESS 2000, I can use the IIF function like (
SELECT IIF(x>0, "True","False") from x_table.
I tried the ISNULL but got a wrong ouput.
How can I do this also in MS SQL?
Please help...
Any effort is higly appreciated.
Thanksselect case when x>0 then 'True' else 'False' end
from x_table

rudy

IIf in Query question

I am attempting to execute the following query but am getting syntax errors.
I don't have a lot of practice with the IIf function, need help.
Any Ideas??

--QUERY--
UPDATE View_Data SET
Num_07 = Num_01/IIf(Den_01=0,1,Den_01)
where Data_Set_ID = 444

--Error--
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.

More Info
----------------
The fields Num_07,Num_01, Den_01 are all of type 'float'I think that you need to replace the Jet/VB Iif() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctiif.asp) with the SQL Server CASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5t9v.asp) statement.

-PatP

IIF function or case

Hi I am wanting to use some thing like the IIF function in Access in a SQL view I did some looking n your Forum and found the case function its awsome does what I need but i can not use it in a view. Does any one have an alternate solution .. Thanks Jakes

My "Case"

USE SysproCompanyB
GO
SELECT dbo.ZZCuCostValue.Supplier, dbo.ZZCuCostValue.StockCode, dbo.ZZCuCostValue.[Year], dbo.ZZCuCostValue.[Month],'RandCost' =
CASE
WHEN BuyMulDiv IS NULL THEN '0'
WHEN BuyMulDiv = 'M' THEN round(dbo.ZZCuCostValue.UnitCost * dbo.ZZCuCostValue.ExchangeRate,4)
WHEN BuyMulDiv = 'D' THEN round(dbo.ZZCuCostValue.UnitCost / dbo.ZZCuCostValue.ExchangeRate,4)
ELSE 0
END
FROM dbo.ApSupplier INNER JOIN
dbo.TblCurrency ON dbo.ApSupplier.Currency = dbo.TblCurrency.Currency INNER JOIN
dbo.ZZCuCostValue ON dbo.ApSupplier.Supplier = dbo.ZZCuCostValue.Supplier

GORun the create view script in Query Analyzer, and you should not get the Enterprise Mangler error message. CASE is perfectly fine in a view, but EM has problems with it.|||Thanks I will try that|||Hi MCrowley. This may sound realy simple how do I run the script in Query Analyzer I can not seem to find any thing that looks fimilar..|||Are you in Enterprise Manager? If so, click on the Tools Menu Item then click on SQL Query Analyzer. Then, select your database from the dropdown atthe top middle of the screen, cut and paste your code, then click on the green arrow next to the blue checkmark to execute the script.|||HI Tomh53 thanks for that but I was wanting to know how to run the create view script that MCrowley told me about.
Jakes|||Here is a sample. Replace the select statement with your query:

create view vwTest
as
select *
from pubs..authors

IIF Function in Dataset

I am trying to build my dataset WHERE clause with the IIF function.
I have 4 parameters that include a "ALL" option and that is what I am trying
to cater for.
Have done is successfully when there is only one parameter involved,
IIF (Parameters!Rep.Value = "0", " WHERE A.Region = '" &
Parameters!Region.Value & "'" , " WHERE A.Region = '" &
Parameters!Region.Value & "' AND A.SLPRSNID = '" & Parameters!Rep.Value &
"'")
but now I have multiple conditions ... have tried the following ...
" WHERE (CustomerServiceRatesReport.Region = @.Region) " &
IIF (Parameters!Rep.Value = "0", "" , " AND
CustomerServiceRatesReport.SLPRSNID = '" & Parameters!Rep.Value & "'") &
IIF (Parameters!Depot.Value = "0", "" , " AND
CustomerServiceRatesReport.OFFID = '" & Parameters!Depot.Value & "'") &
IIF (Parameters!ContractType.Value = "0", "" , " AND
CustomerServiceRatesReport.AV_Contract_Type = '" &
Parameters!ContractType.Value & "'") &
IIF (Parameters!CustomerNumber.Value = "0", "" , " AND
CustomerServiceRatesReport.CUSTNMBR = '" & Parameters!CustomerNumber.Value &
"'")
The above does not give me any errors, but neither does it give me any
results on the report - no matter what my parameter selections are.
Any help / pointers or suggestions on how I can make this work or
alternatives would be much appreciated.
Thank you,
SmeSme,
One thing that I do in these situations is place a textbox on my report
that contains the expression in your dataset, that way you can see what
SQL the dataset is executing.
Should give you a clue about what the query syntax ends up being after
all the expressions are evaluated.
Andy Potter|||Hi Andy,
Thank you for your reply. I will most certainly try that.
Is there any error in the syntax though?
What would the basic syntax be for a multiple IIF function in a query?
Kind Regards,
Sme|||Just in case someone needs the solution ... quite simple actually ...
WHERE (CustomerServiceRatesReport.Region = @.Region) AND
ISNULL(RTRIM(CustomerServiceRatesReport.SLPRSNID ),'') =COALESCE(@.Rep,RTRIM(CustomerServiceRatesReport.SLPRSNID ),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.OFFID),'') =COALESCE(@.Depot,RTRIM(CustomerServiceRatesReport.OFFID ),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.AV_Contract_Type),'') =COALESCE(@.ContractType,
RTRIM(CustomerServiceRatesReport.AV_Contract_Type),'')
AND
ISNULL(RTRIM(CustomerServiceRatesReport.CUSTNMBR),'') =COALESCE(@.CustomerNumber, RTRIM(CustomerServiceRatesReport.CUSTNMBR),'')

IIF Function

I've been looking at the online help and it appears that
this statement should function as follows:
Select IIf(1 > 0 , 'Yes', 'No')
If 1 is greater than 0, Yes should be returned, if not
then No
Sql returns an error message: Incorrect syntax near '>'
Any Ideas?
Thanks
You must be looking at help for MS Access or Excel or something. There is
no such thing as IIF in SQL Server. Maybe try CASE:
SELECT CASE WHEN 1 > 0 THEN 'Yes' ELSE 'No' END
"Raghib" <anonymous@.discussions.microsoft.com> wrote in message
news:100801c4f1fe$f73d1860$a301280a@.phx.gbl...
> I've been looking at the online help and it appears that
> this statement should function as follows:
> Select IIf(1 > 0 , 'Yes', 'No')
>
> If 1 is greater than 0, Yes should be returned, if not
> then No
> Sql returns an error message: Incorrect syntax near '>'
> Any Ideas?
> Thanks
>
|||If you search for IIF in SQL Server Books Online, you will indeed find it.
However, you have to look at the title (or location) and realize it is for
Analysis Services only.
This is not a TSQL function, but rather an MDX function.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23GAGeFg8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> You must be looking at help for MS Access or Excel or something. There is
> no such thing as IIF in SQL Server. Maybe try CASE:
> SELECT CASE WHEN 1 > 0 THEN 'Yes' ELSE 'No' END
>
>
> "Raghib" <anonymous@.discussions.microsoft.com> wrote in message
> news:100801c4f1fe$f73d1860$a301280a@.phx.gbl...
>

IIF Function

I've been looking at the online help and it appears that
this statement should function as follows:
Select IIf(1 > 0 , 'Yes', 'No')
If 1 is greater than 0, Yes should be returned, if not
then No
Sql returns an error message: Incorrect syntax near '>'
Any Ideas?
ThanksYou must be looking at help for MS Access or Excel or something. There is
no such thing as IIF in SQL Server. Maybe try CASE:
SELECT CASE WHEN 1 > 0 THEN 'Yes' ELSE 'No' END
"Raghib" <anonymous@.discussions.microsoft.com> wrote in message
news:100801c4f1fe$f73d1860$a301280a@.phx.gbl...
> I've been looking at the online help and it appears that
> this statement should function as follows:
> Select IIf(1 > 0 , 'Yes', 'No')
>
> If 1 is greater than 0, Yes should be returned, if not
> then No
> Sql returns an error message: Incorrect syntax near '>'
> Any Ideas?
> Thanks
>|||If you search for IIF in SQL Server Books Online, you will indeed find it.
However, you have to look at the title (or location) and realize it is for
Analysis Services only.
This is not a TSQL function, but rather an MDX function.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23GAGeFg8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> You must be looking at help for MS Access or Excel or something. There is
> no such thing as IIF in SQL Server. Maybe try CASE:
> SELECT CASE WHEN 1 > 0 THEN 'Yes' ELSE 'No' END
>
>
> "Raghib" <anonymous@.discussions.microsoft.com> wrote in message
> news:100801c4f1fe$f73d1860$a301280a@.phx.gbl...
>

IIF Function

I've been looking at the online help and it appears that
this statement should function as follows:
Select IIf(1 > 0 , 'Yes', 'No')
If 1 is greater than 0, Yes should be returned, if not
then No
Sql returns an error message: Incorrect syntax near '>'
Any Ideas?
ThanksYou must be looking at help for MS Access or Excel or something. There is
no such thing as IIF in SQL Server. Maybe try CASE:
SELECT CASE WHEN 1 > 0 THEN 'Yes' ELSE 'No' END
"Raghib" <anonymous@.discussions.microsoft.com> wrote in message
news:100801c4f1fe$f73d1860$a301280a@.phx.gbl...
> I've been looking at the online help and it appears that
> this statement should function as follows:
> Select IIf(1 > 0 , 'Yes', 'No')
>
> If 1 is greater than 0, Yes should be returned, if not
> then No
> Sql returns an error message: Incorrect syntax near '>'
> Any Ideas?
> Thanks
>|||If you search for IIF in SQL Server Books Online, you will indeed find it.
However, you have to look at the title (or location) and realize it is for
Analysis Services only.
This is not a TSQL function, but rather an MDX function.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23GAGeFg8EHA.2600@.TK2MSFTNGP09.phx.gbl...
> You must be looking at help for MS Access or Excel or something. There is
> no such thing as IIF in SQL Server. Maybe try CASE:
> SELECT CASE WHEN 1 > 0 THEN 'Yes' ELSE 'No' END
>
>
> "Raghib" <anonymous@.discussions.microsoft.com> wrote in message
> news:100801c4f1fe$f73d1860$a301280a@.phx.gbl...
>> I've been looking at the online help and it appears that
>> this statement should function as follows:
>> Select IIf(1 > 0 , 'Yes', 'No')
>>
>> If 1 is greater than 0, Yes should be returned, if not
>> then No
>> Sql returns an error message: Incorrect syntax near '>'
>> Any Ideas?
>> Thanks
>

IIF Expression with SELECT statement

Is it possible to use a SELECT statement as the expression in an IIF function?
I Cannot get it to work.
i.e. IIF((SELECT COUNT(column) FROM table WHERE column = Fields!Variable.Value) > 0, "BAD","GOOD")
--
Thanks in advance
GHLooks like you can't do that because query can be only inside of data set in
Query element.
Victor
"GH" wrote:
> Is it possible to use a SELECT statement as the expression in an IIF function?
> I Cannot get it to work.
> i.e. IIF((SELECT COUNT(column) FROM table WHERE column => Fields!Variable.Value) > 0, "BAD","GOOD")
> --
> Thanks in advance
> GH|||Nope. Selects can only be used when defining datasets. However, you can use
count (and sum etc) in expressions. From BOL,
>>>>>>>>
The following code example provides a count of employees in the outermost
data region:
Count(Fields!EmployeeID.Value, Nothing)The following code example provides a
count of all orders in the Orders grouping or data region:
Count(Fields!OrderID.Value, "Orders")>>>>>>>>>>--
Bruce Loehle-Conger MVP SQL Server Reporting Services"GH"
<vakar@.community.nospam> wrote in message
news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> Is it possible to use a SELECT statement as the expression in an IIF
function?
> I Cannot get it to work.
> i.e. IIF((SELECT COUNT(column) FROM table WHERE column => Fields!Variable.Value) > 0, "BAD","GOOD")
> --
> Thanks in advance
> GH|||="select count (column) from table where
column='"+iif(fields!variable.value>0, "BAD","GOOD")+"'"
hope works
regards
"GH" <vakar@.community.nospam> wrote in message
news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> Is it possible to use a SELECT statement as the expression in an IIF
function?
> I Cannot get it to work.
> i.e. IIF((SELECT COUNT(column) FROM table WHERE column => Fields!Variable.Value) > 0, "BAD","GOOD")
> --
> Thanks in advance
> GH|||You should do this in "Generic Query Designer"
"saglamtimur" <bsaglamtimur@.mayanet.com.tr> wrote in message
news:OExB7I#2EHA.2804@.TK2MSFTNGP15.phx.gbl...
> ="select count (column) from table where
> column='"+iif(fields!variable.value>0, "BAD","GOOD")+"'"
> hope works
> regards
>
> "GH" <vakar@.community.nospam> wrote in message
> news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> > Is it possible to use a SELECT statement as the expression in an IIF
> function?
> >
> > I Cannot get it to work.
> >
> > i.e. IIF((SELECT COUNT(column) FROM table WHERE column => > Fields!Variable.Value) > 0, "BAD","GOOD")
> > --
> > Thanks in advance
> > GH
>|||Thanks guys.
I had to get 'creative' with my data set ... not sure of the cost ... but
getting to where I want to go.
Thanks again.
"saglamtimur" wrote:
> You should do this in "Generic Query Designer"
>
> "saglamtimur" <bsaglamtimur@.mayanet.com.tr> wrote in message
> news:OExB7I#2EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > ="select count (column) from table where
> > column='"+iif(fields!variable.value>0, "BAD","GOOD")+"'"
> >
> > hope works
> >
> > regards
> >
> >
> > "GH" <vakar@.community.nospam> wrote in message
> > news:90A8523F-42B6-41DB-BB71-DD78F77C8B18@.microsoft.com...
> > > Is it possible to use a SELECT statement as the expression in an IIF
> > function?
> > >
> > > I Cannot get it to work.
> > >
> > > i.e. IIF((SELECT COUNT(column) FROM table WHERE column => > > Fields!Variable.Value) > 0, "BAD","GOOD")
> > > --
> > > Thanks in advance
> > > GH
> >
> >
>
>

IIF Datediff in SQL SERVER

Hi,
I am trying to build a view in SQL server. I have a function in Access
which looks like this:
Breach:
IIf(DateDiff("n",[PP_ARRIVAL_DATE],[PP_DISCHARGE_DATE])>240,"Breach","Non
Breach")

>From reading it is clear that the IIF statement is not available in
SQLServer what do i need to use to produce the same results in
SQLServer?
ThanksSELECT CASE WHEN DATEDIFF(MINUTE, PP_ARRIVAL_DATE, PP_DISCHARGE_DATE) > 240
THEN 'Breach' ELSE 'Non Breach' END
http://www.aspfaq.com/2214
"yariso" <john.campbell600@.ntlworld.com> wrote in message
news:1124112204.794604.128840@.z14g2000cwz.googlegroups.com...
> Hi,
> I am trying to build a view in SQL server. I have a function in Access
> which looks like this:
> Breach:
> IIf(DateDiff("n",[PP_ARRIVAL_DATE],[PP_DISCHARGE_DATE])>240,"Breach","Non
> Breach")
>
> SQLServer what do i need to use to produce the same results in
> SQLServer?
> Thanks
>|||Great stuff thanks