Database Discussion Boards

Re: Numeric Column

Krish - KP31-Oct-07 0:35

Krish - KP

31-Oct-07 0:35

check is this what you are looking for

<br />
SELECT <br />
CASE WHEN Len('00000000' + LTrim(str(myid))) > 9 THEN <br />
	SubString(<br />
		'00000000' + LTrim(Str(myid)), <br />
		Len('00000000' + LTrim(Str(myid))) - 9, <br />
		Len('00000000' + LTrim(Str(myid)))<br />
	) END<br />
FROM tableA<br />

Regards
KP

Re: Numeric Column [modified]

GuyThiebaut31-Oct-07 3:23

GuyThiebaut

31-Oct-07 3:23

Hi Kiran,

The code below will insert '15' into ten zeroes and keep it right justified.
You will just need to jiggle things a bit substituting your number for '15'

<br />
select stuff('0000000000',11-len('15'),len('15'),'15')<br />

Or even better

<br />
select stuff(replicate('0',10),11-len(cast(15 as varchar(100))),len(cast(15 as varchar(100))),cast(15 as varchar(100)))<br />

I hope this is of help.

Guy

You always pass failure on the way to success.

Re: Numeric Column

ianhoc31-Oct-07 7:04

ianhoc

31-Oct-07 7:04

I think this is easier:

SELECT RIGHT('00000000' + Ltrim(str(MyKey)), 8)
From Table

Ian

Need an Article about Replication.

hdv21230-Oct-07 23:22

hdv212

30-Oct-07 23:22

hi i'm looking for an article that explain replication good, can u give me a link for that article ?
thanks

.Net

amsathu30-Oct-07 23:20

amsathu

30-Oct-07 23:20

please give me asp.net sample program with ado.net

send through mail

amsathu

Re: .Net

il_masacratore30-Oct-07 23:36

il_masacratore

30-Oct-07 23:36

... Why don't you search google??

Visit my blog at http://dotnetforeveryone.blogspot.com

Re: .Net

Giorgi Dalakishvili30-Oct-07 23:40

Giorgi Dalakishvili

30-Oct-07 23:40

...Or even CodeProject??

#region signature
my articles
#endregion

Re: .Net

Paul Conrad31-Oct-07 14:12

Paul Conrad

31-Oct-07 14:12

How about doing a search here on CP? There are plenty of examples Big Grin | :-D

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

Getting the columns

M. J. Jaya Chitra30-Oct-07 22:31

M. J. Jaya Chitra

30-Oct-07 22:31

Can anyone tell me how to retrieve the column names in a table?

Best Regards,
M. J. Jaya Chitra

Re: Getting the columns

Giorgi Dalakishvili30-Oct-07 22:47

Giorgi Dalakishvili

30-Oct-07 22:47

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'

#region signature
my articles
#endregion

Re: Getting the columns

r a j u u30-Oct-07 23:04

r a j u u

30-Oct-07 23:04

ist working? i tried this query but its saying Invalid columnname...

Rajendran

Re: Getting the columns

Giorgi Dalakishvili30-Oct-07 23:09

Giorgi Dalakishvili

30-Oct-07 23:09

Yes, it is. Just make sure you are performing the query against the database that contains your table

#region signature
my articles
#endregion

Re: Getting the columns

M. J. Jaya Chitra31-Oct-07 0:06

M. J. Jaya Chitra

31-Oct-07 0:06

Thanks a lot. It is working fine.

Best Regards,
M. J. Jaya Chitra

Re: Getting the columns

Giorgi Dalakishvili31-Oct-07 0:30

Giorgi Dalakishvili

31-Oct-07 0:30

You are welcome Smile | :)

#region signature
my articles
#endregion

Re: Getting the columns

joemonvarghese1-Nov-07 23:32

joemonvarghese

1-Nov-07 23:32

hi
try this qry

select name from syscolumns where id=
(select id from sysobjects where name=)

Re: Getting the columns

r a j u u30-Oct-07 23:12

r a j u u

30-Oct-07 23:12

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

Re: Getting the columns

Krish - KP30-Oct-07 23:25

Krish - KP

30-Oct-07 23:25

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

Re: Getting the columns

Senu Gandhi31-Oct-07 1:56

Senu Gandhi

31-Oct-07 1:56

Hi Jaya,

For a simple solution try this

sp_help ''

Senthil

can MySQL Server 5.0 support command " sp_who" and "sp_who2"?

skyleo_200830-Oct-07 21:09

skyleo_2008

30-Oct-07 21:09

thank for your help

Re: can MySQL Server 5.0 support command " sp_who" and "sp_who2"?

N a v a n e e t h30-Oct-07 21:20

N a v a n e e t h

30-Oct-07 21:20

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.

All C# applications should call Application.Quit(); in the beginning to avoid any .NET problems.- Unclyclopedia

My Website | Ask smart questions

Re: can MySQL Server 5.0 support command " sp_who" and "sp_who2"?

skyleo_200830-Oct-07 22:20

skyleo_2008

30-Oct-07 22:20

thank for help.it's work Smile | :)

Query

Giri K30-Oct-07 14:03

Giri K

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

Re: Query

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.]

Re: Query

Giri K31-Oct-07 14:41

Giri K

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

Re: Query

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))

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.