|
hi guys
i have the followig query
select a.plhrs ,b.sthrs,a.mon
from
(select to_char(month,'Mon-yyyy') as mon,
sum(st) as plhrs from espcs.bud_mpl where empno='0541'
group by to_char(month,'Mon-yyyy'))a,
(select to_char(ddate,'Mon-yyyy') as mon2 , sum(sthrs) as sthrs
from espcs.manhrs where empno='0572' group by to_char(ddate,'Mon-yyyy')) b
where b.mon2 = a.mon
in this query i am getting sum values from both tables based on the months from
the table a,
but the problem is that it eliminates the records if it does not find the matching months from both tables, as i want that it shud show the month with a value of blan if it does not find the matching the matching months's records in the corresponding table,i want to use joins,,
i am new to oralce , can any body help me
thanks in advance
hello
|
|
|
|
|
you can use left join statement
select a.plhrs ,b.sthrs,a.mon
from
(select to_char(month,'Mon-yyyy') as mon,
sum(st) as plhrs from espcs.bud_mpl where empno='0541'
group by to_char(month,'Mon-yyyy'))a left join
(select to_char(ddate,'Mon-yyyy') as mon2 , sum(sthrs) as sthrs
from espcs.manhrs where empno='0572' group by to_char(ddate,'Mon-yyyy')) b
on b.mon2 = a.mon
Please ignore if have error as its fine for sql/access but not confirm for oracle.
Thanks
|
|
|
|
|
i create an object of sqldatasource then bind it
with gridview (using code c#) when i run the program it
ana navigate in gridview it make run time error saying(
gridview fired event pageindexchanging which wasnt handled)
how can i navigate in gridview using code (not wizard)
thanks
|
|
|
|
|
I have dealt with this problem before. Here is how I solved the problem. I used a proc that used the ROW_NUMBER() function. This way you can format the number of rows to show (page size) on your grid and show total pages as @totalRows/@PageSize
Example
CREATE PROC myPROC
(
@State char(2),
@PageSize tinyint,
@PageNumber smallint = 1,
@TotalRows smallint = 0 OUTPUT
)
AS
BEGIN
DECLARE @BeginRow int
DECLARE @EndRow int
SET @BeginRow = (@PageSize * (@PageNumber-1)) + 1
SET @EndRow = (@PageSize * (@PageNumber-1)) + @PageSize
SELECT RowId, FirstName, LastName
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY LastName, FirstName) AS RowId, FirstName, LastName
FROM
(
SELECT au_fname FirstName, au_lname LastName
FROM dbo.authors WHERE [state] = @State
) q1
) q2
WHERE RowId BETWEEN @BeginRow AND @EndRow
END
Dave Evans
|
|
|
|
|
I forgot to add SET @TotalRows = SELECT COUNT(*) FROM dbo.authors WHERE [state] = @State
|
|
|
|
|
|
i make drop to table ..
i want to retrive this table what i can do ??
Palestine
|
|
|
|
|
|
Welcome Pelastine :
You can not retrieve table after drop
but if you have Backup from Database you can restore it
Rami Abdalhalim From Pelastine
|
|
|
|
|
thanks rami ,
i will be very happey to see in my msn tareq_abuzuhri@hotmail.com
Palestine
|
|
|
|
|
Evening Everyone
i have a Table Called "Property", this table a lot of Fields and a Primary key called "Property_ID" the identity column. when there are Dublicate in the table, i would not be able to Catch them by only "Property_ID". i have some 2 fields that are not supposed to be the same. Field "Fun_key" and "Lis_key", and that means there must never be
Func_key * 2 and Lis_key * 2, i tried this statement and it does not bring me Dublicates.
select Func_key, Lis_key
from Property
group by Func_key,Lis_key
having count(Lis_key) >1 and count(Func_key) >1
order by Lis_key
how can i achieve this and sort them by lis_key
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
I'm not exactly sure what you are asking for. If you want a list of property_id's where the func_key and lis_key are the same, you would query something like this:
SELECT property_id, func_key, lis_key<br />
WHERE func_key=lis_key<br />
ORDER BY lis_key
If this is not what you want, then please provide us with examples of your data and specifically what you want to pull back.
Hope this helps.
|
|
|
|
|
code>
SELECT DISTINT *
INTO #TEMP
FROM TABLE
SELECT * FROM #TEMP
TRUNCATE TABLE
INSERT TABLE
SELECT * FROM TEMP
Use this with care as it may cause you issue with indentities and triggers etc,,,,,
|
|
|
|
|
Thanks man it worked
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
Hi all,
Could someoine please provide me with an example of a Oracle SQL Script for creating more than one table.
Many Thanks in advance
Regards,
The only programmers that are better that C programmers are those who code in 1's and 0's
Programm3r
My Blog: ^_^
|
|
|
|
|
CREATE TABLE MYTABLE1(<br />
column1A DECIMAL(4,0) NOT NULL DEFAULT=0, <br />
column1B TEXT(20) NOT NULL DEFAULT='', <br />
);<br />
CREATE TABLE MYTABLE2(<br />
column2A DECIMAL(4,0) NOT NULL DEFAULT=0, <br />
column2B TEXT(20) NOT NULL DEFAULT='', <br />
);
|
|
|
|
|
I don't want any duplicates in a field that is not a primary key how can I do that in SQL Server 2005? thanks
BiG RaLpH
|
|
|
|
|
|
Use Unique key constrainst
Best Regards,
Chetan Patel
|
|
|
|
|
Hi Everyone,
Wasn't sure if this was a VB or SQL question and where to post so I am trying here.
I read it is better to use stored procedures and functions on the SQL server to centralise and re-use code, as well as allowing us to restrict rights to EXECUTE rather than full rights.
I wanted to retrieve (and maybe in another function SET), some data so I wrote the following basic function and stored it on the server. My problem is that no matter how I try, I can't seem to find a way to call the function from my VB.NET 2005 application. Please help
ALTER FUNCTION dbo.GetEmployeeFont
(
@EmployeeID int
)
RETURNS TABLE
AS
RETURN
(SELECT FontName, FontSize
FROM tblEmployees
WHERE (EmployeeID = @EmployeeID))
|
|
|
|
|
You can't call the function directly. You should write a stored procedure that acts as a wrapper for the function to call it from your VB application.
Paul
|
|
|
|
|
You cannot use sqlserver function in vb.net .Create one sp in sqlserver
and call this fun with in sp .
-- modified at 5:30 Friday 7th September, 2007
Manu
|
|
|
|
|
I'm not sure what these people are talking about, but it's just the same as executing any other bit of sql:
SELECT FontName, FontSize FROM dbo.GetEmployeeFont(@EmployeeId)
Just run this with a sql command and return a reader (or whatever suits your code).
(Although, that said, it seems to be a more useful thing to have as a stored procedure. Functions are generally more useful when you will re-use them in other bits of sql.)
|
|
|
|
|
My statement is still correct. A function cannot be called directly. The call must be made from either a stored procedure or inline SQL statement.
Paul
|
|
|
|
|
Yes, but what benefit is gained from wrapping the function in a stored procedure? It's just another level of code to go wrong...
|
|
|
|