Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Reference a value in a table to use in another SQL power Query

Hi All   I have a value held in another PQ table. 1 row 1 column containing an invoice number. - Is it possible to use this particular invoice number and use in a SQL query without having to go int...
Need Help
Tips and Tricks
  • KNP's avatar
    KNP
    4 years ago

    Double quote missing before the closing square bracket I think. 

    I notice you're missing a table alias for your SHIP_DATE_RETURN, not sure if that will cause you any abiguity issues.

    This is just the query portion, make sure it goes inside the correct ().

    [Query="
SELECT rd.SERIAL_NUMBER
     , rd.REF_NUM
     , rd.MODEL_ASC
     , pm.MODEL
     , rd.MOTO_PART_NUMBER
     , rd.ASC_SITE_ID
     , ras.ASC_SITE_NAME
     , rd.REPAIR_TYPE
     , acm.ASC_CUSTOMER_NAME
     , rd.WARR_CODE
     , rd.RMA_CREATION_DATE
     , rd.EXPIRY_DATE
     , rd.RECV_DATE
     , rd.SHIP_DATE_RETURN
     , rd.UNIT_STATUS
     , rd.PROJECT_NAME
     , rd.FINAL_UNIT_DETERMINATION
     , rd.INVOICE_NUMBER
     , rd.INVOICE_DATE
     , ROUND((SHIP_DATE_RETURN - rd.RECV_DATE)) AS AGE
     , (rd.RECV_DATE + 30)                      AS TAT
     , rd.LABCOST
     , rd.FREIGHT_COST
FROM rdwadmin.rdw_repair_details rd
   , rdwadmin.rdw_part_master pm
   , rdwadmin.rdw_asc_customer_master acm
   , rdwadmin.rdw_asc_site ras
WHERE rd.moto_part_number = pm.moto_part_number
      AND rd.ASC_CUSTOMER_NUMBER = acm.ASC_CUSTOMER_NUMBER
      AND rd.ASC_SITE_ID = ras.ASC_SITE_ID
      AND rd.INVOICE_NUMBER = '"& #"Current-Invoice"[CurrentInvoice]{0} &"'

"]

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I have amended the code and now get past the original error and now get the Table wasnt recogonised. 

 

 

As per your recommendation, I then went and changed the column name from "Current-Invoice" to "CurrentInvoice" to see if this would help, but as it is trying to find the actual table i don't think at this stage the column name is the problem.

 

Below is what the Current-Invoice Table with CurrentInvoice column looks like:

 

 

Below is the final code with SQL:

KNP's avatar
KNP
Super User
4 years ago

This looks like your issue. Helps now that I know what the table is called. ðŸ˜‰

 

Change the above to...

'"& #"Current-Invoice[CurrentInvoice]{0} &"'

 

If you renamed your query you wouldn't need to reference the table with the #"".

Pay close attention to the quotes. Opens with single, double. Closes with double, single.

Make sure you don't remove the last ]) that closes off the "Query".

 

I think this will get you there.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    So i amended the name, but it didnt like the extra ' at the end as you can see...

     

     

    I removed the extra ' from the end the, PQ then started loading then came up with the below:

     

     

    Though the name is spelled correctly?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi KNP - Do you have any further suggestions on this, I think we are so close on this.

      • KNP's avatar
        KNP
        Super User
        4 years ago

        Anonymous - sorry, I think this is my typo that has confused things.

        I think it should be...

        '"& #"Current-Invoice"[CurrentInvoice]{0} &"'

        It was missing the double quote to close off the table reference.

        Give that a try and let me know how you go.