10 Replies - 739 Views - Last Post: 04 January 2009 - 07:46 AM Rate Topic: -----

#1 annis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 12-December 08

sql query

Post icon  Posted 12 December 2008 - 08:00 AM

Hi ,
when i run the following sql script , it always show that "invaild character" but i have tried hard to find out and it seems no help.. :(
please advise kindly and thanks so much
select mat_no,
	   SUM(A_purd_qty) A ,
	   SUM(B_purd_qty) B,
	   SUM(C_purd_qty) C,
	   SUM(H_purd_qty) H,
	   SUM(P_purd_qty) P
FROM   
(
	select mat_no,
			   decode(substr(mat_no,1,1),'A',purd_total,0) A_purd_qty,		   decode(substr(mat_no,1,1),'B',purd_total,0) B_purd_qty,
 decode(substr(mat_no,1,1),'C',purd_total,0) C_purd_qty,	  decode(substr(mat_no,1,1),'H',purd_total,0) H_purd_qty,
decode(substr(mat_no,1,1),'P',purd_total,0) P_purd_qty
	from   y3_purdc
	where  
			   purd_date between '20080101' and '20081130' and 
			   substr(mat_no,1,1) in ('A','B','C','H','P') 
	union all
	select mat_no,
				decode(substr(mat_no,1,1),'A',purd_qty,0) A_purd_qty,
	   decode(substr(mat_no,1,1),'B',purd_qty,0) B_purd_qty,
	   decode(substr(mat_no,1,1),'C',purd_qty,0) C_purd_qty,
	   decode(substr(mat_no,1,1),'H',purd_qty,0) H_purd_qty,
	   decode(substr(mat_no,1,1),'P',purd_qty,0) P_purd_qty
	from   y3_purdb
	where  
	   purd_date between '20080101' and '20081130' and 
	   substr(mat_no,1,1) in ('A','B','C','H','P') 
	union all
	select mat_no,
	   decode(substr(mat_no,1,1),'A',purd_qty,0) A_purd_qty,
	   decode(substr(mat_no,1,1),'B',purd_qty,0) B_purd_qty,
	   decode(substr(mat_no,1,1),'C',purd_qty,0) C_purd_qty,
	   decode(substr(mat_no,1,1),'H',purd_qty,0) H_purd_qty,
	   decode(substr(mat_no,1,1),'P',purd_qty,0) P_purd_qty
	from   y3_purdf
	where  
	   purd_date between '20080101' and '20081130' and 
	   substr(mat_no,1,1) in ('A','B','C','H','P') 
	union all
	select mat_no,
		   decode(substr(mat_no,1,1),'A',qty,0) A_purd_qty,
		   decode(substr(mat_no,1,1),'B',qty,0) B_purd_qty,
		   decode(substr(mat_no,1,1),'C',qty,0) C_purd_qty,
		   decode(substr(mat_no,1,1),'H',qty,0) H_purd_qty,
		   decode(substr(mat_no,1,1),'P',qty,0) P_purd_qty
	from   y3_rcvctn
	where  
	   ctn_date between '20080101' and '20081130' and 
	   substr(mat_no,1,1) in ('A','B','C','H','P') 
	union all
	select mat_no,
	   decode(substr(mat_no,1,1),'A',b.purd_qty,0) A_purd_qty,
	   decode(substr(mat_no,1,1),'B',b.purd_qty,0) B_purd_qty,
	   decode(substr(mat_no,1,1),'C',b.purd_qty,0) C_purd_qty,
	   decode(substr(mat_no,1,1),'H',b.purd_qty,0) H_purd_qty,
	   decode(substr(mat_no,1,1),'P',b.purd_qty,0) P_purd_qty
	from   y3_apply a,y3_applys b
	where  a.fact_no  = b.fact_no(+) and
		   a.apply_no = b.apply_no(+) and
		   a.fact_no = 'B03' and 
		   a.apply_date between '20080101' and '20081130' and 
						  substr(b.mat_no,1,1) in ('A','B','C','H','P') 
)
group by mat_no
order by A_purd_qty;


***added code tags***
--jjsaw5

Is This A Good Question/Topic? 0
  • +

Replies To: sql query

#2 jjsaw5  Icon User is offline

  • I must break you
  • member icon

Reputation: 90
  • View blog
  • Posts: 3,060
  • Joined: 04-January 08

Re: sql query

Posted 12 December 2008 - 08:02 AM

:code:


Is it giving you a line number in the error message?
Was This Post Helpful? 0
  • +
  • -

#3 annis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 12-December 08

Re: sql query

Posted 12 December 2008 - 08:10 AM

View Postjjsaw5, on 12 Dec, 2008 - 07:02 AM, said:

:code:


Is it giving you a line number in the error message?



Thanks, it always show the error at the end line, which is in front of ";".
Was This Post Helpful? 0
  • +
  • -

#4 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: sql query

Posted 12 December 2008 - 08:13 AM

Have you tried taking out the ";" and see what happens? Also, what program are you coding in/what type of database?
Was This Post Helpful? 0
  • +
  • -

#5 annis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 12-December 08

Re: sql query

Posted 12 December 2008 - 08:22 AM

View PostP4L, on 12 Dec, 2008 - 07:13 AM, said:

Have you tried taking out the ";" and see what happens? Also, what program are you coding in/what type of database?


e, i have tired , and it show "not properly ended"
Powerbuilder, oracle

it is strange that no matter i make "uncomment" and "comment" it keep showing the error "invaild character" in front of the ";"
Was This Post Helpful? 0
  • +
  • -

#6 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: sql query

Posted 12 December 2008 - 08:38 AM

Copy and paste the entire error message please.
Was This Post Helpful? 0
  • +
  • -

#7 annis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 12-December 08

Re: sql query

Posted 02 January 2009 - 05:23 PM

View PostP4L, on 12 Dec, 2008 - 07:38 AM, said:

Copy and paste the entire error message please.


I have checked out the problem , it is just because the comment "//"
anyway, thanks so much for ur help
Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5929
  • View blog
  • Posts: 12,851
  • Joined: 16-October 07

Re: sql query

Posted 02 January 2009 - 07:44 PM

I couldn't spot it, though I'd drop the order by and see how it goes.

Also, I thought you might be able to simplify this a little:
select mat_no,
		decode(letter,'A',qty,0) A,
		decode(letter,'B',qty,0) B,
		decode(letter,'C',qty,0) C,
		decode(letter,'H',qty,0) H,
		decode(letter,'P',qty,0) P
	FROM (
		select mat_no, purd_date as dt, substr(mat_no,1,1) letter, purd_total as qty from y3_purdc 
		union all
		select mat_no, purd_date as dt, substr(mat_no,1,1) letter, purd_qty as qty from y3_purdb
		union all
		select mat_no, purd_date as dt, substr(mat_no,1,1) letter, purd_qty as qty from y3_purdf
		union all
		select mat_no, ctn_date as dt, substr(mat_no,1,1) letter, qty from y3_rcvctn
		union all
		select b.mat_no, a.apply_date as dt, substr(b.mat_no,1,1) letter, b.purd_qty as qty
			from y3_apply a,y3_applys b
			where a.fact_no  = b.fact_no(+) and a.apply_no = b.apply_no(+) and a.fact_no = 'B03'
		)
	where dt between '20080101' and '20081130' 
		and letter in ('A','B','C','H','P') 
	group by mat_no


Was This Post Helpful? 0
  • +
  • -

#9 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: sql query

Posted 03 January 2009 - 01:15 AM

Did you miss the SUM in the simplification or am i missing something?
Was This Post Helpful? 0
  • +
  • -

#10 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5929
  • View blog
  • Posts: 12,851
  • Joined: 16-October 07

Re: sql query

Posted 03 January 2009 - 05:45 AM

View PostTrogdor, on 3 Jan, 2009 - 02:15 AM, said:

Did you miss the SUM in the simplification or am i missing something?


Nope, I missed it, thanks for the catch.

select mat_no,
		sum(decode(letter,'A',qty,0)) A,
		sum(decode(letter,'B',qty,0)) B,
		sum(decode(letter,'C',qty,0)) C,
		sum(decode(letter,'H',qty,0)) H,
		sum(decode(letter,'P',qty,0)) P
	FROM (
-- ...



The worst thing about SQL questions is that answers take place in the dark. You get spoiled actually having the database in front of you and being able to validate against it. :P
Was This Post Helpful? 0
  • +
  • -

#11 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: sql query

Posted 04 January 2009 - 07:46 AM

Very true, there is nothing as satisfying as seeing a speedup of a factor 1000 on a test query :-)
I am still quite regularly astonished by the sheer speed and power of databases, when used well.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1