Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #256396] Sat, 04 August 2007 04:22 Go to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
i have one card_mst table with following structure:

card_num card_mem_type Id cust_no vaild_date RE_NO
1 t 12 101 01/01/2007
2 c 13 102 02/02/2007
3 t 14 102 03/03/2007
4 y 15 103 04/04/2007
5 t 16 105 05/04/2007
c 17 106 05/05/2007
y 18 104 06/06/2007
c 15 103 07/06/2007
z 16 105 07/07/2007
c 19 109 14/07/2007

1.Case : I want that new cust_no where card_num is null (out of cust_no where card_num is not null)
2.Case : And that cust_no where card_num is null but that cust_no is repeating(means that cust_no should be present where card_num is not null)

I want the two sql query for both case (without using subquery)

SELECT DISTINCT(CUST_NO),ID,VALID_DATE,CARD_MEM_TYPE,RE_NO
FROM CARD_MST
WHERE CARD_NUM IS NULL AND
CUST_NO NOT IN (SELECT CUST_NO FROM Card_MST WHERE CARD_NUM IS NOT NULL)
ORDER BY VALID_DATE;

SELECT DISTINCT(CUST_NO),ID,VALID_DATE,CARD_MEM_TYPE,RE_NO
FROM CARD_MST
WHERE CARD_NUM IS NULL AND
CUST_NO IN (SELECT CUST_NO FROM CarD_MST WHERE CARD_NUM IS NOT NULL)
ORDER BY VALID_DATE;

I Was Trying With Subquery Like It Is Giving Expected Result But It Is Taking More Time Bcaz Of Subquery .
Please Giv Any Solution Without Subquery
Re: sql query [message #256399 is a reply to message #256396] Sat, 04 August 2007 05:11 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi

Your Query..

SELECT DISTINCT(CUST_NO),ID,VALID_DATE,CARD_MEM_TYPE,RE_NO
FROM CARD_MST
WHERE CARD_NUM IS NULL AND
CUST_NO NOT IN (SELECT CUST_NO FROM Card_MST WHERE CARD_NUM IS NOT NULL)
ORDER BY VALID_DATE;

My reply

Dear Friend just delete the line # 4 from the query this will make the query faster.

As per your Question

You need to view the customer number whose card number is not entered yet.
you are duplicating this query's slowness by asking on question both the ways straight way and sub query way.
do according to my suggestion i hope the problem will be solved.

Regards

Muhammad Shahid Mughal
I.T Manager
Fivestar International
Faisalabad Pakistan
Re: sql query [message #256405 is a reply to message #256399] Sat, 04 August 2007 06:38 Go to previous messageGo to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
Hi shahidmughal,

card_num card_mem_type  Id  cust_no  vaild_date  RE_NO
  1         t           12    101    01/01/2007   1
  2         c           13    102    02/02/2007   2
  3         t           14    102    03/03/2007   3
  4         y           15    103    04/04/2007   4
  5         t           16    105    05/04/2007   5
            c           17    106    05/05/2007   6
            y           18    104    06/06/2007   7
            c           15    103    07/06/2007   8
            z           16    105    07/07/2007   9
            c           19    109    14/07/2007   10



u mean this 4th line
" CUST_NO NOT IN (SELECT CUST_NO FROM Card_MST WHERE CARD_NUM IS not null) "

but if i will remove this then how my query will check this cust_no is existing in cust_no column or not where card_num is not null.

i want that list of cust_no where card_num is null and that cust_no should not be present in above cust_no where card_num is null according my requirement this query should be show (106, 104 and 109) cust_no.bcaz these three customer only new customer. is it? :


card_num card_mem_type  id   cust_no  valid_date  re_no
            c           17    106    05/05/2007   6
            y           18    104    06/06/2007   7
            c           19    109    14/07/2007   10



and for 2nd case these following cust_no (103 and 105) is repeating :

card_num card_mem_type  id   cust_no  valid_date  re_no
            c           15    103    07/06/2007   8
            z           16    105    07/07/2007   9



so like that i want result for boh query.

[Mod-edit] added [CODE] and [/CODE] tags.

[Updated on: Mon, 06 August 2007 01:01] by Moderator

Report message to a moderator

Re: sql query [message #256408 is a reply to message #256396] Sat, 04 August 2007 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: sql query [message #256585 is a reply to message #256408] Mon, 06 August 2007 01:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Take your time when you reply, pardeep, and try to write in proper English: "bcaz" is not a real word.

I came up with the following.

The script I used for testing:
CREATE TABLE yourtable ( card_num      NUMBER
                       , card_mem_type VARCHAR2(10)
                       , Id            NUMBER
                       , cust_no       NUMBER
                       , vaild_date    DATE
                       , re_no         NUMBER
                       )
/

INSERT INTO yourtable VALUES ( 1   , 't', 12, 101, TO_DATE('01/01/2007','DD/MM/YYYY'),  1);
INSERT INTO yourtable VALUES ( 2   , 'c', 13, 102, TO_DATE('02/02/2007','DD/MM/YYYY'),  2);
INSERT INTO yourtable VALUES ( 3   , 't', 14, 102, TO_DATE('03/03/2007','DD/MM/YYYY'),  3);
INSERT INTO yourtable VALUES ( 4   , 'y', 15, 103, TO_DATE('04/04/2007','DD/MM/YYYY'),  4);
INSERT INTO yourtable VALUES ( 5   , 't', 16, 105, TO_DATE('05/04/2007','DD/MM/YYYY'),  5);
INSERT INTO yourtable VALUES ( NULL, 'c', 17, 106, TO_DATE('05/05/2007','DD/MM/YYYY'),  6);
INSERT INTO yourtable VALUES ( NULL, 'y', 18, 104, TO_DATE('06/06/2007','DD/MM/YYYY'),  7);
INSERT INTO yourtable VALUES ( NULL, 'c', 15, 103, TO_DATE('07/06/2007','DD/MM/YYYY'),  8);
INSERT INTO yourtable VALUES ( NULL, 'z', 16, 105, TO_DATE('07/07/2007','DD/MM/YYYY'),  9);
INSERT INTO yourtable VALUES ( NULL, 'c', 19, 109, TO_DATE('14/07/2007','DD/MM/YYYY'), 10);

COMMIT
/

SELECT *
FROM   yourtable
/


PROMPT CASE 1: CARD_NUM NULL

SELECT *
FROM   yourtable
WHERE  card_num IS NULL
/

PROMPT CASE 2: CARD_NUM NULL + repeating

SELECT a.*
FROM   yourtable a
   ,   yourtable b
WHERE  a.card_num IS NULL
AND    b.card_num IS NOT NULL
AND    a.cust_no = b.cust_no
/

DROP TABLE yourtable
/


The test run (On Oracle 10g XE):
SQL> @orafaq

Table created.


1 row created.

...<snip>...

Commit complete.


  CARD_NUM CARD_MEM_T         ID    CUST_NO VAILD_DAT      RE_NO
---------- ---------- ---------- ---------- --------- ----------
         1 t                  12        101 01-JAN-07          1
         2 c                  13        102 02-FEB-07          2
         3 t                  14        102 03-MAR-07          3
         4 y                  15        103 04-APR-07          4
         5 t                  16        105 05-APR-07          5
           c                  17        106 05-MAY-07          6
           y                  18        104 06-JUN-07          7
           c                  15        103 07-JUN-07          8
           z                  16        105 07-JUL-07          9
           c                  19        109 14-JUL-07         10

10 rows selected.

CASE 1: CARD_NUM NULL

  CARD_NUM CARD_MEM_T         ID    CUST_NO VAILD_DAT      RE_NO
---------- ---------- ---------- ---------- --------- ----------
           c                  17        106 05-MAY-07          6
           y                  18        104 06-JUN-07          7
           c                  15        103 07-JUN-07          8
           z                  16        105 07-JUL-07          9
           c                  19        109 14-JUL-07         10

CASE 2: CARD_NUM NULL + repeating

  CARD_NUM CARD_MEM_T         ID    CUST_NO VAILD_DAT      RE_NO
---------- ---------- ---------- ---------- --------- ----------
           c                  15        103 07-JUN-07          8
           z                  16        105 07-JUL-07          9


Table dropped.
If you want to exclude the records of case 2 from case 1, I'd still go for a subquery, but if you are reluctant you could opt for MINUS, NOT EXISTS would be another viable option:
SELECT a.*
FROM   yourtable a
WHERE  a.card_num IS NULL
AND    NOT EXISTS ( SELECT 1
                    FROM   yourtable
                    WHERE  card_num IS NOT NULL
                    AND    cust_no = a.cust_no
                  )
/
But it's hard to tell anything about performance without you giving the necessary details:
- number of records
- indexes
- explain plan
- what you expect to be a reasonable response time

MHE
Re: sql query [message #256643 is a reply to message #256585] Mon, 06 August 2007 04:07 Go to previous messageGo to next message
pardeep
Messages: 20
Registered: June 2001
Location: Chennai
Junior Member
Hi Maheer
I am really very very Sorry for using short type wording.And Maheer u given me very good idea.Thank you very much maheer for hellping me.It is working properly. Following query is working properly but in 4-5 million record. will it be ok?

SELECT DISTINCT(A.CUST_NO),
A.Id,
A.VALID_DATE,
A.CARD_MEM_TYPE,
A.RE_No
FROM yourtable a
WHERE a.card_num IS NULL
AND NOT EXISTS ( SELECT cust_no
FROM yourtable
WHERE card_num IS NOT NULL
AND cust_no = a.cust_no
)

But i think it will faster than subquery, isn't it?

and thank again maheer....
please suggest me about this also.
Re: sql query [message #256651 is a reply to message #256643] Mon, 06 August 2007 04:31 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I can't tell from here.
I'd say: run an explain plan on your query. But I'd also check a NOT EXISTS variant.

If you have performance issues you might want to open a thread in the 'Performance Tuning' forum. Make sure you read the sticky in that forum before you start a thread.

Good luck!

MHE
Re: sql query [message #256726 is a reply to message #256643] Mon, 06 August 2007 08:06 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
pardeep wrote on Mon, 06 August 2007 05:07
Hi Maheer
I am really very very Sorry for using short type wording.And Maheer u given me very good idea.


That is pretty funny. A case of the shortest short-term memory loss I have witnessed.
Re: sql query [message #256730 is a reply to message #256726] Mon, 06 August 2007 08:09 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
joy_division wrote on Mon, 06 August 2007 14:06
pardeep wrote on Mon, 06 August 2007 05:07
Hi Maheer
I am really very very Sorry for using short type wording.And Maheer u given me very good idea.


That is pretty funny. A case of the shortest short-term memory loss I have witnessed.

As well as an inability to read someone's name and spell it correctly
Previous Topic: ORA-12518
Next Topic: inconsistent datatypes: expected - got CLOB
Goto Forum:
  


Current Time: Sat Dec 03 16:07:16 CST 2016

Total time taken to generate the page: 0.04446 seconds