Solved

CTE, with a Global Temporary Table doesn't work

Posted on 2010-11-30
30
1,088 Views
Last Modified: 2012-05-10
Hi,

 I have a SQL code (attached), which has a global temp table declared and inserted into.
 Also, there is a CTE table. I was trying to join both these tables, but it is not working.
  Can someone kindly suggest any changes to make it work?

Thanks!
SQL.sql
0
Comment
Question by:pvsbandi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 12
  • 2
30 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34242142
Hi Pvsbandi,

What do you mean "not working"?


Kent
0
 

Author Comment

by:pvsbandi
ID: 34242559
Hi,

   It is throwing errors like, semicolon present before "with" etc. Basically, it is not compiling.
0
 
LVL 8

Expert Comment

by:wolfgang_93
ID: 34242789
You appear to have a ";" in front of the WITH part of the INSERT command.
Try removing it.
0
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34243106
Is this part of a stored procedure or are you running it through an SQL client?

If you're using a client, have you made sure that the semicolon is the statement terminator?

Last, can you run each of these statements "one at a time"?


Kent
0
 
LVL 8

Expert Comment

by:wolfgang_93
ID: 34243687
kdo: I don't think running the second statement in a "one at a time" mode will work as the
global temporary table (on which it is dependent) won't exist as it will have been dropped
after the end of the first session.
0
 

Author Comment

by:pvsbandi
ID: 34248099
Hi,

 Yes, they work one at a time and yes, comma is the delimiter.
  Also, i tried running it as a freehand SQL and also tried to run as a stored procedure. Neither worked.
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34248681
Hi wolfgang,

I was looking to see if there was a syntax error in one of the statements or if the problem lied elsewhere.


Hi pvsbandi,

The semicolon needs to be the delimiter (statement terminator).


Kent
0
 

Author Comment

by:pvsbandi
ID: 34250761
Hi Kent,

           I have put the semicolon after the declare, insert and after the end of the final select.
           But it still throws an error and it is pickling the Rand function to have incorrect number of arguments. But i'm sure, that's not where the error is. Bottomline, didn't work :(
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34250905
Hi pvsbandi,

You're on UDB/LUW 9.x, right?

0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251036
I reformatted your query so that I could better follow it.  I don't see an issue.

Here's the CTE without the join to the temp table.  Try highlighting and executing each of the subqueries and seeing if they all work as intended.


Kent

WITH temp2 as 
(
  SELECT   distinct
    value_tx, county_cd,referral_id,ch_client_id,ch_last_nm,ch_first_nm,ch_middle_nm,ch_dob_dt,investigation_start_dt		
    ,inv_completion_dt,supervisor_first_nm,supervisor_last_nm,worker_first_nm,worker_last_nm
    ,row_number() over (partition by county_cd order by bias) AS RN
    ,BIAS
	FROM
	(
		select   
			value_tx,pl_vals.county_cd,referral_id,ch_client_id,ch_last_nm,ch_first_nm,ch_middle_nm,ch_dob_dt		
			,investigation_accepted_dt as investigation_start_dt,action_dt as inv_completion_dt
			,supervisor_first_nm,supervisor_last_nm,worker_first_nm,worker_last_nm
			,cast(rand() * 10000 as integer) as BIAS
		from 
		(	
			SELECT
				picklist_value_cd  as county_cd,value_tx
			FROM tb_picklist_values	
			WHERE picklist_type_id=104  
				AND DELETE_SW ='N'
				AND picklist_value_cd NOT IN('3824','3825','3826')
		) pl_vals
		join
		(
			select
				t1.referral_id,worker_id,investigation_accepted_dt,action_dt,sf.primary_county_cd as county_cd
				,ssf.first_nm      AS supervisor_first_nm,ssf.last_nm       AS supervisor_last_nm
				,sf.first_nm       AS worker_first_nm,    sf.last_nm        AS worker_last_nm	
				,tbl_chs.client_id as ch_client_id,       tbl_chs.last_nm   as ch_last_nm
				,tbl_chs.first_nm  as  ch_first_nm,       tbl_chs.middle_nm as ch_middle_nm	
				,tbl_chs.dob_dt as ch_dob_dt		
			from
			(
				select 
					referral_id 			
					,CHESSIE.F_PRIM_WORKER_ID(referral_id ,'2957') as worker_id 
					,investigation_accepted_dt,action_dt
				from tb_referral rf			
				left join tb_supervisor_approvals sa
					on (sa.approval_key_id = rf.referral_id  
         and (sa.delete_sw = 'N' or sa.delete_sw ='N')
         and sa.approval_status_cd ='3047' 
				 and sa.approval_nature_cd ='2967'
				 and sa.entity_type_cd ='2957')
				WHERE (rf.delete_sw='N' or rf.expungement_sw='Y')	
					and upper(referral_type_sw)='C' 
					AND cps_recommend_sw='Y'
					and ref_approval_status_cd='3047'
					and  investigation_accepted_dt between DATE('08/20/2010') and DATE('10/20/2010') 
			)  t1
			inner join tb_staff sf
				 on (sf.staff_id = t1.worker_id AND sf.delete_sw ='N')
			inner join tb_county_unit cu
				 on (sf.primary_county_unit_id = cu.county_unit_id AND cu.delete_sw ='N' )
			inner join tb_staff ssf
				 on (ssf.staff_id = cu.unit_supervisor_id AND ssf.delete_sw ='N' )	
			inner join
			(
				select cl.client_id ,cl.dob_dt, referral_id ,first_nm ,last_nm,middle_nm
				from tb_referral_clients rc 
				inner join tb_client cl 
					on ( cl.client_id  = rc.client_id  and cl.delete_sw ='N' and rc.delete_sw ='N')
				where rc.delete_sw ='N' 
					and referral_client_id in (select  referral_client_id  from tb_client_roles 
                                     where  delete_sw = 'N' 
                                       and role_cd ='2085' 
                                       and referral_client_id in (select referral_client_id 
                                                                  from tb_referral_clients 
                                                                  where  delete_sw ='N'
                                                                 )
                                    )
			) tbl_chs
				on ( tbl_chs.referral_id = t1.referral_id )
		) t2
		on t2.county_cd = pl_vals.county_cd
		order by  pl_vals.county_cd
	) AS T
	ORDER BY 15
) 
select temp2.*

Open in new window

0
 

Author Comment

by:pvsbandi
ID: 34251154
Hmm.. you are right! It worked a while ago, but it is now failing at the Rand runction.
  If i comment the Rand function line, it works. But it worked before :(
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251208
Weird.  

The rand function should work just as you have it.  I copied/pasted the cast(rand...) text and it works fine.

Just for test, try inserting an integer value as the parameter to rand().  Perhaps there's something wrong with your environment so that rand(void) is corrupted but rand(int) is OK.


Kent
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251250
For test purposes, what if you omit the call to rand() and just use a constant?

      ,cast(rand() * 10000 as integer) as BIAS


becomes

      , 123 as BIAS


Kent
0
 

Author Comment

by:pvsbandi
ID: 34251289
I tried Rand() * 10000, but it still gives error.
   Can you give an example for your suggestion?

Thanks much for all the help!
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251330
Try the constant suggested two posts back.

Kent
0
 

Author Comment

by:pvsbandi
ID: 34251352
yes, a constant works absolutely fine. But, i need a random selection of the number of rows and that's the purpose of the whole effort.
 
  Is there a workaround for it?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251388

Rand() doesn't work.  Amazing.

Can you confirm your system.  UDB/LUW 9.x?

And let's see if the rand function is there.  Try this query:

  select cast(rand() * 10000 as integer) from sysibm.sysdummy1;


Kent
0
 

Author Comment

by:pvsbandi
ID: 34251421
No, i'm on DB2 UDB 8.2

select cast(rand() * 10000 as integer) from sysibm.sysdummy1

The above query doesn't work and it throws the same error (no function Rand with paramteres ....)

0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251504
Version 8.  Rats.  I don't have a version 8 system to test against.

Try passing an integer to rand.  I just don't rememeber....


  select cast(rand(127) * 10000 as integer) from sysibm.sysdummy1;


Kent
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 34251553
According to the version 8 documentation, rand() behaves similarly in versions 8 and 9.

That's the good news.

Somehow, you're not connecting to it.


rand() should be in the sysfun schema.  There should be two entries, one for rand() and a second for rand(integer).  Check to see if they exist.

0
 

Author Comment

by:pvsbandi
ID: 34251569
nothing seem to work, related to Rand.. But it really worked before :((
   Anyways, thanks much for all your help. Really appreciate.
0
 

Author Comment

by:pvsbandi
ID: 34251597
>>rand() should be in the sysfun schema.  There should be two entries, one for rand() and a second for rand(integer).  Check to see if they exist

BTW, how do we check the above?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251615
Hi...

I think that it's your environment.  Either the rand() functions have been deleted from the sysfun schema (an odd thing to do) or you environment isn't automatically accessing the schema or is somehow not authorized to it.

Run this from your user:

   select sysfun.rand() from sysibm.sysdummy1;


If that doesn't work, run it from the sysadmin user.


Kent
0
 

Author Comment

by:pvsbandi
ID: 34251643
You are a rockstar!!!

     In the query, i made it sysfun.rand() and ran the whole thing.. and it worked out excellently!!
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251652

Or run this query:

  select * from sysibm.sysroutines where routinename = 'RAND';


0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251662
Something is messed up in your environment.  You should link the sysfun routines without having to specify the schema name.


Perhaps you have something in your own schema named rand?


Kent
0
 

Author Comment

by:pvsbandi
ID: 34251713
in the db2cli file on the IBM folder, there is no entry for the Currentfunctionpath.
  But even after i added the SYSFUN to it, didn't work when i didn't explicitly mention the schema name before the function..
0
 

Author Comment

by:pvsbandi
ID: 34251745
Also, when i ran this query

select * from sysibm.sysroutines where routinename = 'RAND';

I see two rows under the sysfun schema; Specific names for these are Rand1 and Rand2.
 Could this have anything to do with my struggle here?
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251760

Let's see if you have a view called RAND.

 select * from sysibm.views where table_name = 'RAND';

Oh, and if you have a table named RAND in that schema you'll also get that error.


Kent
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 34251769

Those appear to be the correct items in the sysfun schema.
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question