Solved

Datawindow conditional sql

Posted on 2015-01-05
7
91 Views
Last Modified: 2015-01-09
Hi,
I'm using Powerbuilder 12.5.
I'm working on a datawindow that uses ordinal dates as the start and end date criteria. The problem is that this doesn't currently work around the new year since the start is larger than the end. I'm trying to find a way to handle this with a single datawindow instead of having to use two. I've tried the below where start_day is 355 and end_day is 5, but it locks the app up.

IF :end_day > 17
	  SELECT dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg as 'wave',
				'New' as 'status',
				sum(dbo.ord_hdr.crd_tot_nbr) as 'tot_os',   
				sum(dbo.ord_hdr.aces_tot_nbr) as 'tot_acc',   
				sum(dbo.ord_hdr.prpk_tot_nbr) as 'tot_pp',   
				sum(dbo.ord_hdr.addon_tot_nbr) as 'tot_sao',
				'     ' as 'ws_os',
				'     ' as 'ws_acc',
				'     ' as 'ws_pp',
				'     ' as 'ws_sao',
				'     ' as 'wc_os',
				'     ' as 'wc_acc',
				'     ' as 'wc_pp',
				'     ' as 'wc_sao'  
		 FROM dbo.ord_hdr  
		WHERE ( convert(integer,dbo.ord_hdr.jul_rlse_dy) >= :start_day) AND
				( convert(integer,dbo.ord_hdr.jul_rlse_dy) <= :end_day) 
	group by dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg

ELSE

  SELECT dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg as 'wave',
         'New' as 'status',
         sum(dbo.ord_hdr.crd_tot_nbr) as 'tot_os',   
         sum(dbo.ord_hdr.aces_tot_nbr) as 'tot_acc',   
         sum(dbo.ord_hdr.prpk_tot_nbr) as 'tot_pp',   
         sum(dbo.ord_hdr.addon_tot_nbr) as 'tot_sao',
         '     ' as 'ws_os',
         '     ' as 'ws_acc',
         '     ' as 'ws_pp',
         '     ' as 'ws_sao',
         '     ' as 'wc_os',
         '     ' as 'wc_acc',
         '     ' as 'wc_pp',
         '     ' as 'wc_sao'  
    FROM dbo.ord_hdr  
   WHERE ( convert(integer,dbo.ord_hdr.jul_rlse_dy) >= :start_day) OR
         ( convert(integer,dbo.ord_hdr.jul_rlse_dy) <= :end_day) 
group by dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg

Open in new window

0
Comment
Question by:coperations07
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 3

Expert Comment

by:kotukunui
ID: 40532389
Please provide the table structure for dbo.ord_hdr as a "CREATE TABLE" DDL statement and some sample data.
It will make it easier to determine a solution if all columns and types are known.
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 40536811
What value is contained in ord_hdr.jul_rlse_dy ?  If this field contains a datetime-value (i.e., has a year, month, day, and maybe a time component) then your logic would be incorrect if table ord_hdr has several years of data because each years' data would contain records for early January and late December.

Converting a datetime-value to an integer would be a really big number, it's the number of milliseconds from the fixed-date 1970-Jan-01.  You'd be better off (both from a performance perspective and final results) if you converted your start/end ordinal-date values into datetime-values instead of converting the table-field jul_rlse_dy into an integer.

No if-statement needed, I would also add a full day to end-day, in case there is a time component:
SELECT	dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg as 'wave'
,	'New' as 'status'
,	sum(dbo.ord_hdr.crd_tot_nbr) as 'tot_os'
,	sum(dbo.ord_hdr.aces_tot_nbr) as 'tot_acc'
,	sum(dbo.ord_hdr.prpk_tot_nbr) as 'tot_pp'
,	sum(dbo.ord_hdr.addon_tot_nbr) as 'tot_sao'
,	'     ' as 'ws_os'
,	'     ' as 'ws_acc'
,	'     ' as 'ws_pp'
,	'     ' as 'ws_sao'
,	'     ' as 'wc_os'
,	'     ' as 'wc_acc'
,	'     ' as 'wc_pp'
,	'     ' as 'wc_sao'
FROM	dbo.ord_hdr  
WHERE	dbo.ord_hdr.jul_rlse_dy >= :start_day
AND	dbo.ord_hdr.jul_rlse_dy <  :end_day
GROUP
BY	dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg

Open in new window

0
 

Author Comment

by:coperations07
ID: 40536835
The value is the ordinal date ( 1 - 365). It is referred to internally as the julian date, hence the field name. Only the last 90 days of data is retained, so there's no overlap. The ordinal date is used as part of a trailer name, so the users like to use it as criteria.
My problem is that the new year caused the end day to be before the start. I'm looking at adding a second set of parameters that would include a second date range if the end is less than the start. The Where would look something like this:
   WHERE (( convert(integer,dbo.ord_hdr.jul_rlse_dy)) BETWEEN :start_day AND :end_day OR
			( convert(integer,dbo.ord_hdr.jul_rlse_dy)) BETWEEN :start_day2 AND :end_day2)

Open in new window

0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 3

Assisted Solution

by:kotukunui
kotukunui earned 250 total points
ID: 40536972
Try using a comparison of the parameters as part of the condition and a UNION ALL to allow two datasets to be mixed as a single SQL query. I think it is the IF statement that Powerbuilder will be throwing the error over

 
SELECT dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg as 'wave',
				'New' as 'status',
				sum(dbo.ord_hdr.crd_tot_nbr) as 'tot_os',   
				sum(dbo.ord_hdr.aces_tot_nbr) as 'tot_acc',   
				sum(dbo.ord_hdr.prpk_tot_nbr) as 'tot_pp',   
				sum(dbo.ord_hdr.addon_tot_nbr) as 'tot_sao',
				'     ' as 'ws_os',
				'     ' as 'ws_acc',
				'     ' as 'ws_pp',
				'     ' as 'ws_sao',
				'     ' as 'wc_os',
				'     ' as 'wc_acc',
				'     ' as 'wc_pp',
				'     ' as 'wc_sao'  
		 FROM dbo.ord_hdr  
		WHERE :start_day < :end_day AND 
		      ( ( convert(integer,dbo.ord_hdr.jul_rlse_dy) >= :start_day) AND
				( convert(integer,dbo.ord_hdr.jul_rlse_dy) <= :end_day) ) 				
	group by dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg

UNION ALL

  SELECT dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg as 'wave',
         'New' as 'status',
         sum(dbo.ord_hdr.crd_tot_nbr) as 'tot_os',   
         sum(dbo.ord_hdr.aces_tot_nbr) as 'tot_acc',   
         sum(dbo.ord_hdr.prpk_tot_nbr) as 'tot_pp',   
         sum(dbo.ord_hdr.addon_tot_nbr) as 'tot_sao',
         '     ' as 'ws_os',
         '     ' as 'ws_acc',
         '     ' as 'ws_pp',
         '     ' as 'ws_sao',
         '     ' as 'wc_os',
         '     ' as 'wc_acc',
         '     ' as 'wc_pp',
         '     ' as 'wc_sao'  
    FROM dbo.ord_hdr  
   WHERE :start_day > :end_day AND 
         ( ( convert(integer,dbo.ord_hdr.jul_rlse_dy) >= :start_day) OR
         ( convert(integer,dbo.ord_hdr.jul_rlse_dy) <= :end_day)) 
group by dbo.ord_hdr.jul_rlse_dy + dbo.ord_hdr.rlse_cycle + dbo.ord_hdr.trlr_seg

Open in new window

0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 250 total points
ID: 40539076
Perhaps all the logic could be placed in the SQL where-clause:
WHERE	(	:start_day <= :end_day
	AND	dbo.ord_hdr.jul_rlse_dy BETWEEN :start_day AND :end_day
	)
OR	(	:start_day > :end_day
	AND	(	dbo.ord_hdr.jul_rlse_dy >= :start_day
		OR	dbo.ord_hdr.jul_rlse_dy <= :end_day
		)
	)

Open in new window

0
 
LVL 3

Expert Comment

by:kotukunui
ID: 40539087
@John Vidmar - Even better!
0
 

Author Closing Comment

by:coperations07
ID: 40541311
Thanks guys! Those both work for me. I think I'll use the one from @John Vidmar since it is a little more compact.
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Introduction to Processes

688 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