|
I think this is easier:
SELECT RIGHT('00000000' + Ltrim(str(MyKey)), 8)
From Table
Ian
|
|
|
|
|
hi
i'm looking for an article that explain replication good, can u give me a link for that article ?
thanks
|
|
|
|
|
please give me asp.net sample program with ado.net
send through mail
amsathu
|
|
|
|
|
... Why don't you search google??
Visit my blog at http://dotnetforeveryone.blogspot.com
|
|
|
|
|
How about doing a search here on CP? There are plenty of examples 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Can anyone tell me how to retrieve the column names in a table?
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
If you are using sql server then you can use the following query:
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'name_of_the_table'
|
|
|
|
|
ist working? i tried this query but its saying Invalid columnname...
Rajendran
|
|
|
|
|
Yes, it is. Just make sure you are performing the query against the database that contains your table
|
|
|
|
|
Thanks a lot. It is working fine.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
hi
try this qry
select name from syscolumns where id=
(select id from sysobjects where name=)
|
|
|
|
|
In SQL,
Assume columnname- id(It should be identity and autoincrement),Name
then you will try this query
select name from tablename where id=0
Rajendran
|
|
|
|
|
In MS SQL Server syscolumns table holds column names and sysobjects table names.
SELECT * <br />
FROM syscolumns co<br />
INNER JOIN sysobjects so ON co.id = so.id<br />
WHERE so.name = 'mytable'
Regards
KP
|
|
|
|
|
Hi Jaya,
For a simple solution try this
sp_help ''
Senthil
|
|
|
|
|
sp_who/sp_who2 is not available in MySql. Use
SHOW FULL PROCESSLISTS , this shows which threads are running. Alternatively you can use MySql Administrator(comes with MySQL GUI Tools) to know the users and processes.
|
|
|
|
|
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
|
|
|
|
|
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.]
|
|
|
|
|
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
|
|
|
|
|
Do you mean something like this?
DECLARE @foo money
SET @foo = 10.02
SELECT 'PREFIX ' + CAST(@foo AS varchar(10))
|
|
|
|
|
Yeah, exactly thats all i want!! I tried it in different ways but couldn't succeed
Thanks a lot Buddy!!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
