Passing SQL through a string parameter

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Passing SQL through a string parameter

Post by mjohnson62982 »

I am currently trying to pass some SQL through a parameter that will allow the user to choose whether to do a full load of information from a table or to just load data from the previous day. The where clause of the select statement in the JDBC Connector looks like this:

Code: Select all

FROM "#Library_ps.Library_Name#"."#TableName#" x
WHERE TRIDTR || '-' || MARQTR IN ('30-0', '32-1', '32-6', '41-77', '40-2', '83-0', '39-0') 
AND"#SFPTR_JACKPOT_Load_Type#"
ORDER BY DHYRTR
The SFPTR_JACKPOT_Load_Type parameter set has to options: Full_Load and Partial_Load. The value of the Full_Load option is:

Code: Select all

DHYRTR <= (select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)
The Partial_Load option is:

Code: Select all

DHYRTR <= (select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)
When I try to view the data from the table I get the following error:
The connector failed to add missing columns to the output link. The rported error is: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=ORDER;< > = <> <= !< !> != >= IN NOT, DRIVER=3.66.46
I would appreciate any help or suggestions to accomplish the task.
Respectfully,
Matt Johnson
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Passing SQL (or anything else for that matter) with single quotes in it as a parameter has always been an issue. This solution is a bit old but should solve your problem.
-craig

"You can never have too many knives" -- Logan Nine Fingers
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

I attempted this fix, however I am still getting the same message.
Respectfully,
Matt Johnson
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Interesting.

Do you know what the actual SQL that it ends up trying to run looks like? Trying to see if there's something more than the typical "stripping of quotes" going on here.

Oh - and just noticed that you marked this as a Server job problem, is that the case? AFAIK, that APT fix only works on PX jobs...
-craig

"You can never have too many knives" -- Logan Nine Fingers
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

I think it looks like this...

Code: Select all

DHYRTR <= (select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)
I wonder is there a problem with putting characters like "<=" in a string parameter?
Respectfully,
Matt Johnson
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I don't think so but others may know more better. So... Server job?
-craig

"You can never have too many knives" -- Logan Nine Fingers
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

Well it was a job transferring specified information from an iSeries table to a DB2 table. Not sure what it should have be labeled as. Sorry for any confusion.
Respectfully,
Matt Johnson
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I only asked because you marked the Job Type property in the initial post as Server. I'm going to assume you are using Parallel jobs as Server job questions here are (sadly) few and far between.
-craig

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Took another look at what you posted while I wait for something to complete and have a couple of comments. First off, your full and partial load parameter values look remarkably similar.

Secondly, perhaps try this minor change:

Code: Select all

FROM "#Library_ps.Library_Name#"."#TableName#" x 
WHERE TRIDTR || '-' || MARQTR IN ('30-0', '32-1', '32-6', '41-77', '40-2', '83-0', '39-0') 
AND DHYRTR <= #SFPTR_JACKPOT_Load_Type#
ORDER BY DHYRTR
Then your parameter string would just be:

Code: Select all

(select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)
I don't see a need for the double-quotes in that part of the where clause. Be curious if that changes the error message at all.
-craig

"You can never have too many knives" -- Logan Nine Fingers
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

I tried it without the double quotes and got the same message. But the reason I am using the parameter to give me the 2 options is that I need to allow the user to run the query to pull every row from the table or just the rows from the previous day.
Respectfully,
Matt Johnson
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

I'm sorry the partial load SQL is:

Code: Select all

DHYRTR = (select days (current date) - days (date('1900-01-01')) FROM SYSIBM.SYSDUMMY1)
Respectfully,
Matt Johnson
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I understand the why of it. So did you try the modification I suggested or just without the double quotes? The latter was just an off-hand comment, the former was an attempt to get something working for you.
-craig

"You can never have too many knives" -- Logan Nine Fingers
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

It works if it is not in a parameter.
Respectfully,
Matt Johnson
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Sure. So... you're not going to try the minor change I mentioned? As a test.
-craig

"You can never have too many knives" -- Logan Nine Fingers
mjohnson62982
Participant
Posts: 20
Joined: Wed Nov 01, 2017 12:14 pm

Post by mjohnson62982 »

The error I get now is:

Code: Select all

The reported error is: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=SFPTR_JACKPOT_FULL_LOAD;*N.N, DRIVER=3.66.46
Respectfully,
Matt Johnson
Post Reply