This discussion is archived
5 Replies Latest reply: May 24, 2012 9:49 AM by cptkirkh RSS

build query in where clause

cptkirkh Newbie
Currently Being Moderated
is this possible?

select id from v_pass vp, (SELECT eve_id,loc_name,eve_Date,'PASS_'||code pass from v_numbers ) ve
where pass=1

if not then what is thew best way to handle this? Thanks.
  • 1. Re: build query in where clause
    sb92075 Guru
    Currently Being Moderated
    cptkirkh wrote:
    is this possible?
    not possible when you use invalid syntax
    select id from v_pass vp, (SELECT eve_id,loc_name,eve_Date,'PASS_'||code pass from v_numbers ) ve
    where pass=1

    if not then what is thew best way to handle this? Thanks.
    missing FROM clause for outermost SELECT
  • 2. Re: build query in where clause
    Paul Horth Expert
    Currently Being Moderated
    What are you actually trying to do?

    Explain your problem clearly, and provide create table statements, insert statements with sample input and
    the expected output.

    See
    SQL and PL/SQL FAQ
  • 3. Re: build query in where clause
    908002 Expert
    Currently Being Moderated
    possible with execute immediate in pl/sql

    lquery = 'select id from v_pass vp, (SELECT eve_id,loc_name,eve_Date,PASS_'||code||' pass from v_numbers ) ve
    where pass=1'

    not sure how many records it will fetch as per your data.

    if single record

    use execute immediate into <variable>

    if multiple records... use cursor loop / bulk collect.


    But no join condition between v_pass and ve?
  • 4. Re: build query in where clause
    918949 Pro
    Currently Being Moderated
    sb92075 wrote: not possible when you use invalid syntax
    syntax is OK, when eve_id, loc_name, eve_Date, code - the columns from v_numbers, but cartesian product and without output from subquery... :(
    what is the question?
    SELECT id
      FROM v_pass vp,
           (SELECT eve_id,
                   loc_name,
                   eve_Date,
                   'PASS_' || code pass
              FROM v_numbers) ve
     WHERE pass = 1
    and
    pass = 1
    is not realy by
    'PASS_' || code pass
    as result you have no row selected
  • 5. Re: build query in where clause
    cptkirkh Newbie
    Currently Being Moderated
    Ok here goes.. I am trying to find students that live near our classes based upon whether they have already passed one of our courses and if the course is low in attendance. I created a view called v_numbers determines percent registered the program code for the course the lat and long and if this course has less than 80 percent registered for the size of the conference room it will be held in. Our DB is oracle 10gr2.

    This view as created by this statement.
    CREATE OR REPLACE FORCE VIEW MY.V_NUMBERS
    (
       EVE_RID,
       EVE_EVENT_CODE,
       LOC_NAME,
       LOC_ST_ADDRESS,
       LOC_ST_STATE,
       LOC_ST_ZIP_CODE,
       EVE_LONG,
       EVE_LAT,
       SEATS_AVAILABLE,
       REGISTERED,
       PERCENT_REG,
       PRG_CODE,
       EX_DESIG,
       EVE_START_DATE,
       ISRMG
    )
    AS
         SELECT eve_rid,
                eve_event_code,
                loc_name,
                loc_st_Address,
                loc_st_state,
                loc_st_zip_code,
                LOC_GEO_LONG_RADS eve_long,
                LOC_GEO_LAT_RADS eve_lat,
                eve_num_seats - actual_seats seats_available,
                REGISTERED,
                TO_NUMBER (TO_CHAR ( (REGISTERED / eve_num_seats * 100), 999))
                   percent_reg,
                prg_code,
                DECODE (
                   prg_des_rid,
                   1, DECODE (
                         prg_ruble,
                         'YES', DECODE (prg_code,
                                        'RGS', 'RUBLE',
                                        'RMG', 'RMG',
                                        'RUBLE'),
                         DECODE (prg_required, 'YES', 'CIC', 'RUBLE')),
                   2, DECODE (prg_required, 'YES', 'CISR', 'ALS'),
                   des_designation_type)
                   ex_desig,
                eve_start_date,
                DECODE (prg_code, 'RMG', 1, 0) isrmg
           FROM locations_test,
                event a,
                s_event_totals,
                program,
                designations
          WHERE     loc_rid = eve_loc_rid
                AND eve_rid = reg_eve_rid
                AND a.eve_program_id = prg_rid
                AND prg_des_rid = des_rid
                AND eve_start_date BETWEEN SYSDATE + 14 AND SYSDATE + 32
                AND eve_inc_account IN ('SS', 'SSIHOE', 'LSIHOERS')
                AND loc_st_state NOT IN ('MN', 'MX')
       GROUP BY eve_rid,
                registered,
                eve_num_seats,
                actual_seats,
                eve_event_code,
                loc_name,
                loc_st_Address,
                loc_st_state,
                loc_st_zip_code,
                LOC_GEO_LONG_RADS,
                prg_code,
                prg_des_rid,
                prg_ruble,
                prg_required,
                des_designation_type,
                eve_start_date,
                LOC_GEO_LAT_RADS
         HAVING TO_NUMBER (TO_CHAR ( (REGISTERED / eve_num_seats * 100), 999)) <
                   80;
    sample data form v_numbers


    EVE_RID     EVE_EVENT_CODE     LOC_NAME     LOC_ST_ADDRESS     LOC_ST_STATE     LOC_ST_ZIP_CODE     EVE_LONG     EVE_LAT     SEATS_AVAILABLE     REGISTERED     PERCENT_REG     PRG_CODE     EX_DESIG     EVE_START_DATE     ISRMG
    1234     12345ABC     Doubletree      8250 N Central Expy      TX     75206     -2     1     5     15     75     SR     DSR     06/14/2012     0

    my other view that was created lists those students their location and what they have passed.
    CREATE OR REPLACE FORCE VIEW MY.V_PASS
    (
       CLI_RID,
       LAST_ATTENDANCE,
       CLI_MAIN_LONG_RADS,
       CLI_MAIN_LAT_RADS,
       PASS_CC,
       PASS_CP,
       PASS_LH,
       PASS_PL,
       PASS_AM,
       PASS_PR,
       PASS_PA,
       PASS_IP,
       PASS_IC,
       PASS_AO,
       PASS_RME,
       PASS_RA,
       PASS_RC,
       PASS_RF,
       PASS_AD,
       PASS_MEA,
       PASS_FSR,
       PASS_ADM,
       PASS_FUN,
       PASS_HAN
    )
    AS
       SELECT cl.cli_rid,
              SC.last_attendance,
              CLI_MAIN_LONG_RADS,
              CLI_MAIN_LAT_RADS,
              PASS_CC,
              PASS_CP,
              PASS_LH,
              PASS_PL,
              PASS_AM,
              PASS_PR,
              PASS_PA,
              PASS_IP,
              PASS_IC,
              PASS_AO,
              PASS_RME,
              PASS_RA,
              PASS_RC,
              PASS_RF,
              PASS_AD,
              PASS_MEA,
              PASS_FSR,
              PASS_ADM,
              PASS_FUN,
              PASS_HAN
         FROM s_client SC, CLIENTS_LOCATION CL
        WHERE SC.CLI_RID = CL.CLI_RID AND SC.last_attendance > SYSDATE - 1825;
    Here is a sample data from v_pass:
    CLI_RID     LAST_ATTENDANCE     CLI_MAIN_LONG_RADS     CLI_MAIN_LAT_RADS     PASS_CC     PASS_CP     PASS_LH     PASS_PL     PASS_AM     PASS_PR     PASS_PA     PASS_IP     PASS_IC     PASS_AO     PASS_RME     PASS_RA     PASS_RC     PASS_RF     PASS_AD     PASS_MEA     PASS_FSR     PASS_ADM     PASS_FUN     PASS_HAN
    12345     11/22/2009     -1.41409218263485     0.509782870830048     0     1     0     0     1     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0
    For example if they have a passed the class with the program code of AO then it will have a 1. Now i have to go through each course low in attendance. Determine if they have passed and then make sure they live within 100 miles of the course. Each course has longtitude and latitude in radians on the address associated with each. i am using a flatearth function that computes if they live within a certain distance. Once i have that i then need to go back to our list of courses and find two more courses in the area that fits their needs. What i have done is created a query that takes the courses in need and then finds all of the courses within 100 miles that are in the next 6 months.

    that query is seen here.
      
    select et.eve_rid event_nearby,en.eve_rid event_in_need from v_event_numbers en,event et,locations_test where et.eve_loc_rid = loc_rid and 
                f_flatearthrad (LOC_GEO_LAT_RADS,LOC_GEO_LONG_RADS,EVE_LAT,EVE_LONG)<100
                and et.eve_rid <> en.eve_rid
                and et.eve_Start_date between en.eve_start_date and sysdate + 180
                group by et.eve_rid,en.eve_rid
    The only tables here that you haven't seen are the event and locations_test tables. The event tables list all of our courses and the locations_test lists all of the locations for our courses.

    Where i am stumped at is the best way to go through each event and first find all of those who have not passed it yet and live within 100 miles. Then find them two more events that they can take in the next 6 months that they have not passed. my output needs to be in the format like this:

    id_1, name, address,event_1,event_2,event_3
    id_1,name,address,event_1,event_2,event_3

    any thoughts about the best way to solve this would be great. Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points