June 30, 2005 at 12:28 am
Anybody who can help me solving this puzzle ... please its critical and urgent ...
Documents have revision number but Transmittals do not ... in this case.
-------------------------------------
Create Table "E0362CSD" ( CSD_ORIG Char( 2 ), CSD_SUBJ Char( 4 ), CSD_TYPE Char( 1 ), CSD_NUMB Char( 4 ), CSD_REVI Char( 1 ), CSD_LABL Char( 50 ), CSD_STAGE Char( 1 ), CSD_ISSU Date, CSD_ALTR Char( 22 ), CSD_REQD Date, CSD_OWNR Char( 5 ), CSD_TDES Char( 6 ), CSD_ACLAS Char( 1 ));
INSERT INTO "E0362CSD" VALUES( 'BS', 'MSUB', 'M', '0001', '0', 'ROCKS FROM AL SHAALI CRUSHERS - 0001', NULL, '2003-09-17', 'BSMSUB00010', NULL, 'BESIX', NULL, 'M' );
INSERT INTO "E0362CSD" VALUES( 'BS', 'TRAN', 'T', '0003', '4', 'TRANSMITTAL', NULL, '2003-09-18', 'BSTRAN00034', NULL, 'BESIX', NULL, 'T' );
INSERT INTO "E0362CSD" VALUES( 'NA', 'TRAN', 'T', '0000', '1', 'TRANSMITTAL', NULL, '2003-09-20', 'DT/001', NULL, 'NAKHE', NULL, 'T' );
INSERT INTO "E0362CSD" VALUES( 'BS', 'MSUB', 'M', '0002', '0', 'ROCKS FROM BARTAWI CRUSHERS', NULL, '2003-09-17', 'BSMSUB00020', NULL, 'BESIX', NULL, 'M' );
INSERT INTO "E0362CSD" VALUES( 'BS', 'TRAN', 'T', '0005', '0', 'TRANSMITTAL', NULL, '2003-09-18', 'BSTRAN00050', NULL, 'BESIX', NULL, 'T' );
INSERT INTO "E0362CSD" VALUES( 'NA', 'TRAN', 'T', '0000', '2', 'TRANSMITTAL', NULL, '2003-09-20', 'DT 001', NULL, 'NAKHE', NULL, 'T' );
INSERT INTO "E0362CSD" VALUES( 'BS', 'MSUB', 'M', '0012', '0', '10mm AGGREGATES - COARSE AGGREGATE FOR CONCRETE', NULL, '2003-09-22', 'BSMSUB00120', NULL, 'BESIX', NULL, 'M' );
INSERT INTO "E0362CSD" VALUES( 'BS', 'TRAN', 'T', '0002', '3', 'TRANSMITTAL', NULL, '2003-09-22', 'BSTRAN00023', NULL, 'BESIX', NULL, 'M' );
No entry for this, coz no reply ...
Indexes
Main Uniq: csd_orig + csd_subj + csd_type + csd_numb + csd_revi
-------------------------------------
Create Table "E0362APR" ( APR_DOCU Char( 12 ), APR_PART Char( 5 ), APR_TYPE Char( 1 ), APR_TRN2 Char( 12 ), APR_STAT Char( 1 ), APR_REQD Date);
INSERT INTO "E0362APR" VALUES( 'BSMSUBM00010', 'NAKHE', 'A', 'NATRANT00001', '3', '2003-09-24' );
INSERT INTO "E0362APR" VALUES( 'BSMSUBM00020', 'NAKHE', 'A', 'NATRANT00002', '3', '2003-09-24' );
INSERT INTO "E0362APR" VALUES( 'BSMSUBM00120', 'NAKHE', 'A', NULL, NULL, '2003-09-29' );
Indexes
Main Uniq: apr_docu + apr_part
-------------------------------------
Create Table "E0362TRA" ( TRA_TRNO Char( 12 ), TRA_DRGN Char( 12 ), TRA_MEDI Char( 2 ), TRA_COPY Numeric( 2 ,0 ), TRA_APPR Logical, TRA_STAG Char( 1 ), TRA_PART Char( 5 ), TRA_TYPE Char( 1 ));
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00034', 'BSMSUBM00010', 'A4', 1, True, NULL, 'NAKHE', 'A' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00034', 'BSMSUBM00010', NULL, 0, False, NULL, 'FSP', 'I' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00034', 'BSMSUBM00010', NULL, 0, False, NULL, 'RECOR', 'F' );
INSERT INTO "E0362TRA" VALUES( 'NATRANT00001', 'BSMSUBM00010', 'A4', 1, True, NULL, 'BESIX', 'I' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00050', 'BSMSUBM00020', 'A4', 1, True, NULL, 'NAKHE', 'A' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00050', 'BSMSUBM00020', NULL, 0, False, NULL, 'FSP', 'I' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00050', 'BSMSUBM00020', NULL, 0, False, NULL, 'RECOR', 'F' );
INSERT INTO "E0362TRA" VALUES( 'NATRANT00002', 'BSMSUBM00020', 'A4', 1, False, NULL, 'BESIX', 'I' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00023', 'BSMSUBM00120', 'A4', 1, True, NULL, 'NAKHE', 'A' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00023', 'BSMSUBM00120', NULL, 0, False, NULL, 'FSP', 'I' );
INSERT INTO "E0362TRA" VALUES( 'BSTRANT00023', 'BSMSUBM00120', NULL, 0, False, NULL, 'RECOR', 'F' );
No entry for this, coz no reply ...
Indexes
Main Uniq: tra_trno + tra_part + tra_drgn
-------------------------------------
Create Table "E0362TRD" ( TRD_TRNO Char( 12 ), TRD_PART Char( 5 ), TRD_LOCK Logical, TRD_COPY Numeric( 2 ,0 ), TRD_MEDI Char( 2 ), TRD_TYPE Char( 1 ), TRD_CC Char( 1 ), TRD_RECD Date, TRD_DSET Char( 3 ));
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00034', 'NAKHE', NULL, 1, 'A4', 'A', '0', '2003-09-18', NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00034', 'RECOR', NULL, 1, 'A4', 'F', '1', NULL, NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00034', 'FSP', NULL, 1, 'A4', 'I', '1', NULL, NULL );
INSERT INTO "E0362TRD" VALUES( 'NATRANT00001', 'BESIX', NULL, 1, 'A4', 'I', '0', '2003-09-22', NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00050', 'NAKHE', NULL, 1, 'A4', 'A', '0', '2003-09-18', NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00050', 'RECOR', NULL, 1, 'A4', 'F', '1', NULL, NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00050', 'FSP', NULL, 1, 'A4', 'I', '1', NULL, NULL );
INSERT INTO "E0362TRD" VALUES( 'NATRANT00002', 'BESIX', NULL, 1, 'A4', 'I', '0', '2003-09-22', NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00023', 'NAKHE', NULL, 2, 'A4', 'A', '0', '2003-09-23', NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00023', 'RECOR', NULL, 1, 'A4', 'F', '1', NULL, NULL );
INSERT INTO "E0362TRD" VALUES( 'BSTRANT00023', 'FSP', NULL, 1, 'A4', 'I', '1', NULL, NULL );
No entry for this, coz no reply ...
Indexes
Main Uniq: trd_trno + trd_cc + trd_part
-------------------------------------
Now I want this above info for all the three documents in a fashion shown below
Doc_No - DOC_DESC - DOC_ISSUE - TRAN_NO - TRAN_ISSUE - TRAN_RECD - REPLY_EXPECTED - REPLYTRAN_NO - REPLYTRAN_ISSUE - REPLYTRAN_RECD - DOC_STATUS
Material Submittal Report has to look like this with the final query ...
Doc_No , DOC_DESC , DOC_ISSUE , TRAN_NO , TRAN_ISSUE , TRAN_RECD , REXPECTED , REPLYNO , REPLYISSUE , REPLY_RECD , DOC_STATUS
csd_altr , csd_labl , csd_issu , csd_altr , csd_issu , trd_recd , apr_reqd , csd_altr , csd_issu , trd_recd , apr_stat
BSMSUB00010 , ROCKS FROM AL SHAALI CRUSHERS - 0001 , 2003-09-17 , BSTRAN00034 , 2003-09-18 , 2003-09-18 , 2003-09-24 , DT/001 , 2003-09-20 , 2003-09-22 , 3
BSMSUB00020 , ROCKS FROM BARTAWI CRUSHERS , 2003-09-17 , BSTRAN00050 , 2003-09-18 , 2003-09-18 , 2003-09-24 , DT 001 , 2003-09-20 , 2003-09-22 , 3
BSMSUB00120 , 10mm AGGREGATES - COARSE AGGREGATE FOR CONCRETE , 2003-09-22 , BSTRAN00023 , 2003-09-22 , 2003-09-23 , 2003-09-29 , Nil , Nil , Nil , Nil
...
...
...
Query I tried is like this ...
But does not give me complete result ... Reply Transmittal Alternate Number, Reply Transmittal Issue Date & Reply Transmittal Received Date
-------------------------------
select
csd1.csd_altr, csd1.csd_labl, csd1.csd_issu,
csd2.csd_altr, csd2.csd_issu,
trd1.trd_recd,
apr1.apr_reqd, apr1.apr_trn2, apr1.apr_stat
from
e0362csd csd1,
e0362tra tra1,
e0362csd csd2,
e0362trd trd1,
e0362apr apr1
where
csd1.csd_orig = 'BS' and
csd1.csd_subj = 'MSUB' and
csd1.csd_type = 'M' and
tra1.tra_trno = csd2.csd_orig + csd2.csd_subj + csd2.csd_type + csd2.csd_numb + csd2.csd_revi and
tra1.tra_drgn = csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi and
tra1.tra_part = 'NAKHE' and
tra1.tra_type = 'A' and
trd1.trd_trno = tra1.tra_trno and
trd1.trd_part = tra1.tra_part and
trd1.trd_cc = '0' and
trd1.trd_type = 'A' and
apr1.apr_docu = tra1.tra_drgn and
apr1.apr_part = tra1.tra_part and
apr1.apr_type = 'A'
order by
csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi
neerajjariwala.besix@gmail.com
Jari
July 7, 2005 at 1:17 am
Not sure I understand the problem fully, but can you clear up a couple of things:
Is this running in SQL Server? - Reason being you have used 2 datatypes 'date' and 'logical' that do not exist in SQl Server.
Changing the data types to create the tables and changing all the trues to 1, falses to 0 lets the query run, and it returns 3 rows which is what I believe you were expecting - can you elaborate?
If you are forced to use those table names you have my sympathy - working out what they mean must be a nightmare!
"But does not give me complete result ... Reply Transmittal Alternate Number, Reply Transmittal Issue Date & Reply Transmittal Received Date" --
Are these extra columns you want - ie is the 3 roews correct - if so which columns are these.
Mike
July 7, 2005 at 11:45 am
Please post this in the appropriate forum. this is for the Question of the Day.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply