SQL select statment output issues?

I need help with understanding a sql select statement. I'm new to sql and learning but I'm not sure what the select statement is trying to output with the decode_TO NUMBER code See below for the example:

Select distinct q.queue_name, 
		INV.FIFO_FLAG, 
        WI.RETRIEVING_USER_ID,
       decode(TO_NUMBER(inv.discount_terms_days), null, 
              inv.document_receipt_date + inv.discount_terms_net, 0, 
              inv.document_receipt_date + inv.discount_terms_net, 
              inv.invoice_date + inv.discount_terms_days) due_date, 
       inv.document_id,
       inv.invoice_num, inv.material_amount, 
       lpad(TO_NUMBER(inv.discount_terms_percentage),5,0) percent,
       TO_NUMBER(inv.discount_terms_days) days,
       TO_NUMBER(inv.discount_terms_net) net,
       TO_DATE(wpv.property_value,'DD-MON-YYYY')  FINCENDT,
       inv.markview_document_id markview,
       wi.workitem_instance_id workitem, 
       trunc(wi.enqueued_timestamp) Enter_que

Open in new window


What would the output be for each of these be? I get the Queue Name, FIFO Flag, User ID  but not sure about what's being requested for that section.
QuackdeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
That DECODE() is saying:

Convert inv.discount_terms_days to a number, and then:
If it equals null, return inv.document_receipt_date + inv.discount_terms_net as due_date,
If it equals 0, return inv.document_receipt_date + inv.discount_terms_net as due_date,
Else returninv.invoice_date + inv.discount_terms_days as  due_date

Hope that helps.  Here is reference for DECODE().



»bp
0
QuackdeveloperAuthor Commented:
How would I set my output up for that? I'm reusing output setting from another report. How would I set the output up for the DECODE section? See below for what I have up to this point:

      <table width="100%" border="0">
		<tr>
		  <td colspan="2">

			<table width="100%" align="center" border="0">
			  <tr class="subcathead">              
          <td width="45" class="subcathead">Queue Name</td>
          <td width="40" height="41" class="subcathead">FIFO<br>
          Flag</td>
          <td width="53" class="subcathead">User ID</td>
          <td width="54" class="subcathead">Reg</td>
          <td width="57" class="subcathead">Document ID<br>
          Code </td>
          <td width="49" class="subcathead">Invoice Number<br>
          Limit </td>
          <td width="72" class="subcathead">Invoice<br>
          Material<br>
          Amount</td>
          <td width="74" class="subcathead">Discount<br>
          Terms<br>
          Percentage</td>
          <td width="74" class="subcathead">Discount<br>
          Terms<br>
          Days</td>
          <td width="74" class="subcathead">Discount<br>
          Terms<br>
          Net</td>
          <td width="107" class="subcathead">FINCENDT<br>
          Date </td>
          <td width="93" class="subcathead">Work Item<br>
          Data </td>
		      </tr>

Open in new window

0
Bill PrewCommented:
The result of the DECODE() in the query will be a single value for the column due_date, just treat it as you would any other column.


»bp
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

QuackdeveloperAuthor Commented:
thanks Bill...I'm still a bit confused though with the  bottom section of the code:

       lpad(TO_NUMBER(inv.discount_terms_percentage),5,0) percent,
       TO_NUMBER(inv.discount_terms_days) days,
       TO_NUMBER(inv.discount_terms_net) net,
       TO_DATE(wpv.property_value,'DD-MON-YYYY')  FINCENDT,
       inv.markview_document_id markview,
       wi.workitem_instance_id workitem, 
       trunc(wi.enqueued_timestamp) Enter_que

Open in new window


does that also deal w/ the Due Date from the Decode?
0
Bill PrewCommented:
Those are just formatting each column, is there anything in specific you don't understand, since there are several different formattings going on.  Very quickly:

lpad(TO_NUMBER(inv.discount_terms_percentage),5,0) => pad 0 onto the left of inv.discount_terms_percentage to get a result that is 5 digits

TO_NUMBER(inv.discount_terms_days) => convert inv.discount_terms_days to a number (I assume it's character data currently)

TO_NUMBER(inv.discount_terms_net)  => convert inv.discount_terms_net to a number (I assume it's character data currently)

TO_DATE(wpv.property_value,'DD-MON-YYYY')   => convert wpv.property_value to a date based on the formatting shown

trunc(wi.enqueued_timestamp) => trunacte the date time field to just a date field


»bp
0
QuackdeveloperAuthor Commented:
thanks a ton! I think its clicking now...only last thing w/ this is:

            INV.FIFO_FLAG, WI.RETRIEVING_USER_ID,

What is the WI. doing? is that working with the FIFO Flag or is that a separate column for the user id?
0
Bill PrewCommented:
The WI and the INV are either table names that will show in the FROM clause, or more likely table aliases that will show in the FROM clause.  I would expect you would see something like:

FROM Invoice_Table INV,
Wishing_Table WI

in the FROM clause of the query.  It just assigns a short alias to the table name for the duration of the query.

If you don't see something like that in the FROM clause post up the full query and we can look at it.


»bp
0
QuackdeveloperAuthor Commented:
here's the full sql provided:
Select 
distinct q.queue_name, 
INV.FIFO_FLAG, 
WI.RETRIEVING_USER_ID,
       decode(TO_NUMBER(inv.discount_terms_days), null, 
              inv.document_receipt_date + inv.discount_terms_net, 0, 
              inv.document_receipt_date + inv.discount_terms_net, 
              inv.invoice_date + inv.discount_terms_days) due_date, 
       inv.document_id,
       inv.invoice_num, inv.material_amount, 
       lpad(TO_NUMBER(inv.discount_terms_percentage),5,0) percent,
       TO_NUMBER(inv.discount_terms_days) days,
       TO_NUMBER(inv.discount_terms_net) net,
       TO_DATE(wpv.property_value,'DD-MON-YYYY')  FINCENDT,
       inv.markview_document_id markview,
       wi.workitem_instance_id workitem, 
       trunc(wi.enqueued_timestamp) Enter_que
  From sf_workitem_instance wi,
       sf_workitem_property_value wpv,
       wins_invoices inv,
       sf_queue q
where wi.queue_id = q.queue_id 
   and wpv.WORKITEM_INSTANCE_ID = wi.WORKITEM_INSTANCE_ID
   and inv.workitem_instance_id = wi.workitem_instance_id
   and wpv.WORKITEM_CLASS_PROPERTY_ID in (1046,187)
   and wi.queue_id not in (120,14,40)
   and q.queue_name <> 'Associated'
   and q.queue_name <> 'Not Associated'
order by 1,2,3,4,5,6,7,8;

Open in new window

0
Bill PrewCommented:
Great, so notice the aliases after each table name; that just provides a way to reference the tables in the query in a shorthand fashion.

  From sf_workitem_instance wi,
       sf_workitem_property_value wpv,
       wins_invoices inv,
       sf_queue q


»bp
0
QuackdeveloperAuthor Commented:
The order is showing 8 columns though. I'm only counting 7. What am I missing?

1 - Queue Name
2 - Fifo Flag
3 - User ID
4 - Due Date
5 - Doc ID
6 - Invoice Number
7 - Material Amount
8 - ?
0
Bill PrewCommented:
I actually count 14...

1 =  distinct q.queue_name,
2 =  INV.FIFO_FLAG,
3 =  WI.RETRIEVING_USER_ID,
4 =  decode(TO_NUMBER(inv.discount_terms_days), null,
           inv.document_receipt_date + inv.discount_terms_net, 0,
           inv.document_receipt_date + inv.discount_terms_net,
           inv.invoice_date + inv.discount_terms_days) due_date,
5 =  inv.document_id,
6 =  inv.invoice_num,
7 =  inv.material_amount,
8 =  lpad(TO_NUMBER(inv.discount_terms_percentage),5,0) percent,
9 =  TO_NUMBER(inv.discount_terms_days) days,
10 =  TO_NUMBER(inv.discount_terms_net) net,
11 =  TO_DATE(wpv.property_value,'DD-MON-YYYY')  FINCENDT,
12 =  inv.markview_document_id markview,
13 =  wi.workitem_instance_id workitem,
14 =  trunc(wi.enqueued_timestamp) Enter_que


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
QuackdeveloperAuthor Commented:
I thought those were just formatting? so they're individual columns which are being formatted separately? long way to go to understand this for sure...thanks for your help.
0
Bill PrewCommented:
Yes, that's it, those are columns selected for output, and in addition some of them have some formatting going on.


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.