|
Exchange the ? with a "to_date" function.
For example: to_date('2009/07/09', 'yyyy/mm/dd') would return a date value of July 9, 2009.
There's more on the formatting here[^]
|
|
|
|
|
SELECT current_date
FROM dual; or
SELECT SYSDATE FROM dual;
|
|
|
|
|
Hi All,
I have one table containing few columns.
LruName, ChName and Type.
I want to fetch a query from the above table
order by LruName, ChName, Type
by using the above query I am getting output like this
-------------------------------------------------------------------------------------------------------
LruName ChName Type
ADU1 CH1 Tx
ADU2 CH2 Tx
AHRS1 CH3 Tx
AHRS2 CH4 Tx
DVS CH24 Rx
DVS CH28 Rx
DVS CH5 Tx
DVS CH6 Tx
FADEC1 CH1 Tx
FADEC1 CH2 Tx
FADEC2 CH3 Tx
FADEC2 CH4 Tx
NLDW CH8 Tx
OAS CH7 Tx
----------------------------------------------------------------------------------------------------------
But I want output like this
----------------------------------------------------------------------------------------------------------
LruName ChName Type
ADU1 CH1 Tx
ADU2 CH2 Tx
AHRS1 CH3 Tx
AHRS2 CH4 Tx
DVS CH5 Tx
DVS CH6 Tx
DVS CH24 Rx
DVS CH28 Rx
FADEC1 CH1 Tx
FADEC1 CH2 Tx
FADEC2 CH3 Tx
FADEC2 CH4 Tx
NLDW CH8 Tx
OAS CH7 Tx
ie I want LruName, Chname and Type to be order wise
again in Type Tx ( rows ) should come first then Rx.
how to write a query by getting the above results.
|
|
|
|
|
if the Type field is limited to the values in your example, then a descending order would do it, hence:
order by LruName, ChName, Type DESC
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Hi,
thank u for the reply.
But i want Tx rows should come first then Rx rows
|
|
|
|
|
as Tx > Rx and you want Tx first, descending should do it.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
It is not working
I gave like this
ORDER BY LruName, ChName, Type desc
|
|
|
|
|
in your example the Type isn't really relevant to the sort as all (LruName,ChName) combinations are unique already without the Type field.
So maybe you want
order by LruName, Type DESC, ChName
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
yaah... it is working
I got what I want
thank u very much.
|
|
|
|
|
One of the problems you are going to have is with the ChName column. It appears as though you want CH5 to appear before CH24. In your order by clause you may have to have it
order by .... ,length(ChName),ChName, ...
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Hi,
Luc Pattyn
solved my problem
thank you for the reply
|
|
|
|
|
That's a good solution for a common problem.
A five from me even if it wasn't what the OP wanted.
|
|
|
|
|
Thanks Jörgen.
Jörgen Andersson wrote: even if it wasn't what the OP wanted.
Oddly, in the original post he indicated what output he was after and so I'm pretty sure he is going to have to do something similar to what I offered.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I thought so too, and was contemplating looking up a stringsplit function that does the same job in a much more tortuous way.
|
|
|
|
|
I have a feeling it's only going to work if all the name values start with the same prefix.
Supposing I have:
A1
A20
A999
C1
C99
Sorting them by length(name), name would give:
A1
C1
A20
C99
A999
So it would solve the OP's specific problem where all names appear to start with CH but I'm not sure it works as a general solution.
|
|
|
|
|
I believe you're quite correct.
We have exactly this issue and have solved it with a separate sortcolumn.
But this isn't always a viable solution, and then one has to build a natural sort function[^].
|
|
|
|
|
just the following,-
order by LruName, ChName, Type DESC
|
|
|
|
|
When a variable in my stored procedure contains an apostrophe, it returns me an error.
I've tried the function REPLACE() but no luck.
waiting for your suggesions.
Thanks
|
|
|
|
|
berba wrote: I've tried the function REPLACE() but no luck.
waiting for your suggesions.
I suggest you post the error-message that you got when you tried that function, or the code that you tried.
I are Troll 
|
|
|
|
|
Without seeing the code that uses the variable and how the variable is applied to the code, it's difficult to know what is happening. And also knowing what the error return is will help out as well.
Just a guess, but usually embedded apostrophes within strings will need to be escaped somehow.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
|
CREATE PROCEDURE sp_GetEZQueryList @@sUser as nchar(500), @@sSupervisor as char(1), @@sAllowed as varchar(8000) AS
--set @@sUser = REPLACE(@@sUser, '''''','''''')
IF @@sSupervisor <> 'Y'
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
WHERE (EzqUserName=@@sUser)
OR (EzqUserName='SYSTEM' AND Cast(EzqFlxID as varchar(10)) IN (select * FROM intlist_to_tbl(@@sAllowed)))
ELSE
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
GO
The SP is retrieving @@sUser from VB and its getting the value Do'Sullivan.
When I ran a trace I found it is executing the SP like this
exec sp_getezquerylist 'DO' + CHAR(39) + 'SULLIVAN', 'n', '100'
Think + CHAR(39) + is causing the error
Thanks
|
|
|
|
|
CREATE PROCEDURE sp_GetEZQueryList @@sUser as nchar(500), @@sSupervisor as char(1), @@sAllowed as varchar(8000) AS
--set @@sUser = REPLACE(@@sUser, '''''','''''')
IF @@sSupervisor <> 'Y'
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
WHERE (EzqUserName=@@sUser)
OR (EzqUserName='SYSTEM' AND Cast(EzqFlxID as varchar(10)) IN (select * FROM intlist_to_tbl(@@sAllowed)))
ELSE
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
GO
The SP is retrieving @@sUser from VB and its getting the value Do'Sullivan.
When I ran a trace I found it is executing the SP like this
exec sp_getezquerylist 'DO' + CHAR(39) + 'SULLIVAN', 'n', '100'
Think + CHAR(39) + is causing the error
Thanks
|
|
|
|
|
|
Little Debugging..
CREATE PROCEDURE sp_GetEZQueryList
(
@sUser AS nchar(500) ,
@sSupervisor AS char(1) ,
@sAllowed AS varchar(8000)
)
AS
IF @sSupervisor <> 'Y'
SELECT
EzqDateMod ,
EzqFlxID ,
EzqUserName ,
EzqName ,
EzqData ,
EzqDescrip
FROM
EZQuery
WHERE
( EzqUserName = @sUser )
OR
( EzqUserName = 'SYSTEM' )
ELSE
SELECT
EzqDateMod ,
EzqFlxID ,
EzqUserName ,
EzqName ,
EzqData ,
EzqDescrip
FROM
EZQuery
GO
exec sp_getezquerylist 'DO''SULLIVAN', 'N', '100'
Executes fine, without the IN clase to look up
The source of your issues appear to be in this function (I assume its a function based on the nomenclature) intlist_to_tbl. If intlist_to_tbl is really a function why are you sending that data to the db, and converting it instead of storing it in the db?
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
