Database

30-Oct-07 14:03

hey Guys, i am having troubling with this query., i want to Assign the RefinanceAmount instead of LoanAmount if the LoanID exists in tblLoanPurpose(LoanID, RefinanceID, RefinanceAmount) which i joined the table.

the way i want is
L.LoanAmount = 'NewfundsAmt'+@RefinanceAmount

the whole Query is

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO

Anyone pls Help..

ALTER PROC [dbo].[upExceptions_GetPending]
@UserID varchar(10)
AS
BEGIN
Declare @LoanAmount money
Declare @RefinanceAmount money

SELECT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'' [LoanIncrease], --tooltip
A.ShortName,
LS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
L.LoanAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
NULL [ExceptionDays],
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID
AND LS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID IN (1,2)
AND E.LoanIncreaseID IS NULL

UNION ALL

--UPFRONT LOAN INCREASES
SELECT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'Loan Increase' [LoanIncrease], --tooltip
A.ShortName,
LIS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
LI.IncreaseAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
NULL [ExceptionDays],
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID
AND LIS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID IN (1,2)
AND E.LoanIncreaseID IS NOT NULL

UNION ALL

--TRAIL LOANS
SELECT DISTINCT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'' [LoanIncrease], --tooltip
A.ShortName,
LS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
L.LoanAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
E.ExceptionDays,
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID
AND LS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID = 3
AND E.LoanIncreaseID IS NULL
AND CA.ApplicantTypeID = 1

UNION ALL

--TRAIL LOAN INCREASES
SELECT DISTINCT E.ExceptionID,
I.IntroducerID,
I.TradingName,
E.LoanID,
'Loan Increase' [LoanIncrease], --tooltip
A.ShortName,
LIS12.StageDate [SettlementDate],
CRT.CommissionRunType,
CR.CommissionRunDate,
ET.ExceptionType [Reason],
LI.IncreaseAmount [Amount],
ISNULL(L.CurrentBalance, 0) [CurrentBalance],
E.ExceptionRate,
E.ExceptionDays,
E.ExceptionAmount,
NULLIF(E.ExceptionGST, 0) [ExceptionGST],
E.ExceptionTotal
FROM tblCommissionRun CR
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID
AND LIS12.LoanStageTypeID = 6
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID
WHERE CR.Pending = 1
AND CR.CommissionRunTypeID = 3
AND E.LoanIncreaseID IS NOT NULL
AND CA.ApplicantTypeID = 1

ORDER BY TradingName, SettlementDate
--CRT.CommissionRunType, CR.CommissionRunDate, SettlementDate
END

Marek Grzenkowicz30-Oct-07 23:40

Marek Grzenkowicz

30-Oct-07 23:40

Is this problem somewhat similar to this one [^]?

Do you need to add a prefix to L.LoanAmount?

[Please post an answer to my post not yours so I can get an e-mail notification when you do so.]

Giri K31-Oct-07 14:41

31-Oct-07 14:41

First of all thanks to your response.,
yes, i need to add the prefix, if the RefinanceAmount is exists., i.e Ref.Amt 10000.00 otherwise just display the LoanAmount'. Just to find out which amount is that.

Thanks Heaps

Marek Grzenkowicz4-Nov-07 22:25

Marek Grzenkowicz

4-Nov-07 22:25

Do you mean something like this?

DECLARE @foo money
SET @foo = 10.02
SELECT 'PREFIX ' + CAST(@foo AS varchar(10))

Giri K5-Nov-07 17:53

5-Nov-07 17:53

Yeah, exactly thats all i want!! I tried it in different ways but couldn't succeed Confused | :confused:

Thanks a lot Buddy!!

Giri K31-Oct-07 14:51

SQL Server Express Autoinc

31-Oct-07 14:51

yeah, thats the same one

AAGTHosting30-Oct-07 12:18

AAGTHosting

30-Oct-07 12:18

I created a table in SQL Express with an autoinc column. When I remove the autoinc the table will create, but when I make the id column an auto increment it will not creawte the table.

Does anyone know why?

Is there aqnother way to auto increment with sql express?

Here is the table.

create table tbl_teachers (<br />
teach_id int AUTOINC NEXTVALUE 1 STEP 1,<br />
teach_fname text,<br />
teach_lname text,<br />
teach_add text,<br />
teach_unit_num text,<br />
teach_city text,<br />
teach_state text,<br />
teach_zip int,<br />
teach_instruments text,<br />
teach_email text,<br />
teach_phone int,<br />
teach_cell int,<br />
primary key(teach_id));

Here is the error I get.

Msg 102, Level 15, State 1, Line 3<br />
Incorrect syntax near 'AUTOINC'.

Re: SQL Server Express Autoinc

pmarfleet30-Oct-07 13:01

pmarfleet

30-Oct-07 13:01

AUTOINC NEXTVALUE is Visual FoxPro syntax. IDENTITY is the SQL Server equivalent.

Paul Marfleet

SqlClient will not connect to 2005 SqlServer

ianhoc30-Oct-07 10:46

ianhoc

30-Oct-07 10:46

OleDb connects fine?

VS2005; C#; SQL 2005 Developer (remote server)

SqlClient connection string:
"data source=MyRemoteServer;user id=MyUserID;password=MyPassword;initial catalog=DbName;workstation id=PC1"

OleDb connection string:
"provider=SQLxxxx;data source=MyRemoteServer;user id=MyUserID;password=MyPassword;initial catalog=DbName;workstation id=PC1"
(xxxx = I forget the exact provider string)

OleDb connects fine, SqlClient times out looking for the server?

Am I missing something obvious? Should I really worry about this - I thought SqlClient was faster and better?
Thanks
Ian

Re: SqlClient will not connect to 2005 SqlServer

Paul Conrad31-Oct-07 14:13

Paul Conrad

31-Oct-07 14:13

Did you get this to work?

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Please Vote For Persian Collation in SQL Server 2008

DotNetWWW30-Oct-07 8:05

DotNetWWW

30-Oct-07 8:05

Hi ,
All my fellow Iranians , Please Vote for this!
https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=307273

Re: Please Vote For Persian Collation in SQL Server 2008

Paul Conrad31-Oct-07 14:14

Paul Conrad

31-Oct-07 14:14

You should learn how to do a clickety link here on CP (it will keep people from having to copy the URL and paste in the address bar). Highlight the URL and press Ctrl+L Big Grin | :-D

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Basic question on SQL [modified]

niaher30-Oct-07 4:35

niaher

30-Oct-07 4:35

Hello. I have a very basic question (at least for most of you pros out there Smile | :)

). Are stored procedures executed in isolation - meaning that, while one SP is being executed no other SP or query can execute on the same record.

More specifically I would like to know if there is a chance that during the execution of the below SP the value of @@ROWCOUNT can be changed on line 16 by another SP or query (in which case the below SP is under the risk of returning a wrong value).

 1: CREATE PROCEDURE ProductsUpdate
 2: (
 3:         @ProductID int,
 4:         @Name varchar(40),
 5:         @Concurrency timestamp
 6: )
 7: AS
 8:     UPDATE
10:         Products
11:     SET
12:         ProductName = @Name
13:     WHERE
14:         ProductID = @productID AND
15:         Concurrency = @Concurrency
16:     -- Is it possible to have another SP or query change @@ROWCOUNT at this point?
17:     return @@ROWCOUNT

Also I was wondering about the same question but with generic queries, like below:

1: UPDATE
2:     Products
3: SET
4:     ProductName = @Name
5: WHERE
6:     ProductID = @productID AND
7:     Concurrency = @Concurrency;
8: -- Can any other query or SP get in here and alter @@ROWCOUNT?
8: SELECT @@ROWCOUNT

Thank you for your help Wink | ;)

Niaher

-- modified at 10:52 Tuesday 30th October, 2007

andyharman30-Oct-07 7:04

andyharman

30-Oct-07 7:04

Hi Niaher

The @@ROWCOUNT variable (and others like @@ERROR) are local to each database connection. Your stored procedure can use this to implement concurrency control.

Regards
Andy

niaher30-Oct-07 7:49

niaher

30-Oct-07 7:49

Thank you. So what about a generic query with multiple statements, does it have a single connection? Can I use them to implement my concurrency control?

andyharman30-Oct-07 9:50

andyharman

30-Oct-07 9:50

Yes you can use them for concurrency control. Which development language are you using?

niaher30-Oct-07 19:41

niaher

30-Oct-07 19:41

Using C#. Btw I know it is a very controversial question, but what is a better choice performance-wise, a stored procedure or a dynamic query? In my case I will have not more than 15 tables and my goal is to support concurrecy in insert, update and delete logic, be it in SPs or dynamic queries.

Niaher