Results 1 to 9 of 9

Thread: SQL

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Registered User
    Join Date: Mar:2018
    Location:
    Posts: 93

    SQL

    SQL. , , , , . ? ?

    Code:
    select
        t.barcode,
        t.shift_date,
        t.start_date,
        t.shift,
        t.attribute_2 model,
        bench,
        t.attribute_4 color,
        tff.name kilnFF,
        m.name kiln,
        decode(t.prod_qty, 1, 'GOOD', decode(t.prod_def_qty, 1, 'EP_GR', decode(t.rework_qty, 1, 'REW', 'LOSS'))) status,
        td.defect_id defect,
        td.defect_location_id defect_location,
        sum(t.prod_qty + t.prod_def_qty + t.rework_qty + t.loss_qty) pcs,
        (select mold_position from s_trm_cast_plan s where mold_position in (1, 2, 3 ,4) and s.barcode=t.barcode)
    from
        (select ttsm.* from
            s_tr_tracking ttsm,
            (select tracking_num, max(sequence_num) sequence_num
             from s_tr_tracking
             where start_date between '2018-04-02' and '2018-04-09'
             and process_id = 21
             and attribute_2 = any ('T2812')
            group by tracking_num) tts
        where
            ttsm.tracking_num = tts.tracking_num and
            ttsm.sequence_num = tts.sequence_num) t,
    
        (select tm.tracking_num, tm.sequence_num, mr.name
         from s_tr_tracking_machine tm,
              s_trl_machine_resource mr
         where tm.machine_id = mr.machine_id and
               mr.mach_type = 6) m,
        (select tracking_num, sequence_num, defect_id, defect_location_id
         from s_tr_tracking_defect where main = 1) td,
        (select tt.barcode, mr.name
         from s_tr_tracking tt,
              s_tr_tracking_machine tm,
              s_trl_machine_resource mr
         where tt.process_id = 31 and tt.barcode != (select barcode from s_tr_barcodes where barcode_id = 1) and
               tt.tracking_num = tm.tracking_num and
               tt.sequence_num = tm.sequence_num and
    --           tt.shift_date <= case when to_date('MAY/11/04') < to_date('MAY/20/04') then 'MAY/20/04' else 'MAY/11/04' end and
               tm.machine_id = mr.machine_id and
               mr.mach_type = 6) tff,
    		(select distinct barcode, name bench
    		from s_tr_tracking s,
    				(select tracking_num, sequence_num, name
    				from s_tr_tracking_machine s,
    						(select distinct machine_id, name
    						from t_machines
    						where mach_type != 4
    						and name = 'A10(30)') t
    				where s.machine_id=t.machine_id) t
    		where process_id = 21
    		and s.tracking_num=t.tracking_num
    		and s.sequence_num=t.sequence_num
    		and barcode <> '')b
    where
        t.tracking_num = m.tracking_num (+) and
        t.sequence_num = m.sequence_num (+) and
        t.tracking_num = td.tracking_num (+)  and
        t.sequence_num = td.sequence_num (+) and
        t.barcode = tff.barcode (+) and
        t.barcode=b.barcode
    group by
                      t.barcode,
        t.shift_date,
       t.start_date,
        t.shift,
        t.attribute_2,
        t.attribute_4,
        bench,
        m.name,
        decode(t.prod_qty, 1, 'GOOD', decode(t.prod_def_qty, 1, 'EP_GR', decode(t.rework_qty, 1, 'REW', 'LOSS'))),
        td.defect_id,
        td.defect_location_id,
        tff.name;

  2. #2
    King nothing amd_fan's Avatar
    Join Date: Sep:2003
    Location:
    Posts: 6,625
    ( , ), , . , . , , , null , .
    Asus A320M-K | R5 1600X | 2x8GB Crucial 3200 | RX 480 4GB | Team 256GB M2 | CM 500W | Zalman Case | Sennheiser HD598SR + Creative tactic 3d sigma | Redragon Varuna|MSI 27'' 144hz|Noblechairs Epic| |

  3. #3
    Registered User elby's Avatar
    Join Date: Jul:2007
    Location: Bulgaria
    Posts: 800
    ? ?
    Last edited by elby; 12th September 2018 at 15:45.
    Jetway I35P-SG-PB/C2D E2140@3333MHz BOX,8x415 1,45V/2x1GB Kingmax DDR2 1038MHz/XFX 7600GT 256MB DDR3/Fortron350W

  4. #4
    Math Lover haho's Avatar
    Join Date: Apr:2006
    Location:
    Posts: 695
    @amd_fan, RDBMS / config , - . , subquery subqury ( -, ) ( ).
    Last edited by haho; 12th September 2018 at 15:29.
    It seems that perfection is attained not when there is nothing more to add, but when there is nothing more to remove.

  5. #5
    Registered User
    Join Date: Mar:2018
    Location:
    Posts: 93
    . , . SQL Developer

  6. #6
    Deleted User 4eRNoBiL's Avatar
    Join Date: Oct:2005
    Location:
    Posts: 739
    :
    Code:
    tt.shift_date <= case when to_date('MAY/11/04') < to_date('MAY/20/04') then 'MAY/20/04' else 'MAY/11/04' end
    ?

    , , . ( ) , . , , barcode "", .

    , - . , . , .

    . MySQL , , "Impossible Where".
    Fujistu Lifebook E756 | Core i7-6500U / 400MHz-3.1GHz | 8 GB DDR4-2133 | Samsung PM871 / 256 GB SSD | 15" 1920x1080 | Manjaro Linux + kernel 4.19

  7. #7
    Registered User
    Join Date: Jan:2019
    Location: around
    Posts: 1,973
    ( , ) SQL, Oracle...? , ?
    Last edited by luibitel; 8th February 2019 at 10:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Copyright © 1999-2011 . .
iskamPC.com | mobility.BG | Bloody's Techblog | | 3D Vision Blog |