July 27, 2004 at 8:52 pm
Currently I'm using SQL 2000 SP3. When creating view in SQL 2000, no problem, result will come out within a minutes. but when when deploy at my customer which uses SQL 7, nothing happen. Other view working fine. Except this one which use outer join. Program still showed as "running" For info, server performance not really huge while running this program. Can some one advise me whether complicated outer join will not work at SQL 7 ? Appreciate yr help. Thnks
July 27, 2004 at 9:40 pm
Hi Suhaimij
Can you post the view design here?
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 27, 2004 at 11:22 pm
Since it combination of view, I posted final view. Let me know if u want me to post other view. Thnks
>>>>>>> Final View before pass to Crystal Report.
SELECT REP20_1.*, rep20_3.data_id AS Expr1,
rep20_3.combotext AS Expr2,
rep2.NUMBERSEGM AS Expr3
FROM dbo.report20_3 rep20_3 RIGHT OUTER JOIN
dbo.report20_1 REP20_1 INNER JOIN
dbo.report20_2 rep2 ON REP20_1.INVNO = rep2.INVNO AND
REP20_1.grplineno = rep2.GRPLINENO ON
rep20_3.invno = REP20_1.INVNO
Report20_3 - view
SELECT DISTINCT
rep20_1.INVNO, rep20_1.CUSTCOD, ainv.SPLITREF,
acusd.DATA_ID, sdc.COMBOTEXT
FROM dbo.report20_1_1 rep20_1 INNER JOIN
dbo.ARINV ainv ON rep20_1.INVNO = ainv.INVNO INNER JOIN
dbo.ARSOGRP_CUSTDATA acusd ON
rep20_1.prodcode = acusd.PRODCODE AND
ainv.SPLITREF = acusd.SONO INNER JOIN
dbo.SYSDATACOMBO sdc ON
acusd.DATA_ID = sdc.DATA_ID
WHERE (acusd.DATA_VALUE IS NOT NULL) AND
(sdc.DISPORDER = 2)
Report20_1 - view
SELECT TOP 100 PERCENT t1.INVNO, t1.grplineno, t1.PRECARRIER,
t1.ORG, t1.DES, t1.CARRIERCODE, t1.CREDCRDNBR,
t1.CUSTCOD, t1.DEPDATE, t1.INVDATE, t1.TKTDATE,
t1.PRICEPAID + (isnull(t2.salesvalue, 0) / t1.grpqty)
- isnull(t3.crnoteamt, 0) AS PRICEPAID,
CASE WHEN t1.MISSEDSAVCODE <> 'L' THEN t1.PRICELOW + ((isnull(t2.salesvalue,
0)) / t1.grpqty) ELSE t1.PRICEPAID + ((isnull(t2.salesvalue, 0))
/ t1.grpqty) - isnull(t3.crnoteamt, 0) END AS PRICELOW,
t1.PRICEREF, t1.MISSEDSAVCODE, t1.REALIZEDSACODE,
t1.ORGCURR, t1.PREFCLASS, t1.ROUTING, t1.LEVEL1,
t1.LEVEL2, t1.TKTTYPE, t1.TRAVELLER, t1.TRIPTYPE,
t1.TKTNBR
FROM report20_1_1 t1 LEFT OUTER JOIN
report20_1_2 AS t2 ON t1.soref = t2.soref AND
t1.airtktcode = t2.airtktcode AND
t1.prodcode = t2.prodcode AND t1.org = t2.original AND
t1.des1 = t2.dest LEFT OUTER JOIN
report20_1_3 AS t3 ON t1.TktNbr = t3.ticketno
ORDER BY t1.TRAVELLER
Report20_2 - view
SELECT AI.INVNO, AI.GRPLINENO, MAX(ASEG.ITNNO)
AS NUMBERSEGM
FROM dbo.ARINVSEG ASEG INNER JOIN
dbo.ARINVGRP AI ON ASEG.INVNO = AI.INVNO AND
ASEG.GRPLINENO = AI.GRPLINENO
WHERE (AI.INVNO IN
(SELECT DISTINCT INVNO
FROM REPORT20_1_1)) AND (ASEG.ITTYPE = 'AIR')
GROUP BY AI.INVNO, AI.GRPLINENO
July 27, 2004 at 11:25 pm
Oo yeah, 1 more thing. 1 working fine in SQL Server 2K. regardless of number of record (result) but in SQL 7, below 500 record, it will appear in Crsytal Report but more than that, no record will display.
Thnks
July 28, 2004 at 2:20 am
I take it that you checked the client's source data?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply