Requetes divers sur SYBASE ASE

1

Autojointure à regroupement :

SELECT col1, col2, count(*)

FROM tab

GROUP BY col1, col2

ORDER BY col1, col2

compute sum(count(*)) BY col1

compute sum(count(*))

 

 

Modification format de date :

 

02/02/02 en 02-02-02 :

select object_name(id), stuff(stuff(convert(varchar, moddate, 102), 5, 1, '-'), 8, 1, '-') + 'T' + convert(char(8), moddate, 108),

 

 

Feb 7 2005 12:53PM en 20050207121240

convert(varchar, moddate, 112) + substring(convert(char(8), moddate, 108) , 1, 2) + substring(convert(char(8), moddate, 108) , 4, 2) + substring(convert(char(8), moddate, 108) , 7, 2)

 

 

 

Commandes sur la gestion des dates

2 format de data différents

declare @dat datetime, @dat2 smalldatetime
select @dat = getdate()
select @dat2 = getdate()
select @dat, @dat2


Difference entre 2 dates :

declare @dat1 datetime, @dat2 datetime
select @dat1 = 'Dec 30 2005'
select @dat2 = getdate()
select @dat1, @dat2
select datediff (hh,@dat1,@dat2)


Ajout 1 heure :

declare @dat1 datetime, @dat2 datetime
select @dat1 = 'Dec 30 2005'
select @dat2 = getdate()
select @dat1, @dat2
select dateadd (hh,+1,@dat2)


Affichage avec un format particulier

select convert (char(26),getdate(),108)

 

Affichage de la date du jour :

- 3 mois : select convert (smalldatetime, Dateadd (mm, -3, getdate()))

- 2 jours : select convert (smalldatetime, Dateadd (dd, - 2, getdate()))



Mise a jour des threshold

select "use "+db_name(dbid)+char(10)

+"go"+char(10)+

"sp_addthreshold @dbname='"+db_name(dbid)+"',@segname='logsegment',@free_space="+ convert( char(8), sum(size)*10/100)

+" , @proc_name='sp_thresholdaction' "+

char(10)+"go"+char(10) as "--seuil 30%"

from master..sysusages where segmap=4 group by db_name(dbid)



Utilisation de patindex et substring :

extraire dans S_AnoDescription, les 50 caractères après la chaine @ModuleName.

select distinct

G_PROCESSUS.G_PrsName,S_LOG.G_PrsID,

Substring(convert (varchar(1500),S_AnoDescription), patindex('%@ModuleName%',S_ANOMALY.S_AnoDescription) ,50 ),

S_ANOMALY.S_AnoID --,S_ANOMALY.S_LogID

from S_ANOMALY, S_LOG, G_PROCESSUS

where S_AnoCode in (506, 5003) AND

S_ANOMALY.S_LogID = S_LOG.S_LogID

AND G_PROCESSUS.G_PrsID= S_LOG.G_PrsID




Visualisation des Logins Externes :

SELECT  Server = s.srvname,

            Login = l.name,

            Externlogin = a.object_cinfo

        FROM    master.dbo.sysattributes a,

            master.dbo.sysservers s,

            master.dbo.syslogins l

        WHERE   a.class = 9 AND

            a.object_type = "EL" AND

            a.object_info1 = s.srvid AND

--          s.srvname LIKE @server AND

            a.object = l.suid