DLookup Equivalent In SQL Server

Jul 23, 2005

I am migrating a student database from Access to SQL Server. In Access
I have a query that displays grade information (grades are calculated
on a 12-point scale). In the query I average the students' scores and
store it in a column called Avg. I look up and display the equivalent
grade letter using Access' DLookup function from a table called
GradeTable_tbl. Here is how it's built in Access:
Grade: DLookUp("[grade_letter]","GradeTable_tbl","[grade_num]= " &
Int([Avg]))

Here is the structure of the GradeTable_tbl:


grade_num grade_letter
0 F
1 F
2 D-
3 D
..
..
..
10 B+
11 A-
12 A


How would I do the same thing in SQL Server? I want my output to be
something like:


Student Score1 Score2 Score3 Avg Grade
Bob 12 10 8 10 B+
Nancy 12 11 11 11 A-
etc...


I appreciate your feedback!

-Paul
------
"You never know enough to know you don't know"

View 3 Replies


ADVERTISEMENT

DLookUp

Mar 18, 2008

UPDATE 13_Together_Dec_final_ICNs SET [13_Together_Dec_final_ICNs].PL = Right(Nz(DLookUp("PH_LEVEL_02_CD","dbo_DIM_PRODUCT","PIN = '")),2)
WHERE ((([13_Together_Dec_final_ICNs].flag)="delete"));


How would I convert this to SQL SERVER 2005

View 2 Replies View Related

Doing A DLookup In ASP.NET Project To A SQL Database

Feb 8, 2008

I have a VB.NET Class called 'Approve' with a public function 'CanApprove' which returns a boolean value and accepts an input parameter of 'CurrentUserID' of type integer.
I want to use something like this that I have usedin Access with VBA:
lngID = Nz(DLookup("PermissionID", "tblPermissions", "ManagerID=" & UserID & " AND CurrentUserID=" & CurrentUserID & " AND Permission='A'"), 0)
So that if the person who is currently logged in has permissions over the person whose invoice he/she is viewing to approve, it will return a positive value so that if lngID > 0 then set CanApprove=false or vice versa.
How do I do this in ASP.net?

View 5 Replies View Related

DLookUp With Multi-value Field

May 13, 2014

I have a multi value field into which I have placed several different values. I wish to compare a value that is entered by the user with the multi value field, to find if the value entered is there. I am assuming ( and also hoping) that I can use DLookUp to do this. At the moment I get all of the multi values returned by DlookUp and they are separated by commas and the value that I want to find is in there. However this is giving me an error because of the separating commas. How can I isolate/extract the value that I want so that I can make a comparison ? Is it something like MyFieldName.Value?

View 12 Replies View Related

DLookup Function In TSQL

May 24, 2008



Hi all,


Access has a DLookup function that allow you to look up a value in another table based on a criteria, how is this functionality achieved in TSQL?


Thanks


Regards
Melt

View 7 Replies View Related

Need - Help To Create DLOOKUP With SSIS

Mar 17, 2006

Hi,

Does anybody knows how to create a DLOOKUP (dynamic lookup) in SSIS withour writing any kind of script?
I need to test records existance in destination from the source before inserting or updating in the destination (if the record exist in destination then update, else insert).

Any help apreciated.

View 1 Replies View Related

SQL Server Equivalent

Jul 9, 2004

I have 2 SQL statements in Visual Basic(with sybase as backend)
1) "set option DBA.MAX_STATEMENT_COUNT = 1069999900"
2) "set option DBA.MAX_CURSOR_COUNT = 1069999900"

And when I migrated the DB from Sybase to SQL server and try to run the vb code it is giving me error in that SQL statement as MS SQL server might not be recognising the above two statements. Is there an equivalent of this in SQL server.
Thanks

View 1 Replies View Related

Autonumber Equivalent In SQL Server?

Jul 13, 2004

Hi

So far, I have only used Access which has an autonumber data type so that in some of my tables the id is automatically generated.
I guess this is a simple question but is there an equivalent data type in sql server?

Thanks in advance.

View 1 Replies View Related

Equivalent Rights To SA In SQL Server 6.5......

Apr 21, 1999

In sql server 6.5, standard security......


Will i assign the SA rights to some one/login...?

wincy

View 1 Replies View Related

Equivalent Of Val() Function In SQL Server?

Mar 8, 2005

what is the equivalent of val() function in SQL server?
and cstr() function

View 4 Replies View Related

SQL Server Equivalent Of Character Set

Apr 30, 2007

Hello MSSQL professionals,

Please tell me the equavalent of the below MySQL query in SQL Server.

create table temp1(variable1 varchar(24) character set latin1 not null default '')
Immediate help will be highly appreciated

View 2 Replies View Related

What Is The SQL Server Equivalent To DESCRIBE

Nov 3, 2005

In ORACLE, I can use DESCRIBE PS_JOB to see the columns for that record. What is the SQL-Server equivalent?

TIA,

Joe

View 5 Replies View Related

Equivalent Query To Another Server

Jun 11, 2007

Hello all,
I'm making a query for this application that is not in the same server where the database is located.

This query I can easily get results when making the query within the server:

USE ADW_Publish;
SELECT distinct b.Subject
FROM dbo.F_Class_Exam a
LEFT OUTER JOIN dbo.D_Course_Catalog b
ON a.Course_ID = b.Course_ID
WHERE a.Term = '$536870994$' AND a.Class_Exam_Type = 'FIN' AND b.Term = '$536870994$'
ORDER BY b.Subject

Here are the details:
Servername: SERVER01

What would an equivalent query be if I apply this to an external application (located outside the server?)

I've gotten some hints to use:
SELECT *
FROM OPENDATASOURCE('MSDASQL', 'DataSource=DataSourceName;UserID=User;Password=SomePassword').DBName.dbo.TableName

But I dont know how to apply the query above to use that..

Any thoughts? Or are there other ways of doing it?

Thanks!

View 5 Replies View Related

Instr() Equivalent In SQL Server

Jul 20, 2005

I am trying to convert a complex function from Oracle to SQL Serverand have come across Oracle's Instr() function. I see SQL Server hasCHARINDEX() which is similar, however it does not provide some keyfunctionality I need. Here is an example of the Oracle code:if Instr( sTg , cDelim, 1, 3 ) > 0 thensd := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1, Instr( sTg,cDelim, 1, 2 ) - Instr( sTg , cDelim, 1, 1 ) - 1)end if;Has anybody converted anything similar to this within SQL Server? Anyhelp is GREATLY appreciated!Thanks.

View 5 Replies View Related

MS-SQL Server Equivalent To Oracle 9i?

Jul 20, 2005

All,Oracle 9i provides a "USING" clause option for inner joins, thatallows me to say:SELECT * FROM TBL1 JOIN TBL2 USING KeyColumnassuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirablefor our software make use of, but we also support SQL Server. Thereis no USING option available, andSELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumncauses an ambiguous column error on KeyColumn.Is there any equivalent to this Oracle functionality on SQL Server?KingGreg

View 7 Replies View Related

What Is DT_UI8 Equivalent Data Type In SQL Server 2005 For Integration Server

Aug 13, 2007

Hello,
I'm trying to build a integration service package importing data from XML files and directs this data to different MS SQL server 2005 Database tables.
Can someone suggest me what is equivalent(mapping) data type of DT_UI8 in Sql server 2005 for Integration Services.

Or how to consume DT_UI8 fields in SQL server 2005.

View 2 Replies View Related

Equivalent Of MS Access Captions In SQL Server?

May 22, 2007

Hi,
 I've been looking for the equivalent of the MS Access 2003 field caption property in SQL but it appears as though there isn't one.
(The caption property gives a friendly name on form labels rather than the field name)
How would I do this in SQL so I don't have to overtype all the column names?  Could it be with Views?
Any pointers appreciated - I'm moving & learning from Access to SQL

View 4 Replies View Related

Equivalent Of ORDER BY [field] IN (…) In SQL Server

May 19, 2005

Hi,
I have small question regarding ORDER BY clause.
 
I wanted some value of the fields should come first before other values….Something like:
 +----------+----------------------------------------+
| code     | name                                   |
+----------+----------------------------------------+
| UK       | United Kingdom                         |
| US       | United States                          |
| AF       | Afghanistan                            |
| AL       | Albania                                |
| DZ       | Algeria                                |
| AS       | American Samoa                         |
 
I want to display country UK and US code first and then rest of the countries using SQL statement. I know in MySQL, I can use ORDER BY code IN (...)
 
--SQL
SELECT * FROM countries ORDER by code IN ('UK', 'US') desc
 
But not sure how can I do same thing in SQL Server. Is there any equivalent of ORDER BY [field] IN (…) clause in SQL Server???
Though I can create a new field something like sequence and use it to display these countries but I can not do that…
Please advice.
 
Thanks,
 
Firoz Ansari
 

View 1 Replies View Related

What Is The SQL Server Equivalent Of Oracle's ROWNUM

Jan 15, 2001

Is there an equivalent to Oracle's ROWNUM in SQL Server. ROWNUM, when added to a select statement as a column - the query would return an automatic counter, numbering each row returned.

View 1 Replies View Related

Oracle MINUS Equivalent In Sql Server

Apr 22, 2003

Hi,
Is there something equivalent to the MINUS in ORacle ?
Or a workaround ?
thanks

View 11 Replies View Related

Ranking Equivalent In Sql Server 2000??

Sep 7, 2005

There are three columns I need to query and rank, then group and total. I'm using MS SQL server 2000 and it doesn’t have a ranking function like the newer SQL server and oracle has. Does anyone have a clever way to create or simulate a ranking function? Thanks so much!
Jake :confused:

View 1 Replies View Related

Can Any One Tell Me What Is The PL/SQL Connect By Operator Equivalent In SQL Server

Jan 9, 2002

I'm trying to get a tree structure from a table. Ex : Security table.
How can I accomplish that task in SQL Server?? I will do it using Level and connect by operators in Oracle to accomplish the task.
Your reply in this regard is highly appreciated.
Thanks,
Navy

View 1 Replies View Related

Is There A SQL Server Equivalent For Oracle Synonyms?

Jul 9, 2004

I have a SQL Server database which has one user (UserA) which owns some tables. I've added an additional user (UserB) to the database such that it has access to the tables owned by UserA. What is happening is that when I log on as UserB I have to fully qualify table names and fields in my SQL statements when I deal with tables owned by UserA. Is there a way make the tables accessible without specifying the owner? In Oracle you could create a public synonym for the table eg. <table_name>. Wherever that synonym is referenced the DBMS would know thats its refering to UserA.<table_name>. Is such functionality available in SQL Server? Thanks.

View 2 Replies View Related

Oracle's ROWNUM Equivalent In SQL Server

Sep 2, 2004

Hi,

Can any one tell me is there anything in SQL Server thats equivalent
to Oracle's ROWNUM.

Note that the Identity Property or TOP n will not solve my problem.

I want to asign a sequence no. to each row when its being fetched.

For example if in the emp table there are 2000 rows and I write
the following query in Oracle ,

SELECT rownum , empno, empname FROM emp Where rownum < =3

I get the result like this

Rownum----Empno--------------Empname
------------------------------------------
1-----------2345---------------ABCD
2-----------3334---------------EFGH
3-----------4484---------------IJKL


I know I can limit the output rows in SQL Server by using TOP n. But
I also want to generate a sequence no. The identity property of SQL Server
will not be usefull here because my actaul WHERE clause will be more
complex like WHERE resigndate = '01-jan-2004'

Thanks

Asim Naveed

3

View 2 Replies View Related

Row Triggers Equivalent In Sql Server 2000

Jan 19, 2007

Hello Guys!i have been working with oracle with quite a time. No i migrated to sqlserver 2000 and i want to create a trigger on a table.the trigger function has to update the Modification field to getdate()whenever a row is being updated.i tried lots of thingsif anyone can help i would appreciate a lot!Regards

View 2 Replies View Related

ORACLE BEFORE INSERT EQUIVALENT IN SQL SERVER

Jul 20, 2005

I am having trouble creating an INSTEAD OF trigger in SQL Server toreplicate a BEFORE UPDATE trigger from ORACLE.Here is a sample of the ORACLE BEFORE UPDATE trigger:CREATE TRIGGER myTRIGGER ON MYTABLEbegin:new.DT := SYSDATE;if :new.NM is NULL then:new.NM := USER;end if;end myTRIGGER;It seems as though I have to jump through hoops in SQL Server AND Icannot come up with correct results.Here is a snippet from SQL SERVER (this is what I figured I needed todo after reading various articles,questions):CREATE TRIGGER myTRIGGER on THETABLEINSTEAD OF UPDATEASSELECT * INTO #MYTABLE FROM INSERTEDUPDATE #MYTABLE SET DT = GETDATE()UPDATE #MYTABLE SET NM = USER WHERE NM IS NULLUPDATE THETABLESETDT = (SELECT DT FROM #MYTABLE),NM = (SELECT NM FROM #MYTABLE)WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)Can anyone please shed some light on this? Thanks in advance.

View 4 Replies View Related

Equivalent Of LIMIT Of MySQL In SQL Server 2005?

Aug 1, 2007

hey all,
i want to know Equivalent of "LIMIT" of MySQL in SQL Server 2005?
in mysql we can direclty get data using LIMIT clause.
my question is how to do this in sql server 2005?
 thanks.

View 6 Replies View Related

SQL Server Equivalent Of MS Access&#39;s &#34;Autonumber&#34; Field

Jan 17, 2001

Hi,

I have developed a web database application using ASP and MS Access, however the requirement for hosting the application is that it must use an MS SQL Server database. I converted the database to SQL without any problems, and many features of the application work under SQL Server except the 'add record' function. I realised there isn't an 'autonumber' field in SQL Server (which i use as the primary key for many tables), but an 'int' field. I considered pulling out the latest int from the database, incrementing it manually and adding the new record with this number... i also noticed there is a 'unique identifier' field.

Is there a quick solution for this problem?

Thanks

View 2 Replies View Related

SQL Server Equivalent For The Oracle Join Query

Nov 30, 2004

Hi,

Kindly give the SQL Server equivalent for the below Oracle query :

select *from t1, t2, t3 where
t1.t1col1 (+) = t2.t2col1 and
t2.t2col1 (+) = t3.t3col1

Thanks,
Sam

View 14 Replies View Related

Equivalent Of Oracle's INSTR( With 4 Parameters) In SQL Server

Feb 2, 2005

The syntax for Oracle's INSTR function is

instr (string1, string2, [start_position], [nth_appearance])

string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

nth_appearance is the nth appearance of string2. This is optional. If omiited, it defaults to 1.

In SQL Server, we are having CHARINDEX and PATINDEX functions. But they will not accept the fourth paremeter (nth_appearance)

Do anybody know the solution for this ????

View 1 Replies View Related

Oracle Translate Function Equivalent In SQL Server

May 5, 2006

Hi

I want to know the equivalent of the Oracle translate function in SQL Server.

eg : select translate('entertain', 'et', 'ab') from dual.

I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'.

Please let me know if there is some other equivalent function in SQL Server

thanks.

View 14 Replies View Related

There Is No Direct Equivalent To Oracle's Rownum Or Row Id In SQL Server

Sep 23, 2006

hi,

There is no direct equivalent to Oracle's rownum or row id in SQL Server

there is any possiblity are alternate thing is exist then please reply



regards

santosh

View 10 Replies View Related

Oracle Translate Function Equivalent In SQL Server

May 5, 2006

Hi



I want to know the equivalent of the Oracle translate function in SQL Server.



eg : select translate('entertain', 'et', 'ab') from dual.



I tried the SQL Server Replace function , but it replaces only
one character or a sequence of character and not each occurrence of
each of the specified characters given in the second argument i.e 'et'.



Please let me know if there is some other equivalent function in SQL Server



thanks.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved