Results 1 to 9 of 9

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 ïî subquåry (ïî÷âàéêè îò íàé-âúòðåøíîòî, íàâúíêà) çà ðàçëèêè â ðåçóëòàòà (ãëåäàì ÷å íå ñà ìàëêî).
    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 | Ìàãàçèí çà åëåêòðîííè öèãàðè