|
thanks for ur valuable,meaningful and expertise reply................................................
regards
sindhu tiwari
its me sid
|
|
|
|
|
need help Guys.,
how can i convert money to varchar, to add some prefix. having problem with the below statement.
Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount],
The Complete Sproc is
USE FastLoan_Commissions
GO
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[upExceptions_GetPending]<br />
@UserID varchar(10)<br />
AS<br />
BEGIN<br />
<br />
SELECT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
NULL [ExceptionDays],<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID<br />
AND LS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID IN (1,2)<br />
AND E.LoanIncreaseID IS NULL<br />
<br />
UNION ALL<br />
<br />
--UPFRONT LOAN INCREASES<br />
SELECT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'Loan Increase' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LIS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
LI.IncreaseAmount [Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
NULL [ExceptionDays],<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID<br />
AND LIS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID IN (1,2)<br />
AND E.LoanIncreaseID IS NOT NULL<br />
<br />
<br />
UNION ALL<br />
<br />
--TRAIL LOANS<br />
SELECT DISTINCT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END [Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
E.ExceptionDays,<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID<br />
AND LS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID = 3<br />
AND E.LoanIncreaseID IS NULL<br />
AND CA.ApplicantTypeID = 1<br />
<br />
UNION ALL<br />
<br />
--TRAIL LOAN INCREASES<br />
SELECT DISTINCT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'Loan Increase' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LIS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
LI.IncreaseAmount [Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
E.ExceptionDays,<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID<br />
AND LIS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID = 3<br />
AND E.LoanIncreaseID IS NOT NULL<br />
AND CA.ApplicantTypeID = 1<br />
<br />
ORDER BY TradingName, SettlementDate<br />
--CRT.CommissionRunType, CR.CommissionRunDate, SettlementDate<br />
END
Thanks in Advance
|
|
|
|
|
it seems like to me that you would do something like this instead
Case When Isnull(LP.RefinanceAmount, '')='' Then cast(L.LoanAmount as varchar(20))
Else 'N.F' + cast(LP.RefinanceAmount as varchar(20)) END [Amount],
|
|
|
|
|
still got the same error, but anyway thanks for the reply
|
|
|
|
|
Hi friends![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
I want to write a code for restor my database backup. I want to write it in SQL procedure. Whaat I must do?
thanks
|
|
|
|
|
|
If I recall, there should be a stored proc already there to to backups and restores. I could be wrong, though.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
I am trying to connect to sql express server but i keep getting the following error "Login Failed for user 'ESELD\pit'. The user is not associated with a trusted sql server connection. Can anyone help?
|
|
|
|
|
Have a look at this article[^].
Paul Marfleet
|
|
|
|
|
Isn't the problem that your Windows account doesn't have permissions to access the SQL Server?
The article explains how to grant these permissions.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Make it a trusted sql server connection. Google can tell you how.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Hello folks,
I am a Newbie. I know I could just do this dynamically, however I am looking for any possible way to keep as much stuff declared at design-time as possible (just a personal preference).
I want to do as is illustrated below -- using the 'Employees' table from the 'Northwind' database. My environment is VS.NET 2005 with SQL server 2005, language is C# and page is ASP.NET.
Basically, I have a DropDown with "City" and "LastName" as the choices, next to a TextBox. The user picks either of the field names in the DropDownList and then types a search filter in the TextBox. I want to use a Parameter to specify the column name and a Parameter to specify the value to search with a WHERE clause. The value is replaced with its appropriate SelectParameter but not the field to search, i.e. the code below is not working:
Default.aspx:
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem>City</asp:ListItem>
<asp:ListItem>LastName</asp:ListItem>
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" Width="220px"></asp:TextBox><br />
<br />
<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False"
DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" InsertVisible="False"
ReadOnly="True" SortExpression="EmployeeID" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [City] FROM [Employees] WHERE ([@Field] LIKE @Value)">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="Field" PropertyName="SelectedValue" Type="String"/>
<asp:ControlParameter ControlID="TextBox1" Name="Value" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Is there something I am doing wrong? Am I not allowed to say, i.e. SelectCommand="SELECT [field1] [field2] FROM [mytable] WHERE [@param] = @value" where @param upon replacement, specifies the field's name? It is not getting replaced, so naturally SQL Server complains with the error:
Invalid column name '@Field.'
What's up? Why cannot I use a parameter when specifying a field in a WHERE clause?
-- modified at 13:21 Wednesday 31st October, 2007
|
|
|
|
|
This article[^] should give you the information you need.
Paul Marfleet
|
|
|
|
|
I have this query that works as it is. I want to modify it so that I do not see Alarm errors less than 50.
I guess it would have to be a sub query to this;
SELECT WaysideName, ST, AlarmMessage, COUNT(AlarmMessage) AS Error_Count, CONVERT(char(10), EventDate, 111) as ErrorDate
FROM AlarmsList
where EventDate >@StartDate and EventDate <dateadd(day, 1,@enddate)="" and="" alarmmessage="" not="" like="" ('%off')="" ('%restored')="" <=""> 'OPERATIONAL AND HEALTHY'
GROUP BY WaysideName, ST, AlarmMessage, CONVERT(char(10), EventDate, 111)
Thanks,
|
|
|
|
|
Try this:
SELECT WaysideName, ST, AlarmMessage, COUNT(AlarmMessage) AS Error_Count, CONVERT(char(10), EventDate, 111) as ErrorDate
FROM AlarmsList
where EventDate >@StartDate and EventDate 'OPERATIONAL AND HEALTHY'
GROUP BY WaysideName, ST, AlarmMessage, CONVERT(char(10), EventDate, 111)
HAVING COUNT(AlarmMessage) >= 50
Adding the HAVING clause modifies the query so that only rows with an alarm message count >=50 are included in the output.
Paul Marfleet
|
|
|
|
|
Thanks Paul. That did the trick. Boy was that simple!
|
|
|
|
|
Thanks. The important thing to remember here is that the WHERE and HAVING clauses provide different ways of filtering the output of a query.
A WHERE clause is used to filter non-aggregated data.
A HAVING clause is used in conjunction with a GROUP BY clause to filter data aggregated in that GROUP BY clause.
Paul Marfleet
|
|
|
|
|
Thank You.
It is always a good day when you learn something new!
Regards,
Brian
|
|
|
|
|
Hi!
I have the following two stored proceures:
SP1:
Create PROCEDURE SP1
@arg1 varchar(100),
@arg2 varchar(100) out
AS
BEGIN
SET @arg2=arg1+': SP1 Data'
return
END
SP2:
Create PROCEDURE SP2
AS
BEGIN
Declare @argloc varchar(100)
EXEC SP1 'SP2 Data',@argloc
print @argloc
END
On calling SP2 i want result like:
SP2 Data: SP1 Data
But it returns:
SP2 Data
How can can i fix this problem?
Thanks!
|
|
|
|
|
This should work:
Create PROCEDURE SP2
AS
BEGIN
Declare @argloc varchar(100)
EXEC SP1 'SP2 Data',@argloc OUTPUT
print @argloc
END
You needed to mark @argloc with the OUTPUT keyword in order for this variable to receive the output value from SP1.
Paul Marfleet
|
|
|
|
|
I tried and it succeeded because of your nice suggestion.
Thanks!
|
|
|
|
|
That's OK. As long as my advice was helpful.
Paul Marfleet
|
|
|
|
|
Is there any mechanism to refresh a dataset automatically when the data source ,lets say
a table in SqlServer, is updated, deleted or inserted. No periodic fill or execuete a sql statement please.
Thanks
|
|
|
|
|
muharrem wrote: No periodic fill or execuete a sql statement please.
Why not?
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
I don't know when the original data source is updated by other user or
an other aplication.
My project is going to be part of a plant automation system. So the events from the
plant level my arise in very short time duration or too long. It depends on the condition
at that moment. So if I periodically fill my data set, what will be the period?
It is very difficult to gess id advance. Having it too short will bring unnecessary workload
for the system. Or having it too long will cause to miss the some indermediate update in
the data source.
I am not very good on Ado.Net. I need to know ,if possible, can I have my client application
to refresh its dataset (automatically) from the data source when it is updated.
Thanks for reply, anyhow
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
