How to implement oracle query to select from different tables in laravel

Yes it is possible to run it in DB:raw, just add DB:raw("your query here"); and is should work.

I would suggest either giving you tables better names or at least aliases to make the query more readable.

@TRAY2 - At least for now I don't want to rename tables since they are directly used by my oracle database.

Although I do not get any error when running this query, my temp table doesn't populated (it should do however!). What else could I do?

DB::raw("insert into temp (object_type, oblect_id)
        select distinct hrp1001_cg.object_type, hrp1001_cg.object_id from hrp1001_sc, hrp1001_cg, config
        where
        (hrp1001_sc.object_type = 'cg')
        and
        (hrp1001_sc.rel_obj_type = 'sc')
        and
        (hrp1001_sc.rel_obj_id = config.sc)
        and
        ((hrp1001_sc.st_date < config.des_date) and (hrp1001_sc.end_date > config.des_date))
        and
        (hrp1001_cg.rel_obj_id = hrp1001_sc.object_id)
        and
        ((hrp1001_cg.object_type ='cg') or (hrp1001_cg.object_type ='sc'))
        order by hrp1001_cg.object_id;");

@TRAY2 - I want to write a code like the following but gives me no results...

$data = DB::table('hrp1001_sc')
            ->join('config', 'config.sc', '=', 'hrp1001_sc.rel_obj_id')
            ->join('config', 'config.des_date', '>', 'hrp1001_sc.st_date')
            ->join('config', 'config.des_date', '<', 'hrp1001_sc.end_date')
            ->join('hrp1001_cg', 'hrp1001_cg.rel_obj_id', '=', 'hrp1001_sc.object_id')
            ->where('hrp1001_sc.object_type', '=', 'cg')
            ->where('hrp1001_sc.rel_obj_type', '=', 'sc')
            ->select('hrp1001_sc.object_id')
            //->distinct()
            ->get();

Try using DB::insertinstead of DB::raw.

https://laracasts.com/discuss/channels/laravel/raw-insert-query

@TRAY2 - When just replacing raw with insert I get this error

Symfony \ Component \ Debug \ Exception \ FatalThrowableError (E_PARSE)
syntax error, unexpected 'cg' (T_STRING), expecting ',' or ')'

And when replacing 'cg' with "cg" I get error again:

Error Code : 911 Error Message : ORA-00911: invalid character Position : 606 Statement : insert into temp (object_type, oblect_id) select distinct hrp1001_cg.object_type, hrp1001_cg.object_id from hrp1001_sc, hrp1001_cg, config where (hrp1001_sc.object_type = "cg") and (hrp1001_sc.rel_obj_type = "sc") and (hrp1001_sc.rel_obj_id = config.sc) and ((hrp1001_sc.st_date < config.des_date) and (hrp1001_sc.end_date > config.des_date)) and (hrp1001_cg.rel_obj_id = hrp1001_sc.object_id) and ((hrp1001_cg.object_type ="cg") or (hrp1001_cg.object_type ="sc")) order by hrp1001_cg.object_id; Bindings : [] (911)

I have started to wonder if it is possible to do it. I have tried many many combinations with no success...

Do you really need to put it in a temp table? Can't you just create a view and query that?

@TRAY2 - In fact this is a part of a bigger query. If I succeed with this excerpt I wil proceed with my code. Temp data will be used afterwards. Now I'm badly stuck and cannot proceed with my work.

There is obviously something fishy in you SQL. Why not try something simple and get that to work first?

INSERT INTO temp (object_type, object_id) 
SELECT cg.object_type, cg.object_id
FROM hrp1001_cg cg;

If you get that to work you should be able to add a more complex query.

Otherwise you can try creating a procedure in the database and call it in a raw query. Something like

DB::raw('EXECUTE my_sweet_procedure');

I don't have an Oracle DB at home and at work I can't access it that way so I'm not able to test.

@TRAY2 - Thank you for your advice! The ideal would be to make the DB:raw to work somehow . I can't understand what's wrong with it. I've asked for help at stackoverflow too, but you are the one and only who has responded. Thank you so much for that.

I didn't know about the db procedures. I'll try to search about how to. I have to find a way fast.

@TRAY2 - Finally I figured out the solution. It was simple. My mistake was that I was directly inserting the whole query into DB. I should have first assign it to string. So below is my code excerpt which is tested and working.

    $q = "insert into temp(object_type, object_id)";
    $q = $q."select distinct hrp1001_cg.object_type, hrp1001_cg.object_id from 
    hrp1001_cg, hrp1001_sc, config where";
    $q =$q." hrp1001_sc.object_type = 'CG'";
    $q = $q." and hrp1001_sc.rel_obj_type = 'SC'";
    $q = $q." and hrp1001_sc.rel_obj_id = config.sc";
    $q = $q." and hrp1001_sc.st_date < config.des_date";
    $q = $q." and hrp1001_sc.end_date > config.des_date";
    $q = $q." and hrp1001_cg.rel_obj_id = hrp1001_sc.object_id";
    $q = $q." and";
    $q = $q." ((hrp1001_cg.object_type = 'CG') or (hrp1001_cg.object_type = 'SM'))";

    $xx = DB::insert($q);

I'm glad you solved it :)

@TRAY2 - Me too ;). I'd be much happier if I could do it with eloquent and no raws, but still it's a decent solution. At least I'm declaring tables with schemas. I have a long way to go!