$ cat sql.txt
0x000007FEB0E701C0 : 7365 6C65 6374 2063 7573 746E 6F2C 2020 select custno,
0x000007FEB0E701D0 : 696E 765F 7472 6E73 2E74 7261 6E6E 6F2C inv_trns.tranno,
0x000007FEB0E701E0 : 2069 6E76 5F74 726E 732E 7479 7065 2C20 inv_trns.type,
0x000007FEB0E701F0 : 696E 765F 7472 6E73 2E72 6566 6572 656E inv_trns.referen
0x000007FEB0E70200 : 6365 2C20 696E 765F 7472 6E73 2E70 6172 ce, inv_trns.par
0x000007FEB0E70210 : 742C 2020 2063 6173 6520 2069 6E76 5F74 t, case inv_t
0x000007FEB0E70220 : 726E 732E 636F 6E64 6974 696F 6E20 2077 rns.condition w
0x000007FEB0E70230 : 6865 6E20 274E 2720 7468 656E 2027 4E65 hen 'N' then 'Ne
0x000007FEB0E70240 : 7727 2020 2020 7768 656E 2027 5227 2074 w' when 'R' t
0x000007FEB0E70250 : 6865 6E20 2752 6566 2720 2020 2077 6865 hen 'Ref' whe
0x000007FEB0E70260 : 6E20 2747 2720 7468 656E 2027 5265 6627 n 'G' then 'Ref'
0x000007FEB0E70270 : 2020 2020 7768 656E 2027 4427 2074 6865 when 'D' the
0x000007FEB0E70280 : 6E20 2744 6566 2720 2020 2077 6865 6E20 n 'Def' when
0x000007FEB0E70290 : 2753 2720 7468 656E 2027 5363 7261 7027 'S' then 'Scrap'
0x000007FEB0E702A0 : 2020 2020 7768 656E 2027 5427 2074 6865 when 'T' the
0x000007FEB0E702B0 : 6E20 2754 7261 6465 696E 2720 2020 2065 n 'Tradein' e
0x000007FEB0E702C0 : 6C73 6520 2069 6E76 5F74 726E 732E 636F lse inv_trns.co
0x000007FEB0E702D0 : 6E64 6974 696F 6E20 2065 6E64 2061 7320 ndition end as
0x000007FEB0E702E0 : 636F 6E64 6974 696F 6E2C 2020 2028 7365 condition, (se
0x000007FEB0E702F0 : 6C65 6374 2064 6973 7469 6E63 7420 6465 lect distinct de
0x000007FEB0E70300 : 7363 5F65 6E67 2020 6672 6F6D 2070 726F sc_eng from pro
0x000007FEB0E70310 : 645F 6D61 7320 7768 6572 6520 7072 6F64 d_mas where prod
0x000007FEB0E70320 : 7563 7420 3D20 696E 765F 7472 6E73 2E70 uct = inv_trns.p
0x000007FEB0E70330 : 6172 7429 2061 7320 6465 7363 5F65 6E67 art) as desc_eng
0x000007FEB0E70340 : 2C20 2020 2873 656C 6563 7420 6469 7374 , (select dist
0x000007FEB0E70350 : 696E 6374 2070 726F 645F 7479 7065 2066 inct prod_type f
0x000007FEB0E70360 : 726F 6D20 7072 6F64 5F6D 6173 2077 6865 rom prod_mas whe
0x000007FEB0E70370 : 7265 2070 726F 6475 6374 203D 2069 6E76 re product = inv
0x000007FEB0E70380 : 5F74 726E 732E 7061 7274 2920 6173 2070 _trns.part) as p
0x000007FEB0E70390 : 726F 645F 7479 7065 2C20 2020 696E 765F rod_type, inv_
0x000007FEB0E703A0 : 7472 6E73 2E71 7561 6E74 6974 792C 2063 trns.quantity, c
0x000007FEB0E703B0 : 6861 7228 696E 765F 7472 6E73 2E74 6461 har(inv_trns.tda
0x000007FEB0E703C0 : 7465 2C55 5341 2920 6173 2074 6461 7465 te,USA) as tdate
0x000007FEB0E703D0 : 2C20 2020 6361 7365 2069 6E76 5F74 726E , case inv_trn
0x000007FEB0E703E0 : 732E 7479 7065 2020 7768 656E 2027 4927 s.type when 'I'
0x000007FEB0E703F0 : 2074 6865 6E20 2749 6E73 7461 6C6C 2720 then 'Install'
0x000007FEB0E70400 : 2020 2077 6865 6E20 274D 2720 7468 656E when 'M' then
0x000007FEB0E70410 : 2027 4D61 6327 2020 656E 6420 6173 2074 'Mac' end as t
0x000007FEB0E70420 : 7970 6564 6573 632C 2020 2063 6173 6520 ypedesc, case
0x000007FEB0E70430 : 696E 765F 7472 6E73 2E74 7261 6E73 6163 inv_trns.transac
0x000007FEB0E70440 : 746E 2020 7768 656E 2027 4927 2074 6865 tn when 'I' the
0x000007FEB0E70450 : 6E20 2749 7373 7565 2720 2020 2077 6865 n 'Issue' whe
0x000007FEB0E70460 : 6E20 2752 2720 7468 656E 2027 5265 6364 n 'R' then 'Recd
0x000007FEB0E70470 : 2720 2065 6E64 2061 7320 7472 616E 7361 ' end as transa
0x000007FEB0E70480 : 6374 6E2C 2020 2069 6E76 5F74 726E 732E ctn, inv_trns.
0x000007FEB0E70490 : 7464 6174 6520 6173 2074 6461 7465 322C tdate as tdate2,
0x000007FEB0E704A0 : 2020 2020 2873 656C 6563 7420 636F 756E (select coun
0x000007FEB0E704B0 : 7428 2A29 2066 726F 6D20 7072 6F64 5F70 t(*) from prod_p
0x000007FEB0E704C0 : 6B67 2077 6865 7265 2070 726F 6475 6374 kg where product
0x000007FEB0E704D0 : 203D 2069 6E76 5F74 726E 732E 7061 7274 = inv_trns.part
0x000007FEB0E704E0 : 2920 6173 2070 6B67 636F 756E 7420 2066 ) as pkgcount f
0x000007FEB0E704F0 : 726F 6D20 765F 696E 765F 7472 6E73 2069 rom v_inv_trns i
0x000007FEB0E70500 : 6E76 5F74 726E 7320 2020 2057 4845 5245 nv_trns WHERE
0x000007FEB0E70510 : 2069 6E76 5F74 726E 732E 4355 5354 4E4F inv_trns.CUSTNO
0x000007FEB0E70520 : 203D 2734 3832 3732 3127 2061 6E64 2069 ='482721' and i
0x000007FEB0E70530 : 6E76 5F74 726E 732E 7479 7065 203D 2753 nv_trns.type ='S
0x000007FEB0E70540 : 2720 616E 6420 7265 6665 7265 6E63 6520 ' and reference
0x000007FEB0E70550 : 3D20 2757 3435 3035 3827 206F 7264 6572 = 'W45058' order
0x000007FEB0E70560 : 2062 7920 7464 6174 6532 2020 2064 6573 by tdate2 des
0x000007FEB0E70570 : 6320 2066 6574 6368 2066 6972 7374 2032 c fetch first 2
0x000007FEB0E70580 : 3030 3020 726F 7773 206F 6E6C 79 000 rows only
$ cut -c 65- sql.txt | tr -d '\n' > myfile.sql
$ cat myfile.sql
ect custno,_trns.tranno,_trns.type,_trns.referen inv_trns.parcase inv_t.condition w 'N' then 'Newhen 'R' t 'Ref' wheG' then 'Ref'n 'D' theDef' when then 'Scrap'n 'T' theTradein' e inv_trns.cotion end asdition, (set distinct deeng from proas where prod = inv_trns.p) as desc_engselect distt prod_type f prod_mas wheproduct = invns.part) as p_type, inv_s.quantity, c(inv_trns.tdaUSA) as tdatease inv_trnype when 'I'n 'Install'n 'M' thenc' end as tdesc, case_trns.transacwhen 'I' theIssue' wheR' then 'Recdnd as transa, inv_trns.te as tdate2,lect coun) from prod_pwhere productnv_trns.parts pkgcount f v_inv_trns itrns WHERE_trns.CUSTNO82721' and itrns.type ='Snd referenceW45058' ordertdate2 desetch first 2nly
---------- Post updated at 12:13 AM ---------- Previous update was at 12:10 AM ----------
@ itkamaraj : it has bit issue in SPACES
Code:
$ awk '{for(i=11;i<=NF;i++){if(i==11){printf("%s ",$i)}else {printf(" %s",$i)}}}' sql.txt
select custno,inv_trns.tranno, inv_trns.type, inv_trns.referen ce, inv_trns.part, case inv_trns.condition when 'N' then 'New' when 'R' then 'Ref' when 'G' then 'Ref'when 'D' then 'Def' when'S' then 'Scrap'when 'T' then 'Tradein' else inv_trns.condition end ascondition, (select distinct desc_eng from prod_mas where product = inv_trns.part) as desc_eng, (select distinct prod_type from prod_mas where product = inv_trns.part) as prod_type, inv_trns.quantity, char(inv_trns.tda te,USA) as tdate, case inv_trns.type when 'I'then 'Install'when 'M' then'Mac' end as typedesc, caseinv_trns.transac tn when 'I' then 'Issue' when 'R' then 'Recd' end as transactn, inv_trns.tdate as tdate2,(select count(*) from prod_pkg where product= inv_trns.part) as pkgcount from v_inv_trns inv_trns WHEREinv_trns.CUSTNO ='482721' and inv_trns.type ='S' and reference= 'W45058' orderby tdate2 desc fetch first 2rows only
Last edited by Scrutinizer; 07-05-2012 at 03:39 AM..
Reason: code tags
Friends,
I have a data with 3 columns:
30 41 1
39 19 4
14 25 3
....
....
.....
I want to retrieve any data in the first column that is greater 15. What is the best way to do this?
Thanks! (2 Replies)
Hi All,
I am having trouble through, I am reading the input from tab delimited file containing several records,
e.g.
line1 field1 field2 field3 so on..
line2 field1 field2 field3 so on..
..
..
on the basis of certain fields for each record in input file, I have to retrieve... (1 Reply)
Hi
I'm using and Oracle 10g Database.
I want to write a script to retrieve data from the database and store it toa file.
I'm using simple sql statements such as
Select * from celltable
I don't know how to specify the database name etc.
I have this but it doesn't work
... (1 Reply)
Hello everyone,
I am writing a script to process data from the ATP world tour.
I have a file which contains:
t=540 y=2011 r=1 p=N409
t=540 y=2011 r=2 p=N409
t=540 y=2011 r=3 p=N409
t=540 y=2011 r=4 p=N409
t=520 y=2011 r=1 p=N409
t=520 y=2011 r=2 p=N409
t=520 y=2011 r=3 p=N409
The... (4 Replies)
Hello,
I had Gentoo installed on a Microsoft Windows Hyper-V virtual machine. The system shutdown properly but the RAID array on the drive it was on failed. We had a backup that was poorly configured and as such we didn't back up all of the data we needed.
Therefore, after getting the RAID... (0 Replies)
Dear Friends,
I have a file in which lists State and Phone numbers. Does anybody have a solution in which to take the data from col3 and place it on col2?
AK 2988421640 9077467107
AK 2998266711 2069239034
AK 2983804242 2069239034
AK 2960407849
AK ... (3 Replies)
I have input file like
AAA
AAA
CCC
CCC
CCC
EEE
EEE
EEE
EEE
FFF
FFF
GGG
GGG
i was trying to retrieve data between two strings using sed.
sed -n /CCC/,/FFF/p input_file
Am getting output like
CCC
CCC
CCC (22 Replies)
Hello,
I have some tab delimited data and I need to move the last col. I could hard code it,
awk '{ print $1,$NF,$2,$3,$4,etc }' infile > outfile
but it would be nice to know the syntax to print a range cols.
I know in cut you can do,
cut -f 1,4-8,11-
to print fields 1,... (8 Replies)
Hi,
Please help with this.
I have several excel files (with and .xlsx format) with 10-15 columns each.
They all have the same type of data but the columns are not ordered in the same way.
Here is a 3 column example. What I want to do add the alphabet
from column 2 to column 3, provided... (9 Replies)
Hello experts :cool:,
I am new to programming and will need your help.. I have 2 very large files with the following format:
FILE1:
>MLP1019 PL4
>MLP7456 PL3
>MLP9268 PL9
>MLP6245 PL1
FILE2:
>MLP1019
STNAPLQTSNTWVSYQPSMMMSLQ
>MLP7456
PPYWYWNSAVMIFYVQPLSLLAVLLA
>MLP9268... (2 Replies)
07-05-2012