|
I am creating an advanced search page.in that I have 11 fields.i wrote a stored procedure which has all 11 parameters.
If I don’t enter any value for the parameters I am not getting the result and it goes to exception handling.
Now what I want is even I enter one value this must be excute………….i think some validations must applied to Stored procedure's where clause.but I don’t know how …………….can anyone help me regarding this
would be thankful if you send the code for this
Thanks
sri
|
|
|
|
|
These type of queries can get messy quick. Normally you need one table that will always be returned no matter what is being searched on. So that is the first table in the statement. All other tables that are optional need to be left joins to that first table other wise you won't get any rows returned. Then in your where clause you have to do some stuff like:
where (@param1 = null || table2.column = @param1)
It can be a real mess. The only other option is to dynamically create your query in your stored procedure and then call Exec on that sql statement.
Hope that helps.
Ben
|
|
|
|
|
AS:1
QNO : 1
CREATE TABLE MEMBER_VIJAY(MEMBER_ID NUMBER(5),
MEMBER_NAME CHAR(25),
ACC_OPEN_DATE DATE,
MAX_BOOKS_ALLOWED NUMBER(2),
PENALTY_AMOUNT NUMBER(7,2)
);
CREATE TABLE BOOK_VIJAY(BOOK_NO NUMBER(6),
BOOK_NAME VARCHAR2(30),
AUTHOR CHAR(30),
COST NUMBER(7,2),
CATEGORY CHAR(10)
);
CREATE TABLE ISSUE_VIJAY(LIB_ISSUE_ID NUMBER(10),
BOOK_NO NUMBER(6),
MEMBER_ID NUMBER(5),
ISSUE_DATE DATE,
RETURN_DATE DATE
);
QNO : 2
DESC MEMBER_VIJAY;
DESC BOOK_VIJAY;
DESC ISSUE_VIJAY;
QNO : 3
ALTER TABLE ISSUE_VIJAY ADD( COMMENTS CHAR(100));
QNO : 4
ALTER TABLE MEMBER_VIJAY MODIFY(MEMBER_NAME CHAR(30));
QNO : 5
ALTER TABLE ISSUE_VIJAY ADD(REFERENCE CHAR(30));
QNO : 6
ALTER TABLE ISSUE_VIJAY DROP COLUMN REFERENCE;
QNO : 7
RENAME ISSUE_VIJAY TO LIB_ISSUE_VIJAY;
QNO : 8
INSERT INTO MEMBER_VIJAY VALUES(1,'RICHA SHARMA','10-DEC-05',5,50);
INSERT INTO MEMBER_VIJAY VALUES(2,'GARIMA SHARMA',SYSDATE,3,NULL);
QNO : 9
ALTER TABLE MEMBER_VIJAY MODIFY(MEMBER_NAME CHAR(20));
ORA-01441: cannot decrease column length because some value is too big
QNO : 10
INSERT INTO MEMBER_VIJAY VALUES(1,'VIJAY','27-JUNE-2007',110,100);
ORA-01438: value larger than specified precision allows for this column
QNO : 11
CREATE TABLE MEMBER101_VIJAY AS (SELECT *FROM MEMBER_VIJAY);
QNO : 12
INSERT INTO BOOK_VIJAY VALUES(101,'LET US C','DENIS RITCHIE',450,'SYSTEM');
INSERT INTO BOOK_VIJAY VALUES(102,'ORACLE-COMPLETE REFERENCE','LONI',550,'DATABASE');
INSERT INTO BOOK_VIJAY VALUES(103,'MASTERING SQL','LONI',250,'DATABASE');
INSERT INTO BOOK_VIJAY VALUES(104,'PL SQL-REF','SCOTT URMAN',750,'DATABASE');
QNO : 13
INSERT INTO BOOK_VIJAY VALUES(&BOOK_NO,'&BOOK_NAME','&AUTHOR',&COST,'&CATEGORY');
Enter value for book_no: 105
Enter value for book_name: C++
Enter value for author: LIPMAN
Enter value for cost: 450
Enter value for category: SYSTEM
QNO : 14
CREATE TABLE BOOK101_VIJAY AS (SELECT BOOK_NO,BOOK_NAME,AUTHOR,COST,CATEGORY FROM BOOK_VIJAY WHERE BOOK_NO NOT LIKE '1%');
QNO : 15
INSERT INTO BOOK101_VIJAY(SELECT *FROM BOOK_VIJAY);
QNO : 16
COMMIT;
QNO : 17
SELECT *FROM MEMBER_VIJAY;
SELECT *FROM BOOK_VIJAY;
SELECT *FROM ISSUE_VIJAY;
SELECT *FROM MEMBER101_VIJAY;
SELECT *FROM BOOK101_VIJAY;
QNO : 18
INSERT INTO BOOK_VIJAY VALUES(105,'NATIONAL GEOGRAPHIC','ADIS SCOTT',1000,'SCIENCE');
QNO : 19
ROLLBACK;
QNO : 20
UPDATE BOOK_VIJAY SET COST=300,CATEGORY='RDBMS' WHERE BOOK_NO=103;
QNO : 21
RENAME TABLE LIB_ISSUE_VIJAY TO ISSUE_VIJAY;
QNO : 22
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7001
Enter value for book_no: 101
Enter value for member_id: 1
Enter value for issue_date: '10-DEC-06'
QNO : 23
COMMIT;
QNO : 24
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7007
Enter value for book_no: 101
Enter value for member_id: 3
Enter value for issue_date: '10-JUNE-06'
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7007
Enter value for book_no: 102
Enter value for member_id: 2
Enter value for issue_date: '15-JULY-06'
QNO : 25
UPDATE ISSUE_VIJAY SET RETURN_DATE=(ISSUE_DATE+15) WHERE LIB_ISSUE_ID=7004
OR LIB_ISSUE_ID=7005;
QNO : 26
UPDATE MEMBER_VIJAY SET PENALTY_AMOUNT=100 WHERE MEMBER_NAME='GARIMA SHARMA';
QNO : 27
SAVEPOINT X;
QNO : 28
DELETE FROM ISSUE_VIJAY WHERE MEMBER_ID=1 AND ISSUE_DATE<'10-DEC-06';
QNO : 29
DELETE FROM BOOK_VIJAY WHERE CATEGORY!='RDBMS' AND CATEGORY!='DATABASE';
QNO : 30
ROLLBACK TO X;
QNO : 31
COMMIT;
QNO : 32
DROP TABLE MEMBER101_VIJAY;
QNO : 33
DROP TABLE BOOK101_VIJAY;
QNO : 34
SELECT * FROM MEMBER_VIJAY;
SELECT * FROM BOOK_VIJAY;
SELECT *FROM ISSUE_VIJAY;
DESC MEMBER_VIJAY;
DESC BOOK_VIJAY;
DESC ISSUE_VIJAY;
QNO : 35
SELECT TNAME FROM TAB WHERE TNAME LIKE '%VIJAY';
########################################################################
AS: 2
QNO : 1
SELECT MEMBER_NAME
FROM MEMBER
WHERE ACC_OPEN_DATE>'31-DEC-2005' AND ACC_OPEN_DATE <'1-JAN-2007';
OR
SELECT MEMBER_NAME
FROM MEMBER
WHERE ACC_OPEN_DATE BETWEEN '31-DEC-2005' AND '1-JAN-2007';
QNO : 2
SELECT BOOK_NAME,AUTHOR
FROM BOOK
WHERE AUTHOR='LONI' AND COST>600;
QNO : 3
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE COMMENTS='PENDING';
QNO : 4
UPDATE ISSUE SET RETURN_DATE='31-DEC-06'
WHERE RETURN_DATE=NULL AND (LIB_ISSUE_ID!=7005 OR LIB_ISSUE_ID!=7006);
QNO : 5
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE (SYSDATE-ISSUE_DATE)>30 AND (ISSUE_DATE-RETURN_DATE)>30;
OR
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE ((SYSDATE-ISSUE_DATE)>30 AND RETURN_DATE=NULL) OR (ISSUE_DATE-RETURN_DATE)>30;
QNO : 6
SELECT BOOK_NAME
FROM BOOK
WHERE (COST BETWEEN 500 AND 750) AND CATEGORY='DATABASE';
QNO : 7
SELECT BOOK_NAME
FROM BOOK
WHERE CATEGORY IN('SCIENCE','DATABASE','FICTION','MANAGEMENT');
QNO : 8
SELECT MEMBER_ID,MEMBER_NAME,PENALTY_AMOUNT
FROM MEMBER
ORDER BY PENALTY_AMOUNT DESC;
OR
SELECT MEMBER_ID,MEMBER_NAME,PENALTY_AMOUNT
FROM MEMBER
ORDER BY 3 DESC;
QNO : 9
SELECT BOOK_NAME,COST
FROM BOOK
ORDER BY BOOK_NAME,COST DESC;
OR
SELECT BOOK_NAME,COST
FROM BOOK
ORDER BY 1,2 DESC;
QN0 : 10
SELECT BOOK_NAME
FROM BOOK
WHERE BOOK_NAME LIKE '%SQL%';
QNO : 11
SELECT MEMBER_NAME
FROM MEMBER
WHERE MEMBER_NAME LIKE ('G%I%' ||'R%I%');
QNO : 12
SELECT INITCAP(BOOK_NAME),UPPER(AUTHOR)
FROM BOOK
ORDER BY 1 DESC;
QNO : 13
SELECT BOOK_NO, CONCAT('IS WRRITTEN ', 'BY') " ---------------- ", AUTHOR AS NAME
FROM BOOK;
OR
SELECT CONCAT( BOOK_NO,' IS WRITTEN BY') "BOOKNO ------------------",AUTHOR AS NAME
FROM BOOK;
QNO : 14
SELECT LIB_ISSUE_ID,BOOK_NO,TO_CHAR(TO_DATE(ISSUE_DATE,'DD-MM-YY'),'Day,Month,DD,YYYY') ,
TO_CHAR(TO_DATE(RETURN_DATE,'DD-MM-YY'),'Day,Month,DD,YYYY')
FROM ISSUE
WHERE MEMBER_ID=101;
QNO : 15 & 16
SELECT CATEGORY,
DECODE(CATEGORY,'DATABASE','D','SCIENCE','S','RDBMS','R','OTHERS','O')
FROM BOOK;
QNO : 17
SELECT CONCAT( RPAD(BOOK_NAME,20,'*'),LPAD(AUTHOR,20,'*') )
FROM BOOK;
QNO : 18
SELECT LIB_ISSUE_ID,ISSUE_DATE,RETURN_DATE,COUNT(ISSUE_DATE-RETURN_DATE) "NO OF DAYS"
FROM ISSUE;
QNO : 19
SELECT MEMBER_ID,MEMBER_NAME,ACC_OPEN_DATE,MAX_BOOKS_ALLOWED,PENALTY_AMOUNT
FROM MEMBER
ORDER BY 3 DESC;
QNO : 20
SELECT COUNT(BOOK_NO)
FROM ISSUE
WHERE MEMBER_ID=101;
QNO : 21
SELECT SUM(PENALTY_AMOUNT)
FROM MEMBER;
QNO : 22
SELECT SUM(COST)
FROM BOOK
WHERE CATEGORY='DATABASE';
QNO : 23
SELECT BOOK_NAME,COST
FROM BOOK ;
QNO : 24
SELECT BOOK_NO,MIN(ISSUE_DATE)
FROM ISSUE;
QNO : 25
SELECT BOOK_NO,MAX(ISSUE_DATE)
FROM ISSUE;
QNO : 26
SELECT AVG(COST)
FROM BOOK
WHERE CATEGORY='DATABASE';
########################################################################
AS: 3
QNO : 1
SELECT CATEGORY,COUNT(BOOK_NO) FROM BOOK_C_VIJAY GROUP BY CATEGORY;
QNO : 2
SELECT B.BOOK_NO,COUNT(I.BOOK_NO) FROM BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE (B.BOOK_NO=I.BOOK_NO) GROUP BY B.BOOK_NO;
QNO : 3
SELECT MAX(PENALTY_AMOUNT),MIN(PENALTY_AMOUNT),SUM(PENALTY_AMOUNT),
AVG(PENALTY_AMOUNT) FROM MEMBER_C_VIJAY;
QNO : 4
SELECT M.MEMBER_ID,COUNT(I.MEMBER_ID) FROM
MEMBER_C_VIJAY M,ISSUE_C_VIJAY I WHERE
(M.MEMBER_ID=I.MEMBER_ID) GROUP BY M.MEMBER_ID;
QNO : 5
SELECT I.MEMBER_ID,B.BOOK_NO,COUNT(I.BOOK_NO)
FROM ISSUE_C_VIJAY I,BOOK_C_VIJAY B
WHERE I.BOOK_NO IN(B.BOOK_NO)
GROUP BY I.MEMBER_ID,B.BOOK_NO;
QNO : 6
SELECT TO_CHAR(ISSUE_DATE,'MONTH'),COUNT(BOOK_NO)
FROM ISSUE_C_VIJAY
GROUP BY TO_CHAR(ISSUE_DATE,'MONTH')
ORDER BY 2 DESC;
QNO : 7
SELECT BOOK_NO FROM BOOK_C_VIJAY
WHERE BOOK_NO NOT IN(SELECT BOOK_NO FROM ISSUE_C_VIJAY);
QNO : 8
SELECT MEMBER_ID FROM MEMBER_C_VIJAY
WHERE MEMBER_ID IN(SELECT MEMBER_ID FROM ISSUE_C_VIJAY);
QNO : 9
SELECT MEMBER_ID,COUNT(*)
FROM ISSUE_VIJAY
GROUP BY MEMBER_ID
HAVING COUNT(*) IN
( (SELECT MAX( COUNT(*) ) FROM ISSUE_VIJAY
GROUP BY MEMBER_ID),
(SELECT MIN( COUNT(*) ) FROM ISSUE_VIJAY
GROUP BY MEMBER_ID) );
QNO : 10
SELECT * FROM ISSUE_C_VIJAY
WHERE TO_CHAR(ISSUE_DATE,'MM') IN('12','07');
QNO : 11
SELECT B.BOOK_NO,B.BOOK_NAME,I.ISSUE_DATE FROM
BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE TO_CHAR(ISSUE_DATE,'MM') IN('12') AND B.BOOK_NO=I.BOOK_NO AND CATEGORY='DATABASE';
QNO : 12
SELECT I.MEMBER_ID,M.MEMBER_NAME,COUNT(I.BOOK_NO)
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE I.MEMBER_ID=M.MEMBER_ID
GROUP BY I.MEMBER_ID,M.MEMBER_NAME
ORDER BY 3 DESC;
QNO : 13
SELECT B.BOOK_NO,B.BOOK_NAME,I.ISSUE_DATE,I.RETURN_DATE
FROM BOOK_C_VIJAY B,MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE (M.MEMBER_NAME='VIJAY' AND I.BOOK_NO=B.BOOK_NO
AND I.MEMBER_ID=M.MEMBER_ID);
QNO : 14
SELECT M.MEMBER_ID,M.MEMBER_NAME,B.CATEGORY
FROM MEMBER_C_VIJAY M,BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE B.CATEGORY='DATABASE' AND M.MEMBER_ID=I.MEMBER_ID
AND B.BOOK_NO=I.BOOK_NO;
QNO : 15
SELECT CATEGORY, MAX(COST)
FROM BOOK_C_VIJAY
GROUP BY CATEGORY;
QNO : 16
SELECT M.ACC_OPEN_DATE,I.BOOK_NO,I.MEMBER_ID,I.ISSUE_DATE,I.RETURN_DATE
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY
I WHERE (I.ISSUE_DATE NOT BETWEEN M.ACC_OPEN_DATE AND I.RETURN_DATE)
AND I.MEMBER_ID=M.MEMBER_ID ;
QNO : 17
SELECT DISTINCT M.MEMBER_ID,M.MEMBER_NAME
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY
WHERE M.MEMBER_ID NOT IN(SELECT MEMBER_ID FROM ISSUE_C_VIJAY);
QNO : 18
SELECT MEMBER_ID,MEMBER_NAME
FROM MEMBER_C_VIJAY
WHERE
(SELECT COUNT(I.BOOK_NO) FROM ISSUE_C_VIJAY I,BOOK_C_VIJAY B,MEMBER_C_VIJAY M
WHERE I.BOOK_NO=B.BOOK_NO AND I.MEMBER_ID=M.MEMBER_ID)>MAX_ALLOWED_BOOKS
GROUP BY MEMBER_ID,MEMBER_NAME;
QNO : 19
QNO : 20
SELECT DISTINCT B.BOOK_NAME,B.COST
FROM BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE (I.RETURN_DATE-I.ISSUE_DATE)>30;
QNO : 21
SELECT B.AUTHOR,B.BOOK_NAME FROM BOOK_C_VIJAY B
WHERE (SELECT COUNT(C.AUTHOR) FROM BOOK_C_VIJAY C WHERE B.AUTHOR=C.AUTHOR)>=2;
QNO : 22
SELECT M.MEMBER_NAME,M.MEMBER_ID,COUNT(*)
FROM ISSUE_C_VIJAY I,MEMBER_C_VIJAY M
WHERE M.MEMBER_ID = I.MEMBER_ID
GROUP BY M.MEMBER_NAME,M.MEMBER_ID
HAVING COUNT(*) IN
( (SELECT MAX( COUNT(*) ) FROM ISSUE_C_VIJAY GROUP BY MEMBER_ID),
(SELECT MIN( COUNT(*) ) FROM ISSUE_C_VIJAY GROUP BY MEMBER_ID) ) ;
QNO : 23
SELECT MAX(COST) FROM BOOK_C_VIJAY
UNION
SELECT MAX(COST) FROM BOOK_C_VIJAY
WHERE COST < (SELECT MAX(COST) FROM BOOK_VIJAY)
UNION
SELECT MAX(COST) FROM BOOK_C_VIJAY
WHERE COST < ( SELECT MAX(COST) FROM BOOK_C_VIJAY WHERE COST < (SELECT MAX(COST) FROM BOOK_C_VIJAY) );
OR
SELECT BOOK_NAME,AUTHOR,COST FROM BOOK_C_VIJAY
ORDER BY COST DESC;
QNO : 24
SELECT SUM(B.COST) FROM BOOK_C_VIJAY B,
ISSUE_C_VIJAY I
WHERE (I.RETURN_DATE>SYSDATE AND B.BOOK_NO=I.BOOK_NO);
QNO : 25
SELECT B.BOOK_NO,B.BOOK_NAME,COUNT(*)
FROM ISSUE_C_VIJAY I,BOOK_C_VIJAY B
WHERE B.BOOK_NO=I.BOOK_NO
GROUP BY B.BOOK_NO,B.BOOK_NAME
HAVING COUNT(*) IN(SELECT MAX( COUNT(*) ) FROM ISSUE_C_VIJAY GROUP BY MEMBER_ID);
#####################################################################
AS : 4
QNO : 1
CREATE TABLE MEMBER_C_VIJAY(MEMBER_ID NUMBER(5) PRIMARY KEY,
ACC_OPEN_DATE DATE DEFAULT SYSDATE,
MAX_ALLOWED_BOOKS NUMBER(2) CHECK(MAX_ALLOWED_BOOKS<100),
PENALTY_AMOUNT NUMBER(7,2) CHECK(PENALTY_AMOUNT<=1000)
MEMBER_NAME CHAR(25));
QNO : 2
CREATE TABLE BOOK_C_VIJAY(BOOK_NO NUMBER(6) PRIMARY KEY,
BOOK_NAME VARCHAR2(30) NOT NULL, CATEGORY CHAR(10)
CHECK(CATEGORY='SCIENCE' OR CATEGORY='FICTION' OR CATEGORY='FICTION' OR CATEGORY='DATABASE' OR CATEGORY='RDBMS' OR CATEGORY='OTHERS'))
QNO : 3
CREATE TABLE ISSUE_C_VIJAY(LIB_ISSUE_ID NUMBER(10) PRIMARY KEY,
MEMBER_ID NUMBER(5) REFERENCES MEMBER_C_VIJAY(MEMBER_ID),
BOOK_NO NUMBER(6) REFERENCES BOOK_C_VIJAY(BOOK_NO),
ISSUE_DATE DATE,
RETURN_DATE DATE );
ALTER TABLE ISSUE_C_VIJAY ADD CONSTRAINT I CHECK(RETURN_DATE>ISSUE_DATE);
OR
CREATE TABLE ISSUE_C_VIJAY(LIB_ISSUE_ID NUMBER(10) PRIMARY KEY,
MEMBER_ID NUMBER(5) REFERENCES MEMBER_C_VIJAY(MEMBER_ID),
BOOK_NO NUMBER(6) REFERENCES BOOK_C_VIJAY(BOOK_NO),
ISSUE_DATE DATE,
RETURN_DATE DATE ,CHECK(RETURN_DATE>ISSUE_DATE);
QNO : 4
ALTER TABLE BOOK_C_VIJAY ADD CONSTRAINT V CHECK(COST<2500);
QNO : 5
SELECT *FROM USER_CONSTRAINTS WHERE TABLE_NAME='ISSUE_C_VIJAY';
QNO : 6
ALTER TABLE ISSUE_C_VIJAY DISABLE CONSTRAINT I;
QNO : 7
CREATE VIEW Vijay AS( SELECT LIB_ISSUE_ID,BOOK_NAME,MEMBER_NAME,AUTHOR,ISSUE_DATE
FROM
BOOK_C_VIJAY B,MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE ((B.COST>500 AND B.COST<750) AND (B.CATEGORY='RDBMS' OR B.CATEGORY='DATABASE') AND
I.BOOK_NO=B.BOOK_NO AND I.MEMBER_ID=M.MEMBER_ID));
QNO : 8
CREATE VIEW VIJAY1 AS(SELECT BOOK_NAME FROM BOOK_C_VIJAY WHERE COST>500);
QNO : 9
QNO : 10
|
|
|
|
|
I want to enter the date in my mysql table so that when I read from the database, I can read only the entries entered on that day or the latest. How do I do this. which datatype should I enter.
I tried with unix_timestamp() but it saves in seconds, so wont it result in lots of arithmetic calculation?
|
|
|
|
|
Hi,
I am having a stored procedure whose output directly binds to the datagrid. The procedure is having a select query as follows:
SELECT P. Project_id as ID, P.Project_Name as Name , P.Project_version as Version , P.Project_Description as Description ,Convert (VarChar(15), P.projected_start_date,106) as ProjectedStartDate , Convert(VarChar(15),P.projected_end_date,106) as ProjectedEndDate,P.actual_start_date as ActualStartDate, P.actual_completion_date as ActualCompletionDate
FROM tblProjects P
INNER JOIN tblProjectType PT ON P.Project_Type_Id = PT.Project_Type_Id
/*ORDER BY P. project_type_id*/
WHERE P.is_active=1
Here the ActualStartDate and ActualCompletionDate can have some mininum date value like '11/09/1753' or the actual dates entered by the user. If the ActualStartDate is '11/09/1753' then i want the to get it as null or empty string and then bind it to the datagrid. so in the datagrid under the ActualStartDate where '11/09/1753' is present, it will actually display null or empty.
Thanks,
Riz
|
|
|
|
|
Compare ActualStartDate with DateTime.MinValue. if its same treat as null or empty...
|
|
|
|
|
ya...i knw that i have to compare the ActualStartDate with DateTime.MinValue But how exactly to do it in the stored procedure?
Let me knw if u have got any solution.
Thanks,
riz
|
|
|
|
|
I am in the same boat on a project. It is said to be "bad design" to allow nulls and you should avoid doing so at all cost. Visual Basic shows the actual dates in the database so I have many fields with the date 12/31/9999 11:59:59 PM which is the max date. It seems that if something is such bad practice they would accommodate it better than they do. I will be watching this topic for answers, also if I find an answer I will be sure to post it here.
|
|
|
|
|
Hello everybody!
I have a small question.
I don't know how to insert a null DateTime value into database.
In my form, I have a textbox-based field named OrderDate. If the user doesn't set value of this textbox, when inserting, I consider the value 1/1/1900 is added to database unexpectedly. I don't like this, I like empty. How should I do to get empty in Database.
Thanks in advance !
It seem to be a solution or an answer.
|
|
|
|
|
There is a sqlDataTime object that has a null property. I would suggest passing that into the parameter.
Hope that helps.
Ben
|
|
|
|
|
hi i have installed SQL7 on my Machine. but when i try to register a Server it gives me this error.
"The Application has Failed to start because VNSAPI32 was not Found.Reinstalling the Application may Fix this Problem"
i try to goggle this Problem , i only found one Answer but its in Arabic , cant read. So i tried to reinstall it again it gives me the same Error
Please Help
Vuyiswa
|
|
|
|
|
Why are you using SQL Server 7.0??  It's older than dirt itself!
|
|
|
|
|
VNSAPI32 is the Banyan Vines network protocol library. Try using the Server Network Utility or Client Network Utility to disable this very rare protocol and try again.
I think that's how you do it, anyway - it's been years since I've used SQL Server 7.0 (I'm struggling to recall if I've ever used SQL Server 7.0).
|
|
|
|
|
Hi all,
I need a way out of this sql server querry. I have a database wich stores username with space in-between. the porblem am having is that when I run a search query using like operator it returns no value. e.g
in my table I have a username stored their called "acod man" but i want to search by entering acod since i am using like operator I want it to locate "acod man" for me but it returns nothing
select username from tbl_names
where username like'%acod%'
I will appreciate your responses.
thanks in advance.
|
|
|
|
|
It should work perfectly. I dont see any reason for this query not working.
I have tried a similar query as:
select c1, c5 from t1 where c5 like 'R%'
and this returns all the records for which c5 columns has values starting with 'R'.
Juz try it again. Otherwise provide table records and structure...
Letz see whats the probs??
Regards
SG (sgg245@yahoo.co.in)
|
|
|
|
|
Tell me what is the type of column for which you are doing pattern-matching??
If the datatype of the column is nchar then it will return 0 records.
Bcoz then its Unicode pattern - matching...
Regards
SG (sgg245@yahoo.co.in)
|
|
|
|
|
QNO : 1
CREATE TABLE MEMBER_C_VIJAY(MEMBER_ID NUMBER(5) PRIMARY KEY,
ACC_OPEN_DATE DATE DEFAULT SYSDATE,
MAX_ALLOWED_BOOKS NUMBER(2) CHECK(MAX_ALLOWED_BOOKS<100),
PENALTY_AMOUNT NUMBER(7,2) CHECK(PENALTY_AMOUNT<=1000)
MEMBER_NAME CHAR(25));
QNO : 2
CREATE TABLE BOOK_C_VIJAY(BOOK_NO NUMBER(6) PRIMARY KEY,
BOOK_NAME VARCHAR2(30) NOT NULL, CATEGORY CHAR(10)
CHECK(CATEGORY='SCIENCE' OR CATEGORY='FICTION' OR CATEGORY='FICTION' OR CATEGORY='DATABASE' OR CATEGORY='RDBMS' OR CATEGORY='OTHERS'))
QNO : 3
CREATE TABLE ISSUE_C_VIJAY(LIB_ISSUE_ID NUMBER(10) PRIMARY KEY,
MEMBER_ID NUMBER(5) REFERENCES MEMBER_C_VIJAY(MEMBER_ID),
BOOK_NO NUMBER(6) REFERENCES BOOK_C_VIJAY(BOOK_NO),
ISSUE_DATE DATE,
RETURN_DATE DATE );
ALTER TABLE ISSUE_C_VIJAY ADD CONSTRAINT I CHECK(RETURN_DATE>ISSUE_DATE);
QNO : 4
ALTER TABLE BOOK_C_VIJAY ADD CONSTRAINT V CHECK(COST<2500);
QNO : 5
QNO : 6
ALTER TABLE ISSUE_C_VIJAY DISABLE CONSTRAINT I;
QNO : 7
CREATE VIEW Vijay AS( SELECT LIB_ISSUE_ID,BOOK_NAME,MEMBER_NAME,AUTHOR,ISSUE_DATE
FROM
BOOK_C_VIJAY B,MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE ((B.COST>500 AND B.COST<750) AND (B.CATEGORY='RDBMS' OR B.CATEGORY='DATABASE') AND
I.BOOK_NO=B.BOOK_NO AND I.MEMBER_ID=M.MEMBER_ID));
QNO : 8
CREATE VIEW VIJAY1 AS(SELECT BOOK_NAME FROM BOOK_C_VIJAY WHERE COST>500);
QNO : 9
QNO : 10
|
|
|
|
|
This looks suspiciously like you are posting answers to questions. Naughty, naughty.
|
|
|
|
|
Now he is doing it in the asp.net forum as well.
Ben
|
|
|
|
|
AS : 3
QNO : 1
SELECT CATEGORY,COUNT(BOOK_NO) FROM BOOK_C_VIJAY GROUP BY CATEGORY;
QNO : 2
SELECT B.BOOK_NO,COUNT(I.BOOK_NO) FROM BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE (B.BOOK_NO=I.BOOK_NO) GROUP BY B.BOOK_NO;
QNO : 3
SELECT MAX(PENALTY_AMOUNT),MIN(PENALTY_AMOUNT),SUM(PENALTY_AMOUNT),
AVG(PENALTY_AMOUNT) FROM MEMBER_C_VIJAY;
QNO : 4
SELECT M.MEMBER_ID,COUNT(I.MEMBER_ID) FROM
MEMBER_C_VIJAY M,ISSUE_C_VIJAY I WHERE
(M.MEMBER_ID=I.MEMBER_ID) GROUP BY M.MEMBER_ID;
QNO : 5
SELECT I.MEMBER_ID,B.BOOK_NO,COUNT(I.BOOK_NO)
FROM ISSUE_C_VIJAY I,BOOK_C_VIJAY B
WHERE I.BOOK_NO IN(B.BOOK_NO)
GROUP BY I.MEMBER_ID,B.BOOK_NO;
QNO : 6
SELECT TO_CHAR(ISSUE_DATE,'MONTH'),COUNT(BOOK_NO)
FROM ISSUE_C_VIJAY
GROUP BY TO_CHAR(ISSUE_DATE,'MONTH')
ORDER BY 2 DESC;
QNO : 7
SELECT BOOK_NO FROM BOOK_C_VIJAY
WHERE BOOK_NO NOT IN(SELECT BOOK_NO FROM ISSUE_C_VIJAY);
QNO : 8
SELECT MEMBER_ID FROM MEMBER_C_VIJAY
WHERE MEMBER_ID IN(SELECT MEMBER_ID FROM ISSUE_C_VIJAY);
QNO : 9
QNO : 10
SELECT * FROM ISSUE_C_VIJAY
WHERE TO_CHAR(ISSUE_DATE,'MM') IN('12','07');
QNO : 11
SELECT B.BOOK_NO,B.BOOK_NAME,I.ISSUE_DATE FROM
BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE TO_CHAR(ISSUE_DATE,'MM') IN('12') AND B.BOOK_NO=I.BOOK_NO AND CATEGORY='DATABASE';
QNO : 12
SELECT I.MEMBER_ID,M.MEMBER_NAME,COUNT(I.BOOK_NO)
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE I.MEMBER_ID=M.MEMBER_ID
GROUP BY I.MEMBER_ID,M.MEMBER_NAME
ORDER BY 3 DESC;
QNO : 13
SELECT B.BOOK_NO,B.BOOK_NAME,I.ISSUE_DATE,I.RETURN_DATE
FROM BOOK_C_VIJAY B,MEMBER_C_VIJAY M,ISSUE_C_VIJAY I
WHERE (M.MEMBER_NAME='VIJAY' AND I.BOOK_NO=B.BOOK_NO
AND I.MEMBER_ID=M.MEMBER_ID);
QNO : 14
SELECT M.MEMBER_ID,M.MEMBER_NAME,B.CATEGORY
FROM MEMBER_C_VIJAY M,BOOK_C_VIJAY B,ISSUE_C_VIJAY I
WHERE B.CATEGORY='DATABASE' AND M.MEMBER_ID=I.MEMBER_ID
AND B.BOOK_NO=I.BOOK_NO;
QNO : 15
SELECT CATEGORY, MAX(COST)
FROM BOOK_C_VIJAY
GROUP BY CATEGORY;
QNO : 16
SELECT M.ACC_OPEN_DATE,I.BOOK_NO,I.MEMBER_ID,I.ISSUE_DATE,I.RETURN_DATE
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY
I WHERE (I.ISSUE_DATE NOT BETWEEN M.ACC_OPEN_DATE AND I.RETURN_DATE)
AND I.MEMBER_ID=M.MEMBER_ID ;
QNO : 17
SELECT DISTINCT M.MEMBER_ID,M.MEMBER_NAME
FROM MEMBER_C_VIJAY M,ISSUE_C_VIJAY
WHERE M.MEMBER_ID NOT IN(SELECT MEMBER_ID FROM ISSUE_C_VIJAY);
QNO : 18
SELECT MEMBER_ID,MEMBER_NAME
FROM MEMBER_C_VIJAY
WHERE
(SELECT COUNT(I.BOOK_NO) FROM ISSUE_C_VIJAY I,BOOK_C_VIJAY B,MEMBER_C_VIJAY M
WHERE I.BOOK_NO=B.BOOK_NO AND I.MEMBER_ID=M.MEMBER_ID)>MAX_ALLOWED_BOOKS
GROUP BY MEMBER_ID,MEMBER_NAME;
QNO : 19
****************************************************************************
|
|
|
|
|
AS:2
QNO : 1
SELECT MEMBER_NAME
FROM MEMBER
WHERE ACC_OPEN_DATE>'31-DEC-2005' AND ACC_OPEN_DATE <'1-JAN-2007';
OR
SELECT MEMBER_NAME
FROM MEMBER
WHERE ACC_OPEN_DATE BETWEEN '31-DEC-2005' AND '1-JAN-2007';
QNO : 2
SELECT BOOK_NAME,AUTHOR
FROM BOOK
WHERE AUTHOR='LONI' AND COST>600;
QNO : 3
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE COMMENTS='PENDING';
QNO : 4
UPDATE ISSUE SET RETURN_DATE='31-DEC-06'
WHERE RETURN_DATE=NULL AND (LIB_ISSUE_ID!=7005 OR LIB_ISSUE_ID!=7006);
QNO : 5
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE (SYSDATE-ISSUE_DATE)>30 AND (ISSUE_DATE-RETURN_DATE)>30;
OR
SELECT LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE,RETURN_DATE
FROM ISSUE
WHERE ((SYSDATE-ISSUE_DATE)>30 AND RETURN_DATE=NULL) OR (ISSUE_DATE-RETURN_DATE)>30;
QNO : 6
SELECT BOOK_NAME
FROM BOOK
WHERE (COST BETWEEN 500 AND 750) AND CATEGORY='DATABASE';
QNO : 7
SELECT BOOK_NAME
FROM BOOK
WHERE CATEGORY IN('SCIENCE','DATABASE','FICTION','MANAGEMENT');
QNO : 8
SELECT MEMBER_ID,MEMBER_NAME,PENALTY_AMOUNT
FROM MEMBER
ORDER BY PENALTY_AMOUNT DESC;
OR
SELECT MEMBER_ID,MEMBER_NAME,PENALTY_AMOUNT
FROM MEMBER
ORDER BY 3 DESC;
QNO : 9
SELECT BOOK_NAME,COST
FROM BOOK
ORDER BY BOOK_NAME,COST DESC;
OR
SELECT BOOK_NAME,COST
FROM BOOK
ORDER BY 1,2 DESC;
QN0 : 10
SELECT BOOK_NAME
FROM BOOK
WHERE BOOK_NAME LIKE '%SQL%';
QNO : 11
SELECT MEMBER_NAME
FROM MEMBER
WHERE MEMBER_NAME LIKE ('G%I%' ||'R%I%');
QNO : 12
SELECT INITCAP(BOOK_NAME),UPPER(AUTHOR)
FROM BOOK
ORDER BY 1 DESC;
QNO : 13
SELECT BOOK_NO, CONCAT('IS WRRITTEN ', 'BY') " ---------------- ", AUTHOR AS NAME
FROM BOOK;
OR
SELECT CONCAT( BOOK_NO,' IS WRITTEN BY') "BOOKNO ------------------",AUTHOR AS NAME
FROM BOOK;
QNO : 14
SELECT LIB_ISSUE_ID,BOOK_NO,TO_CHAR(TO_DATE(ISSUE_DATE,'DD-MM-YY'),'Day,Month,DD,YYYY') ,
TO_CHAR(TO_DATE(RETURN_DATE,'DD-MM-YY'),'Day,Month,DD,YYYY')
FROM ISSUE
WHERE MEMBER_ID=101;
QNO : 15 & 16
SELECT CATEGORY,
DECODE(CATEGORY,'DATABASE','D','SCIENCE','S','RDBMS','R','OTHERS','O')
FROM BOOK;
QNO : 17
SELECT CONCAT( RPAD(BOOK_NAME,20,'*'),LPAD(AUTHOR,20,'*') )
FROM BOOK;
QNO : 18
SELECT LIB_ISSUE_ID,ISSUE_DATE,RETURN_DATE,COUNT(ISSUE_DATE-RETURN_DATE) "NO OF DAYS"
FROM ISSUE;
QNO : 19
SELECT MEMBER_ID,MEMBER_NAME,ACC_OPEN_DATE,MAX_BOOKS_ALLOWED,PENALTY_AMOUNT
FROM MEMBER
ORDER BY 3 DESC;
QNO : 20
SELECT COUNT(BOOK_NO)
FROM ISSUE
WHERE MEMBER_ID=101;
QNO : 21
SELECT SUM(PENALTY_AMOUNT)
FROM MEMBER;
QNO : 22
SELECT SUM(COST)
FROM BOOK
WHERE CATEGORY='DATABASE';
QNO : 23
SELECT BOOK_NAME,COST
FROM BOOK ;
QNO : 24
SELECT BOOK_NO,MIN(ISSUE_DATE)
FROM ISSUE;
QNO : 25
SELECT BOOK_NO,MAX(ISSUE_DATE)
FROM ISSUE;
QNO : 26
SELECT AVG(COST)
FROM BOOK
WHERE CATEGORY='DATABASE';
*****************************************************************************
|
|
|
|
|
AS:1
QNO : 1
CREATE TABLE MEMBER_VIJAY(MEMBER_ID NUMBER(5),
MEMBER_NAME CHAR(25),
ACC_OPEN_DATE DATE,
MAX_BOOKS_ALLOWED NUMBER(2),
PENALTY_AMOUNT NUMBER(7,2)
);
CREATE TABLE BOOK_VIJAY(BOOK_NO NUMBER(6),
BOOK_NAME VARCHAR2(30),
AUTHOR CHAR(30),
COST NUMBER(7,2),
CATEGORY CHAR(10)
);
CREATE TABLE ISSUE_VIJAY(LIB_ISSUE_ID NUMBER(10),
BOOK_NO NUMBER(6),
MEMBER_ID NUMBER(5),
ISSUE_DATE DATE,
RETURN_DATE DATE
);
QNO : 2
DESC MEMBER_VIJAY;
DESC BOOK_VIJAY;
DESC ISSUE_VIJAY;
QNO : 3
ALTER TABLE ISSUE_VIJAY ADD( COMMENTS CHAR(100));
QNO : 4
ALTER TABLE MEMBER_VIJAY MODIFY(MEMBER_NAME CHAR(30));
QNO : 5
ALTER TABLE ISSUE_VIJAY ADD(REFERENCE CHAR(30));
QNO : 6
ALTER TABLE ISSUE_VIJAY DROP COLUMN REFERENCE;
QNO : 7
RENAME ISSUE_VIJAY TO LIB_ISSUE_VIJAY;
QNO : 8
INSERT INTO MEMBER_VIJAY VALUES(1,'RICHA SHARMA','10-DEC-05',5,50);
INSERT INTO MEMBER_VIJAY VALUES(2,'GARIMA SHARMA',SYSDATE,3,NULL);
QNO : 9
ALTER TABLE MEMBER_VIJAY MODIFY(MEMBER_NAME CHAR(20));
ORA-01441: cannot decrease column length because some value is too big
QNO : 10
INSERT INTO MEMBER_VIJAY VALUES(1,'VIJAY','27-JUNE-2007',110,100);
ORA-01438: value larger than specified precision allows for this column
QNO : 11
CREATE TABLE MEMBER101_VIJAY AS (SELECT *FROM MEMBER_VIJAY);
QNO : 12
INSERT INTO BOOK_VIJAY VALUES(101,'LET US C','DENIS RITCHIE',450,'SYSTEM');
INSERT INTO BOOK_VIJAY VALUES(102,'ORACLE-COMPLETE REFERENCE','LONI',550,'DATABASE');
INSERT INTO BOOK_VIJAY VALUES(103,'MASTERING SQL','LONI',250,'DATABASE');
INSERT INTO BOOK_VIJAY VALUES(104,'PL SQL-REF','SCOTT URMAN',750,'DATABASE');
QNO : 13
INSERT INTO BOOK_VIJAY VALUES(&BOOK_NO,'&BOOK_NAME','&AUTHOR',&COST,'&CATEGORY');
Enter value for book_no: 105
Enter value for book_name: C++
Enter value for author: LIPMAN
Enter value for cost: 450
Enter value for category: SYSTEM
QNO : 14
CREATE TABLE BOOK101_VIJAY AS (SELECT BOOK_NO,BOOK_NAME,AUTHOR,COST,CATEGORY FROM BOOK_VIJAY WHERE BOOK_NO NOT LIKE '1%');
QNO : 15
INSERT INTO BOOK101_VIJAY(SELECT *FROM BOOK_VIJAY);
QNO : 16
COMMIT;
QNO : 17
SELECT *FROM MEMBER_VIJAY;
SELECT *FROM BOOK_VIJAY;
SELECT *FROM ISSUE_VIJAY;
SELECT *FROM MEMBER101_VIJAY;
SELECT *FROM BOOK101_VIJAY;
QNO : 18
INSERT INTO BOOK_VIJAY VALUES(105,'NATIONAL GEOGRAPHIC','ADIS SCOTT',1000,'SCIENCE');
QNO : 19
ROLLBACK;
QNO : 20
UPDATE BOOK_VIJAY SET COST=300,CATEGORY='RDBMS' WHERE BOOK_NO=103;
QNO : 21
RENAME TABLE LIB_ISSUE_VIJAY TO ISSUE_VIJAY;
QNO : 22
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7001
Enter value for book_no: 101
Enter value for member_id: 1
Enter value for issue_date: '10-DEC-06'
QNO : 23
COMMIT;
QNO : 24
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7007
Enter value for book_no: 101
Enter value for member_id: 3
Enter value for issue_date: '10-JUNE-06'
INSERT INTO ISSUE_VIJAY(LIB_ISSUE_ID,BOOK_NO,MEMBER_ID,ISSUE_DATE)
VALUES(&LIB_ISSUE_ID,&BOOK_NO,&MEMBER_ID,&ISSUE_DATE);
Enter value for lib_issue_id: 7007
Enter value for book_no: 102
Enter value for member_id: 2
Enter value for issue_date: '15-JULY-06'
QNO : 25
UPDATE ISSUE_VIJAY SET RETURN_DATE=(ISSUE_DATE+15) WHERE LIB_ISSUE_ID=7004
OR LIB_ISSUE_ID=7005;
QNO : 26
UPDATE MEMBER_VIJAY SET PENALTY_AMOUNT=100 WHERE MEMBER_NAME='GARIMA SHARMA';
QNO : 27
SAVEPOINT X;
QNO : 28
DELETE FROM ISSUE_VIJAY WHERE MEMBER_ID=1 AND ISSUE_DATE<'10-DEC-06';
QNO : 29
DELETE FROM BOOK_VIJAY WHERE CATEGORY!='RDBMS' AND CATEGORY!='DATABASE';
QNO : 30
ROLLBACK TO X;
QNO : 31
COMMIT;
QNO : 32
DROP TABLE MEMBER101_VIJAY;
QNO : 33
DROP TABLE BOOK101_VIJAY;
QNO : 34
SELECT * FROM MEMBER_VIJAY;
SELECT * FROM BOOK_VIJAY;
SELECT *FROM ISSUE_VIJAY;
DESC MEMBER_VIJAY;
DESC BOOK_VIJAY;
DESC ISSUE_VIJAY;
QNO : 35
SELECT TNAME FROM TAB WHERE TNAME LIKE '%VIJAY';
*****************************************************************************
|
|
|
|
|
hi
i want to encrypt whole database, that admin or other users can't see my dataBase's core, (i don't want to encrypt fields, i want to encrypt whole database), can anyone help me ?
thanks
|
|
|
|
|
To stop anyone else accessing your database via the management tools you'd have to deny them the rights to do so. Easy enough to do but if you don't want any server admins to be able to access your databse I assume that means your using external hosting, in which case I doubt they'd let you deny them rights :P
|
|
|
|
|
I have a central db as publisher on SQL Server 2005, which has SUPERVISOR user having sysadmin rights.
And created another client db with subscriber user (not having sysadmin rights on the publisher SQL Server 2005), say U1, on SQL Express 2005 instance. As subscriber is the owner of db of subscriber db, so has sysadmin rights on the SQL Express 2005.
Done all the pre-requisite for merge replication.
When I try to replicate between the publisher db & subscriber db with subscriber user, it gives error
"The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a publisher or a republishing Subscriber has run out of identity ranges to allocate to its own subscriber or when an identity column data type does not support an additional identity range allocation. If a republishing subscriber has run out of identity ranges, synchronize the republishing subscriber to obtain more identity ranges before restarting the synchronization. if a publisher runs out of identity....."
Although, if the subscriber user is created as sysadmin on the publisher i.e. SQL Server 2005 then the synchronization works perfectly and synchronizes the data.
I would like to know why SYSADMIN right is needed to be given to the subscriber user at the publisher i.e. SQL Server 2005 instance???
Regards
SG (sgg245@yahoo.co.in)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
