Solved

CTE, with a Global Temporary Table doesn't work

Posted on 2010-11-30
30
1,074 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
  • 16
  • 12
  • 2
30 Comments
 
LVL 45

Expert Comment

by:Kdo
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
 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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:Kdo
ID: 34250905
Hi pvsbandi,

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

0
 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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:Kdo
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:Kdo
ID: 34251330
Try the constant suggested two posts back.

Kent
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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:Kdo
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:Kdo
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:
Kdo 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:Kdo
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:Kdo
ID: 34251652

Or run this query:

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


0
 
LVL 45

Expert Comment

by:Kdo
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:Kdo
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:Kdo
ID: 34251769

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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now