Home » SQL & PL/SQL » SQL & PL/SQL » selection in columns
selection in columns [message #285554] Wed, 05 December 2007 00:50 Go to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

Hi Experts

see the coding first
SELECT D_CODE, NAME, ADDRESS1, VISIT1, VISIT2, VISIT3, VISIT4, VISIT5, VISIT6, VISIT7 
FROM DEALER_INFO 
WHERE VISIT3 IS NOT NULL 
AND SUBSTR(D_CODE,1,2)='01' 
AND SUBSTR(D_CODE,3,6)='ACL34-';

results is
D_CODE          NAME                                     ADDRESS1                                 V V V V V V V
--------------- ---------------------------------------- ---------------------------------------- - - - - - - -
01ACL34-003     COOL POINT(FOC)                          MAIN BUS STOP LIAQUATABAD NO 1           Y   Y
01ACL34-017     BISMILLAH GENERAL(FOC) STORE             3/607 LIAQUATABAD.                       Y   Y
01ACL34-058     TARIQ BROTHERS(FOC)26-9-97               SHOP NO-5 AL NASEER SQUARE               Y   Y
01ACL34-061     NEW PARADISE BAK(FOC)2-3-98              R-11/1 F.B AREA                          Y   Y
01ACL34-062     AL MOHSIN MED &(FOC)4-4-98               RAJA APPARTMENT SHOP # 1 BLOCK-1         Y   Y
01ACL34-085     NEW KARIM BAKERY FOC                     HOUSE 700/4                              Y   Y
01ACL34-087     NEW BLUE LINE FOC C\SPOT                 BLOCK #4 AL-KARAM SQUARE                 Y   Y   Y
01ACL34-504     AL-AZIZ BAKERS FOC                       A/961-4                                  Y   Y
01ACL34-508     MANAMA BAKERY(FOC)11-3-98                F/9/9-A, AL-AZAM SQUARE,LIAQAT ABAD      Y   Y
01ACL34-701     ERUM BAKERY(FOC)10-11-96                 4/390,IBNE-SEENA                         Y   Y

its a simple query and visit1-visit7 are the 7 visit days in a week. Problem is i want to make a report which is independent on visit days not like fixed report like ( visit3 is not null). In the above mension techniq i have to fixed the required column, so the report is just only for that perticular visit day. for others i have to make another report. So my question here is what should i do to get out of this fixation.

Regards

Anwer

[Updated on: Wed, 05 December 2007 01:17] by Moderator

Report message to a moderator

Re: selection in columns [message #285558 is a reply to message #285554] Wed, 05 December 2007 00:59 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Your query is not formatted!

You can use OR bewteen conditions.

select * from tab_name
where col1 is not null or col2 is not null


Kiran.

[Updated on: Wed, 05 December 2007 00:59]

Report message to a moderator

Re: selection in columns [message #285581 is a reply to message #285558] Wed, 05 December 2007 01:37 Go to previous messageGo to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

thank you for repling

but the thins is to understand is i dont want to fixed the columns in the query. its variable and it may be visit1 or may be visit5. its truely depend on user choice.

In your given solution if i fixed all the visit days and use OR operator then it may give me all the rown in the table which is exactly not my requirment.

i just need those rows which is truly meet the critaria but the column VISIT1- VISIT7 are depend on user choice. and you can say VISIT is ????

Any idea ?? any other solution


Regards
Anwer
Re: selection in columns [message #285582 is a reply to message #285581] Wed, 05 December 2007 01:39 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT D_CODE, NAME, ADDRESS1, VISIT1, VISIT2, VISIT3, VISIT4, VISIT5, VISIT6, VISIT7 
FROM DEALER_INFO 
WHERE SUBSTR(
    NVL(VISIT1, ' ') ||
    NVL(VISIT2, ' ') ||
    NVL(VISIT3, ' ') ||
    NVL(VISIT4, ' ') ||
    NVL(VISIT5, ' ') ||
    NVL(VISIT6, ' ') ||
    NVL(VISIT7, ' ')
, :VISITNO, 1) = 'Y'
AND SUBSTR(D_CODE,1,2)='01' 
AND SUBSTR(D_CODE,3,6)='ACL34-';


Ross Leishman

[Updated on: Wed, 05 December 2007 01:40]

Report message to a moderator

Previous Topic: how to grant privilege on another schema to a user
Next Topic: Cusor and if statement
Goto Forum:
  


Current Time: Mon Nov 04 12:52:24 CST 2024